In my previous post

Advanced SQL To Connect With External Databases

Let’s look at how we can use plain SQL Statements (SELECT, INSERT, UPDATE, DELETE & Make Table) to retrieve data from external Access databases.

I demonstrated how you can build queries that directly query with external databases. (queries that query, I know so eloquent of me)

Today however, I thought I’d turn my attention to using VBA to interact with external databases.

VBA Functions

Does a Table Exist

Let’s look at a typical example of checking whether a table exists within a database.

The typical code for working with a local table would be something along the lines of:

Public Function Table_Exist(ByVal sTable As String) As Boolean
    On Error GoTo Error_Handler
    Dim sName                 As String

    sName = CurrentDb.TableDefs(sTable).Name
    Table_Exist = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number <> 3265 Then 'Item not found in this collection. / Table doesn't exist
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Table_Exist" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

And we can use it by simply doing:

Debug.Print Table_Exist("Contacts")

So the question therefore becomes

How can I adapt this code to work with an external database?

If we review the code, we can see that the key to our issues lies with the fact that it uses the CurrentDb to check the TableDefs collection for our specified table. Thus, we need to change the CurrentDb to an external db.

We can actually easily do this using the OpenDatabase() Method:

Workspace.OpenDatabase method (DAO)

Opens a specified database in a Workspace object and returns a reference to the Database object that represents it.

Knowing this, we can then adapt our function into something like:

Public Function Table_Exist(ByVal db As DAO.Database, ByVal sTable As String) As Boolean
    On Error GoTo Error_Handler
    Dim sName                 As String

    sName = db.TableDefs(sTable).Name
    Table_Exist = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number <> 3265 Then 'Item not found in this collection. / Table doesn't exist
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Table_Exist" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

Now, we can still use this new version locally by doing:

Debug.Print Table_Exist(Currentdb, "ProductCount")

Or for any external database with no password by doing:

Sub Table_Exist_Test()
    Dim db                    As DAO.Database
    
    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Databases\test.mdb", True, False)
    Debug.Print Table_Exist(db, "Contacts")
    If Not db Is Nothing Then
        db.Close
        Set db = Nothing
    End If 
End Sub

Or for any external database with a password by doing:

Sub Table_Exist_Test()
    Dim db                    As DAO.Database
    
    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Databases\test.mdb", True, False, ";PWD=cmrhaL5tdhh4")
    Debug.Print Table_Exist(db, "Contacts")
    If Not db Is Nothing Then
        db.Close
        Set db = Nothing
    End If 
End Sub

So I hope this illustrated how you can easily adapt any existing function to work with an external database by simply changing the CurrentDb object.

Executing a Query Via VBA

Sometimes, all we want to do is run a query against an external database. Normally, we’d simply do something like:
CurrentDb.Execute “ALTER TABLE [tbl_Contacts] ALTER COLUMN [FirstName] TEXT(55)”, dbFailOnError

So how do we adapt that same code to execute against an external database?

Once again, knowing that we can utilize the OpenDatabase() method to create an external database object, we can simply do something more like:

Sub RunQuery(ByVal db As DAO.Database, ByVal sSQL As String)
On Error GoTo Error_Handler

    db.Execute sSQL, dbFailOnError
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RunQuery" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Now, we can use this new version locally by doing:

Call RunQuery(CurrentDb, "ALTER TABLE [tbl_Contacts] ALTER COLUMN [FirstName] TEXT(55)")

Or for any external database with no password by doing:

Sub RunQuery_Test()
    Dim db                    As DAO.Database
    
    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Databases\test.mdb", True, False)
    Call RunQuery (db, "ALTER TABLE [tbl_Contacts] ALTER COLUMN [FirstName] TEXT(55)")
    If Not db Is Nothing Then
        db.Close
        Set db = Nothing
    End If 
End Sub

Or for any external database with a password by doing:

Sub RunQuery_Test()
    Dim db                    As DAO.Database
    
    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Databases\test.mdb", True, False, ";PWD=cmrhaL5tdhh4")
    Call RunQuery (db, "ALTER TABLE [tbl_Contacts] ALTER COLUMN [FirstName] TEXT(55)")
    If Not db Is Nothing Then
        db.Close
        Set db = Nothing
    End If 
End Sub

Hopefully this illustrates just how easy it actually is to execute queries against external databases.