Update data between tables using a Date (1 Viewer)

[Jayce72]

I have two identical Databases - one is a Live DB and the other is a Training DB. The data is held on a sql server connected to an access front end. (I do not have access to the sql server).

- On each table I have a field called UpdatedOn
- On each table every record has one field that is a unique ref. But the name of each field containing the unique ref is different on each table
- There are about 100 table to loop through.

What I need is some code that will loop through each table and each record, and where the UpdatedOn field on the live DB is not equal to the UpdatedOn on the training DB - update the whole line on the training DB with the Live DB data using the unique ref

I intend to only have one access client and import the sql tables from the Live and Training into the one. Therefore for example sqlMyTable will be live DB and sqlMyTable1 will be the Training DB

If anyone has a better way - please feel free to say.

Hope someone can please help

Thanks

Jason

 

[CJ_London]

why not just delete all the data in the training db and then import all the data from the livedb?

depending on the number of fields in a table, the update query is going to be quite cumbersome - particularly if you have to do it for 100 tables

 

[Jayce72]

Hi - not possible to delete the data - as a number of tables have a few hundred thousand lines and it'll take hours - unless it's possible to overwrite the exisitng data
Thanks
Jason

 

[CJ_London]

ok, delete the tables then and copy the tables across - or the whole db

 

[RainLover]

ok, delete the tables then and copy the tables across - or the whole db

Sounds like the way to go. Simple and almost fool free.

I did say almost.

 

[Jayce72]

ok, delete the tables then and copy the tables across - or the whole db

Can't just Delete the tables as they are sql tables and deleting them and copying them across will only copy over the live tables into the Training access Database. Also, I cannot delete the data and repopulate due to the hundred thousand records in some tables - it would take hours

 

[CJ_London]

OK - back to basics, athough you are complaining about the time the various suggestions will take, I presume you appreciate that update queries are the slowest

What I need is some code that will loop through each table and each record, and where the UpdatedOn field on the live DB is not equal to the UpdatedOn on the training DB - update the whole line on the training DB with the Live DB data using the unique ref

So ask yourself these questions

- did both db's start out the same and since then changes have been made to both?
- has either db had records added which are not duplicates of each other
-assuming this is the case, what happens to 'new' records in the live db which do not have a matching record in the training db - and visa versa?
- what is supposed to happen if there are two mismatched ID's - i.e. a record has been added in the live db with a unique id of 1000, in the meantime another record has been added in the training db which also has the same unique id
-is there a consistent formula for identifying the name of the uniqueID field? e.g. tablename & 'ID'
- is the uniqueID a primary key?
-do you have multi field unique ID'S
-is the unique id always numeric?
-assuming tables are linked, how are you identifying which is linked to which db
-is the uniqueid an autonumber?

Depending on how you answer those questions you can adapt this pseudo code

 dim tdef as tabledef
 dim fld as field
  dim sqlstr as string
 dim TUID as number
  
 'loop through tables
 for each tdef in currentdb.tabledefs
     if right(tdef.name,1)<>"1" then 'only choose the live db tables
         'build sql
         TUID='function to determine name of uniqueID
         sqlstr="Update " & tdef.name & "1 Inner Join " & tdef.name & " ON " & tdef.name & "1." & TUID & " = " & tdef.name & "." & TUID & "SET "
         'loop through field names
         for each fld in tdef.fields
              'function to determine if field is autonumber or uniqueID in which case do not update
              if fld is not autonumber or uniqueID sqlstr=sqlstr & tdef.name & "1." & fld.Name & " = " & tdef.name & "." & fld.name
         next fld
         'add where clause
         sqlstr=sqlstr & "WHERE tdef.name & "1.UpdatedOn<>" & tdef.name & ".UpdatedOn"
         'execute query
         currentdb.execute sqlstr, dbfailonerror
      End If
 next tdef