Update 1 table to be identicle to another table in another DB

[Guest]

I have a main database with a backup database. The main database has one
table that is very important so with each new record added to the main table,
I run an append query to adds that record to the backup database table.
Updates are made to the initial records from time to time, and what I'd like
to do is run an update query that will compare the main database table with
the backup database table to make updates that were made to the main table
after the append query ran (this only adds new records). I know I could
import the table to the backup each time, but this can be a headache. This
is easy to do within the same database, but any help on update queries
between two databases would be a life saver!

Thanks

 

[Rick B]

If it were me, I'd avoid the chance for missed data or the much longer
processing time needed to do all this, and simply backup my database from
time to time. Just drag a copy in Windows Explorer each evening.

Maybe you'll get some other responses that address how you'd like to do it,
but it just seem like it is setting you up for problems.

If the data is that important, back it all up.

 

[KARL DEWEY]

I would add another field to the backup table so as to record the record
changes.
If you post your table structure then someone could help with the append
query.

 

[Guest]

I already have an append query which works quite fine. Also, I'd rather just
click a query to update the backup table than to import the table into the
backup database. I would like help with an update query between two
databases that will update any and all changes made to the main database
table.

 

[John Vinson]

I already have an append query which works quite fine. Also, I'd rather just
click a query to update the backup table than to import the table into the
backup database. I would like help with an update query between two
databases that will update any and all changes made to the main database
table.

I would suggest that the simplest way to do this is to instead force
all updates to be done using a Form; in the AfterUpdate of the form
you could open a recordset on the linked table in the backup database,
selecting the record currently on the form, and update it directly.

It is possible to write an Update query which compares every field in
every record in the two tables and only updates those records which
have changed - but this will be monstrously slow, since you probably
won't be able to take advantage of indexes on all fields and it must
do a full table scan, perhaps even in both tables.

John W. Vinson[MVP]

 

[Guest]

Could you share some code that would accomplish this via the form idea? Just
give me an example of a table with two fields and I can adjust it to update
every field I have. The tables would be identical in two different databases
with one exception. The backup database table, I changed the Order ID Key
field from an autonumber to just a number field so I always get the same
number from the main database when it appends to the backup database and it
is set to no duplicates so it only appends new records.

 

[John Vinson]

Could you share some code that would accomplish this via the form idea? Just
give me an example of a table with two fields and I can adjust it to update
every field I have. The tables would be identical in two different databases
with one exception. The backup database table, I changed the Order ID Key
field from an autonumber to just a number field so I always get the same
number from the main database when it appends to the backup database and it
is set to no duplicates so it only appends new records.

Sure.

Private Sub Form_AfterUpdate()
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set ws = DBEngine.Workspaces(0) ' current workspace
Set db = ws.OpenDatabase("G:\path\BackupDatabase.mdb")
Set rs = db.OpenRecordset("Tablename", dbOpenDynaset)
rs.FindFirst "[OrderID] = " & Me!txtOrderID ' Does this order exist?
If rs.NoMatch Then
' New record, add it
rs.AddNew
Else
rs.Edit
End If
' Add data from the Form to the new or existing record
rs!OrderID = Me!txtOrderID
rs!CustomerID = Me!cboCustomerID
rs!thisfield = Me!thiscontrol
<etc>
rs.Update ' write the data to the table
rs.Close
Set rs = Nothing
Set ws = Nothing
db.Close
Set db = Nothing
End Sub

John W. Vinson[MVP]