Using Access to do a Make Table query to create a SQL Server table using ODBC connection

I am upsizing an Access 2010 database to SQL server. There is an updatable "Make Table" query that is run to create a table and fill it. Currently, it fills a backend table in another ACCDB file. I have moved all the backend tables to SQL Server and trying to change the query to do make a table on SQL Server.

I removed the path to the ACCDB file from the Destination DB property, and put in an ODBC connection string in the Dest Connect Str property.

When I run the query, I get the error

ODBC call failed There is already an object name 'MyTableName' in the database (#2714)

I delete the table on the SQL server first and then run the query it works. If using a local table, it will properly delete the table, re-create it, and then fill it. Using ODBC connection it appears that it unable to delete it first, thus making the Make Table useless.

I have the remote table configured as a Linked table in Access, was hoping there was a way to use it directly without having to re-specify the connection string once again. This didn't seem possible either.

Looking for a solution or any possible alternatives to this problem. I have almost 20 queries that are of this type.

Solution

A make-table query targeting an ODBC external database will have a .SQL property similar to this

SELECT localTable.ID, localTable.textCol 
INTO (ODBC;DSN=myDb;Trusted_Connection=Yes;DATABASE=myDb;AutoTranslate=No;) externalTable
FROM localTable;

Therefore we can use a bit of VBA code to identify that type of make-table query, drop the table on the SQL Server, and then execute the make-table query. So, instead of doing

DoCmd.OpenQuery "YourMakeTableQueryName"

(as I suspect the code does now) you could use

RunMakeTableQuery "YourMakeTableQueryName"

where RunMakeTableQuery is defined in a standard VBA module as

Option Compare Database
Option Explicit

Public Sub RunMakeTableQuery(MakeTableQueryName As String)
    Dim cdb As DAO.Database, qdf As DAO.QueryDef, qdf2 As DAO.QueryDef
    Dim i As Long, j As Long, ConnectionString As String, TableName As String
    Const ExternalIntoTag = "INTO (ODBC;"

    Set cdb = CurrentDb
    Set qdf = cdb.QueryDefs(MakeTableQueryName)
    i = InStr(1, qdf.SQL, ExternalIntoTag, vbBinaryCompare)
    If i > 0 Then
        ' target table is external (SQL Server)
        i = i + Len(ExternalIntoTag)
        j = InStr(i, qdf.SQL, ")", vbBinaryCompare)
        ConnectionString = Trim(Mid(qdf.SQL, i, j - i))

        i = InStr(j + 1, qdf.SQL, "FROM", vbBinaryCompare)
        TableName = Trim(Mid(qdf.SQL, j + 1, i - j - 3))

        Set qdf2 = cdb.CreateQueryDef("")
        qdf2.Connect = "ODBC;" + ConnectionString
        qdf2.ReturnsRecords = False
        qdf2.SQL = "IF OBJECT_ID('" & TableName & "','U') IS NOT NULL DROP TABLE [" & TableName & "]"
        qdf2.Execute dbFailOnError
        Set qdf2 = Nothing
        qdf.Execute dbFailOnError
        Set qdf = Nothing
    Else
        ' target table is an Access table
        Set qdf = Nothing
        ' this will overwrite an existing target table with no prompts
        DoCmd.SetWarnings False
        DoCmd.OpenQuery MakeTableQueryName
        DoCmd.SetWarnings True
    End If
    Set cdb = Nothing
End Sub