Updating lookup tables which are linked in the relationship window

[fiona108]

I have written and support a database for a client which has a large number of lookup tables. These are linked in the relationships window to the master tables and Referntial Integrity is set for all relationships.

The database is run on 2 separate sites, one of which can update the lookup tables. The autonumber field of the lookups is held as the foreign key in the master tables and it is obviously crucial that both sites have identical versions of these lookup tables.

I want to automate the refresh of the remote site with the updated lookup tables. I was hoping to simply delete the existing table and replace it with the updated version, using the DeleteObject and TransferDatabase methods. However, this does not appear to be possible due to the relationships. I know that I could code around this and programatically delete then recreate the relationships once the lookups have been refreshed, but it is a large, complicated database and I feel I am missing a more striaghtforward solution.

Does anyone know if there is a simple way to update the off-site database with the lookups without deleting then recreating all of the relationships ?

I have tried using Update queries, but any new records go in at the next autonumber value and I do not seem to be able to guarantee that these values are the same across the databases - for example if records are added then deleted, the autonumbers can get out of step.

Hope this makes sense to someone ! I must be missing something straightforward, but I cannot see it. All help gratefully received. Thanks.

 

[traingamer]

Copy the records into a temp table in the remote database and run an append query USING the specific id.

INSERT INTO tblLookup
SELECT tblLookup2.*
FROM tblLookup2 where tblLookup2.id not in (Select t3.id from tblLookup as t3);

Even though the id is set to autonumber, this will use the existing ids.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[fiona108]

Thanks so much ! That sorts it.