From Excel VBA- Check if Access Table Exists / If Not, Create/Copy

Question

I am working with an Access databasein VBA through Excel. I want to check if a table in the DB exists, and then if not, I want to create the table using the same fields/definitions as a table labeled "Blank" in that database. I can simply copy/rename Blank if necessary.

I found a way to check if the table exists using the following (not sure if it is the best way though).

Set objCatalog = CreateObject("ADOX.catalog")
objCatalog.ActiveConnection = dbConn
For i = 0 To objCatalog.Tables.Count - 1
    If objCatalog.Tables.Item(i).Name = tbName Then tbExists = True
Next

This works, but I'm struggling to find a way to copy or create a new table using Blank's fields/definitions when the destination table does not exist (tbExists = False).

user_4313815 · Answer

This might be another way to check if a table exists by checking if the Table Definition returns anything.

A TableDef object represents the stored definition of a base table or a linked table (Microsoft Access workspaces only).

Set exampleDB = workSpace.OpenDatabase(DatabaseName)

Set tableDefinition = exampleDB.TableDefs(TableName)
tableExists = Err.Number = 0

For more information

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/tabledef-object-dao

user_7607190 · Answer

This worked for me:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\June\Umpires.accdb"
cn.Execute "SELECT Blank.* INTO [" & tbName & "] FROM Blank"
cn.Close
Set cn = Nothing

The new table won't actually show in the Navigation Pane without doing something else, like Compact & Repair or Close/Reopen database or run code that selects the table in Navigation Pane or somehow refreshing the Pane (hide/unhide Pane does that).

Some field property settings won't be carried over (AllowZeroLength = No). Calculated type field will cause run-time error as will multi-value field.

DoCmd.CopyObject and DoCmd.TransferDatabase can exactly copy table. DoCmd.Rename can simply rename table. All will require code that declares and opens Access database object variable.

user_2227085 · Answer

Creating access object from excel vba

Sub TableExistOrCreate()

    Dim appAccess As Object, tbl As Object
    Set appAccess = CreateObject("Access.Application")

    appAccess.OpenCurrentDatabase ("C:\Users\santosh\Desktop	est.accdb")

    On Error Resume Next
    Set tbl = appAccess.currentdata.AllTables("Test")
    On Error GoTo 0

    If tbl Is Nothing Then
        appAccess.docmd.CopyObject , "Test", 0, "Blank"
    End If

    Set appAccess = Nothing

End Sub

From Excel VBA- Check if Access Table Exists / If Not, Create/Copy

3 Answers