0

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.

Ron
  • 978
  • 3
  • 13
  • 27
  • When you say "Make table query", is that a series of SQL queries (maybe in a procedure?), or are we talking VBA, or something else? If the columns remain constant, you may want to consider switching to [`TRUNCATE`](http://msdn.microsoft.com/en-us/library/ms177570.aspx) instead of dropping the table. – jpmc26 Feb 12 '14 at 00:43
  • It is an Access query option called "Make Table" where it will run a query and create a table and fill a table. Is is just a single SQL query created by Access by filling in other properties in it's query builder, wizard, etc. – Ron Feb 12 '14 at 01:27
  • @Ron If you can convert from a "make table" to an "append query", empty out the destination table as jpmc26 suggested and then execute the append. – HansUp Feb 12 '14 at 02:00
  • Not an access expert, so will need to see how the query is called and see if I can hook into before it is called and do the empty. Hopefully, Access will provide some type of hooks for this. Was hoping that I could modify the query and work as is without having to go back into the code where places are called and modify, but may not be this lucky ;) thx – Ron Feb 12 '14 at 02:52

1 Answers1

2

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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • thanks, I think this may be the way I will have to go. I did discover that all the queries were called from macros, so possibly I can use this along with adding some pre-steps to the macro to delete the tables. – Ron Feb 14 '14 at 04:18
  • Just a note to anyone finding this page who is looking for an example of a Make-Table query query from Access to SQL Server... The syntax needs to be written with square brackets around the ODBC connection and a period before the table name with no spaces. If you save that syntax in an Access query and then open the query later, the square brackets are replaced by parenthesis and the period removed. It needs to look like this: SELECT localTable.ID, localTable.textCol INTO [ODBC;DSN=myDb; Trusted_Connection=Yes; DATABASE=myDb; AutoTranslate=No;].externalTable FROM localTable; – Ben May 14 '18 at 14:21