Update to another DB

[Middlemarch]

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

  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 ?

[June7]

You understand an UPDATE SQL uses SET clause where you have to specify field pairs? How many fields? Loop may be simpler code.

If you set a link to Table1, could possibly build an UPSERT SQL to accomplish both INSERT and UPDATE - again, uses SET clause. Review https://stackoverflow.com/questions/...crosoft-access

[Middlemarch]

Thank you June, I have something working but it's big having to specify each of the 20 fieldnames. You can't use * ? That may fail anyway if then can't exclude field0 (id).

[June7]

No, cannot use wildcard in UPDATE. Exactly how would you envision anyway?

So why not stick with the loop?