We have a very large SQL database that we are using Access to work with. Now currently I have need of adding information without adding fields to the main database. The primary reason for this would be for management in my work center to take notes on items, however we do not want to broadcast these notes to everyone using the SQL database.
The plan is to add a table to the GUI we have and have it duplicate the unique reference numbers from the core database/table in the SQL server. This would be done in order to pull up a form that would contain both the info from the core database and the notes from the work center and be able to edit both from said form. To do this I require a relationship that links the entries in my table to the entries in the core table so that they can be matched for the form display. As there are literally thousands of entries in the core, and they add more daily the best course of action is to have a system in place that checks the core and then updates my notes table in the GUI. This is where I am stuck, I cannot seem to find a way to have the gui check/copy/update the notes table with the reference numbers in the core table, I only need to copy this one column in order for everything to work.
If you want placeholder records in your notes table and you only allow a single note field per record then you can set the reference number field in the notes table as the primary. With this, a simple update query from the core database will keep the notes table populated. Something like:
insert into notes (refNum) select distinct referenceNum from coreDb
Just run this query on form open or form current and the primary key on the notes table will kick out any duplicates.
However, it may be better to only have entries in the notes tables for those records that actually have notes rather than placeholder records just in case. The logic behind the form would then simply insert or update as appropriate.