title: Object oriented model for tables in MS Access
body: <p>I have an MS Access database with several tables. Almost all tables contain inventory information about different classes of items (there are some utility tables which store extra information, such as a list of classes and lists of commonly used lookup values). Some classes of items have particular data specific to them - for instance, volume is relevant for liquids but not solid objects, but all objects have a location. The logical structure of my database is a textbook example of a case where an object oriented model provides clarity and maintainability benefits:</p>
  
  <ul>
  <li>There is one basic table which is a catch-all table for all items that don't fit into other categories. It contains a few columns, like item name, date, location and notes that is applicable to any item. This would be the top superclass, e.g. <code>class InventoryTable</code>.</li>
  <li>There are tables for specific classes, such as a table for printer cartridges. This table will have all the columns that <code>InventoryTable</code> has, but also include some specialized information that is only relevant for printer cartridges, such as printer model, ink color and brand. This table would be a subclass, e.g. <code>class PrinterCartridgeTable : InventoryTable</code>.</li>
  <li>Sometimes there is a deeper inheritance structure. For example, there may be a table for all documents (<code>class DocumentTable : InventoryTable</code>, includes extra field for how many pages a document has) and then another table for letters (<code>class LetterTable : DocumentTable</code> which also has columns for sender and recipient of the letter). The assumption is that one would look for letters in the <code>LetterTable</code>, and if not found there, could try looking in the <code>DocumentTable</code> and the top level <code>InventoryTable</code>.</li>
  </ul>
  
  <p>Let's say my dates are currently displayed as <code>MM/DD/YYYY</code>. I want to change them to ISO format (<code>YYYY-MM-DD</code>). Currently, I have to open every single table I have (about 20) and change the format in each one of them one by one. If there was some kind of inheritance mechanism, I could instead change the format only in my top-level <code>InventoryTable</code>, and all my other tables would inherit the change.</p>
  
  <p>Or, suppose I decide to store a new piece of data, called "Owner", for all items. This would describe who entered the item into the inventory. I could simply add this column to <code>InventoryTable</code>, and it would appear in all the child tables automatically.</p>
  
  <p>Lastly, let's say I make cosmetic changes such as rearranging the order of columns. Let's say in my document-related tables, the page number appeared at the end. I instead move the page number to the very beginning of the table - this would propagate to both <code>DocumentTable</code> as well as <code>LetterTable</code> but not unrelated tables.</p>
  
  <p>Bear in mind that I am editing these tables manually using the GUI of MS Access 2013. When editing information pertaining to a single class of items, I would not like to switch back and forth between tables or queries to edit different parts of the same record - I want to be able to see and edit all of the information for any given record in one place. Therefore, some complicated solutions based on chaining queries may be impractical.</p>
  
  <p>Is it possible for me to accomplish what I want (the inheritance structure) in Access using some kind of object oriented scheme? Is there an alternative way of obtaining the same benefits? Do I have no choice except to give up and manually propagate every change to all tables?</p>