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, Commented Dec 6, 2018 at 23:22
@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
– LazyBear, Commented Dec 6, 2018 at 23:26

Erik A · Accepted Answer · 2018-12-07 18:39:52Z

4

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

edited Dec 7, 2018 at 18:39

answered Dec 7, 2018 at 8:21

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LazyBear Over a year ago

I see. The alias is a neat solution. So, just to be clear, I don't need to specify a ThisDatabase (or something like it) to denote that "INSERT INTO " & SelectedTable & " t " is referencing the database we're working from? SelectedTable would just be a table name, like cars for example.

Erik A Over a year ago

Yup, the ` t` assigns the name t to SelectedTable. In the rest of your query, you can just refer to it as t. Same for s and s1. You only need to specify the full name once. Btw, you can also use ` As t` instead of just t, but that's not needed.

LazyBear Over a year ago

The only problem is that Access didn't like the alias t for the table for the current DB (line 1 of the SQL code). I read in other posts that do that as a rule, more or less, when only referencing a table (still unclear based on my experience level, as I lack a bit of context). Not a big deal. I just deleted the alias t and referenced the table directly in the WHERE tests. Other than that, this is the answer I needed to my question. Thanks. Now I'm having a logic issue, but that's another question for another post.

June7 · Accepted Answer · 2018-12-07 04:24:32Z

0

Following code from my db SENDS data to OTHER db:

strExtract = gstrBasePath & "Program\Editing\ConstructionExtract.accdb"

CurrentDb.Execute "INSERT INTO Bituminous IN '" & strExtract & "' SELECT * FROM ConstructionBIT;"

gstrBasePath is a Global constant declared in a general module:

Global Const gstrBasePath = "\\servernamehere\Crm\Lab\Database\"

You can use literal string path within your procedure.

Following PULLS data from OTHER db:

CurrentDb.Execute "INSERT INTO Employees SELECT * FROM Employees IN '\\servername\filename.accdb'"

edited Dec 7, 2018 at 4:24

answered Dec 6, 2018 at 23:26

June7

21.7k8 gold badges28 silver badges36 bronze badges

3 Comments

LazyBear Over a year ago

So you do have to specify the path of the current DB even though your working from that path? Seems too hard coded. I guess I expected something similar to the ThisWorksheet in excel.

June7 Over a year ago

CurrentDb is for the Execute command but the SQL statement itself does not have reference to current db. The path I show is for the OTHER db. Now that I look at this again, I see I am doing the reverse of what you want. I am SENDING data to the OTHER file, while you are PULLING. See my revised answer.

ComputerVersteher Over a year ago

@LazyBear: You need to specify what Database executes the SQL statement. Instead of CurrentDb.Execute you can also use

Set db = OpenDatabase(strExtract): db.Execute "INSERT INTO  [" & SelectedTable & "] IN '" & CurrentProject.Path & "\" & CurrentProject.Name & "' SELECT * FROM [" & SelectedTable & "]"

what executes the SQL in the external DB, but this is not recommended. And enclose your table- and field-names in square brackets if they come from vars and you can't be 100% sure they dont't contain unwanted chars (like a blank in your code or a quote in @junes).

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related