sqlms-accesslinked-tables

Is it possible to update a local table with values from a linked table?

I have two identical tables in my database, T1 and T2. T1 is a local table while T2 is a linked table from a live SQL database. At the moment the two tables are identical.

In short, I would like to be able to run a query that will update T1 will all new records that have been added to T2. So once I have run the query, the two tables should be identical again. Is this at all possible? I need to have the data in T1 locally available as I need to be able to query that table even when the data in T2 is not available. The SQL database in question is off site so I will not always be able to run my queries as the link is unreliable.

Any assistance will be greatly appreciated.

Solution

  • If you do have a unique ID for every record and are sure records already entered will never be changed this can actually be quite simple:

    INSERT INTO [TBL_INVOICES_LOCAL]
SELECT TBL_INVOICES.*
FROM [TBL_INVOICES_LOCAL] RIGHT JOIN TBL_INVOICES ON [TBL_INVOICES_LOCAL].InvoiceID = TBL_INVOICES.InvoiceID
WHERE ((([TBL_INVOICES_LOCAL].InvoiceID) Is Null));

    All you need to do is join the two tables with the relationship being set to:

    Include ALL records from LINKED_TABLE and only records from LOCAL_TABLE where the joined fields are equal.

    Setting the criteria of the Local Tables ID field to "Is Null" will only show missing records. If you do this in an append query you can update your table in only one query as seen below:

    Demo Image