I have two databases each with a table of identical structure.
From DB1 tblSource I want to update or add a record in DB2 Table1
Code:
sql = "SELECT * FROM [;DATABASE=" & ExtDB & "].Table1 Where Prefix = " & Chr$(34) & ThisPrefix & Chr$(34) & ";"
Set r = CurrentDb.OpenRecordset(sql)
Set e = CurrentDb.OpenRecordset("Select * from tblSource;")
If r.RecordCount = 0 Then
r.AddNew
Else
r.Edit
End If
For i = 1 To e.Fields.Count - 1 'start at 1 to skip idfield
r(i) = e(i)
Next
r.Update
r.Close
e.Close
But it seems overkill to For next loop thru all fields? Might it be possible to use an Update query ?