Использование Access для выполнения запроса Make Table для создания таблицы SQL Server с использованием соединения ODBC

Question

Я увеличиваю размер базы данных Access 2010 до сервера SQL. Существует обновляемый запрос "Создать таблицу", который запускается для создания таблицы и ее заполнения. В настоящее время он заполняет бэкэнд-таблицу в другом файле ACCDB. Я перенес все бэкэнд-таблицы на SQL Server и пытаюсь изменить запрос, чтобы создать таблицу на SQL Server.

Я удалил путь к файлу ACCDB из свойства Destination DB и вставил строку подключения ODBC в свойство Dest Connect Str.

Когда я запускаю запрос, я получаю сообщение об ошибке

Ошибка вызова ODBC. В базе данных уже есть имя объекта "MyTableName" (#2714)

Сначала я удаляю таблицу на сервере SQL, а затем запускаю запрос, который работает. Если вы используете локальную таблицу, она правильно удалит таблицу, создаст ее заново, а затем заполнит. При использовании соединения ODBC кажется, что он не может удалить его первым, что делает Make Table бесполезным.

У меня есть удаленная таблица, настроенная как Связанная таблица в Access, я надеялся, что есть способ использовать ее напрямую, без повторного указания строки подключения. Это тоже не казалось возможным.

Ищете решение или любые возможные альтернативы этой проблеме. У меня есть почти 20 запросов этого типа.

Answer 1

Запрос рабочей таблицы для внешней базы данных ODBC будет иметь .SQL свойство похожее на это

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

Поэтому мы можем использовать немного кода VBA для идентификации этого типа запроса make-table, удалить таблицу на SQL Server и затем выполнить запрос make-table. Итак, вместо того, чтобы делать

DoCmd.OpenQuery "YourMakeTableQueryName"

(как я подозреваю, код делает сейчас) вы могли бы использовать

RunMakeTableQuery "YourMakeTableQueryName"

где RunMakeTableQuery определяется в стандартном модуле VBA как

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