I have been tasked with compleating a data import system (on access 2000)
I have a database that "reads" in updates for 7 of its 26 tables from a temporary MDB file.
The MDE (will be is MDB at mo) uses this SQL to read the file
SELECT Mat_T1.Converter_Name AS Name, Mat_T1.RatioM, Mat_T1.RatioV, Mat_T1.Offset, Mat_T1.Converter_SBKEY AS SBKEY
FROM Mat_T1 IN 'C:\Documents and Settings\All Users\Documents\Matt\split ver\New_16_8_2004.mdb'
GROUP BY Mat_T1.Converter_Name, Mat_T1.RatioM, Mat_T1.RatioV, Mat_T1.Offset, Mat_T1.Converter_SBKEY;
now the destination table has all those fields plus an autonumber ID which is the database primery key. However there is an alternate key in the form of the field SBKEY which is a nation wide identifier of the record. What I need to do is create some SQL that will look at the SBKEY for both tables and update the database with the new values.
I must also add any values that are currently absent so if it could do that it would be good.
However the primary key holds the integraty for the rest of the database and so rows can not be deleted from the active DB.
I don't know exactly how to do this without potentially running DLookup 2500 times a day per table per update file... with up to three files and anything upto and exeading 5 tables per update table that's a lot of sitting around.