Handling an external Access database within an Access Database with VBA?

Question

So I'm a little confused as to how to handle an external database and current database within VBA code. Below is a sub whose purpose is to update the current Access database with unique entries found in the external Access database.

The external SourceDBPath and SelectedTable is passed in, and I specify the external database and table with the string variable SourceDBTable. Then, in the SQL, I try to pull out entries with values that don't match their coresponding field so only unique entries between the two DBs are inserted into the source database.

(For Example, where source = external:
NOT EXIST sourceDB.SelectedTable.Field1 = currentDB.SelectedTable.Field1 And sourceDB.SelectedTable.Field2 = currentDB.SelectedTable.Field2 And sourceDB.SelectedTable.Field3 = currentDB.SelectedTable.Field3, etc.)

SO, my questions are:

1) Do I need to specify the current database within the SQL (like currentDB.table.field), or will it default to the current database if a table or field is called without a prefix (just table or field, like in the code below)?
2) Ultimately, am I going about this in the right way?

My code:

Private Sub UpdateDatabaseTable(SourceDBPath As String, SelectedTable As String)
    Dim SourceDBTable As String  

    On Error GoTo DBError  

    SourceDBTable = "[;DATABASE=" & SourceDBPath & "]." & SelectedTable

    Call DoCmd.RunSQL("INSERT INTO " & SelectedTable & " " & _
                        "SELECT Field1, Field2, Field3 " & _
                        "FROM " & SourceDBTable & " " & _
                        "WHERE NOT EXISTS( SELECT * " & _
                                            "FROM " & SourceDBTable & " " & _
                                            "WHERE (Field1=" & SourceDBTable & ".Field1 And Field2=" & SourceDBTable & ".Field2 And Field3=" & SourceDBTable & ".Field3"));")

    GoTo EndSub

DBError:
    MsgBox "Database Error!" & vbCrLf & "Error #" & Str(Err.Number) & ":  " & Err.Source & vbCrLf & Err.Description, vbExclamation, "Database Error"

EndSub:

End Sub

NOTE: I derived my SQL by extrapolating and modifying the code found in the solution HERE

Do not need to specify CurrentDb. Why not just links to the other db tables? — June7
@June7 Normally I would start there, but my requirements for this project are to avoid linking tables. Duplicate entries are defined by specific fields since the others are more fluid. So an entry that would traditionally look unique during an update, in our case, is actually a duplicate. — LazyBear

Erik A Erik A · Accepted Answer · 2018-12-07T08:21:48

You have 2 main mistakes in your code, otherwise, it should work.

Don't specify the tablename for each field. Use an alias instead
You want to escape both the tablename, and the database name, not only the database name

    Private Sub UpdateDatabaseTable(SourceDBPath As String, SelectedTable As String)
        Dim SourceDBTable As String  

        On Error GoTo DBError  

        SourceDBTable = "[;DATABASE=" & SourceDBPath & "].[" & SelectedTable & "]"

        DoCmd.RunSQL "INSERT INTO " & SelectedTable & " t " & _
                            "SELECT Field1, Field2, Field3 " & _
                            "FROM " & SourceDBTable & " s" & _
                            "WHERE NOT EXISTS( SELECT * " & _
                                                "FROM " & SourceDBTable & " s1 " & _
                                                "WHERE (t.Field1=s1.Field1 And t.Field2=s1.Field2 And t.Field3=s1.Field3));"

        GoTo EndSub

    DBError:
        MsgBox "Database Error!" & vbCrLf & "Error #" & Str(Err.Number) & ":  " & Err.Source & vbCrLf & Err.Description, vbExclamation, "Database Error"

    EndSub:

    End Sub

I've also removed the deprecated Call keyword. Optionally, you can adjust this further by using CurrentDb.Execute, but that's not needed

Handling an external Access database within an Access Database with VBA?

2 Answers