使用 Access 执行 Make Table 查询以使用 ODBC 连接创建 SQL Server 表 [英] Using Access to do a Make Table query to create a SQL Server table using ODBC connection

问题描述

我正在将 Access 2010 数据库升级到 SQL 服务器.有一个可更新的Make Table"查询，用于创建表并填充它.目前，它填充另一个 ACCDB 文件中的后端表.我已将所有后端表移至 SQL Server 并尝试更改查询以在 SQL Server 上创建表.

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.

我从 Destination DB 属性中删除了 ACCDB 文件的路径，并在 Dest Connect Str 属性中放入了一个 ODBC 连接字符串.

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 调用失败 数据库中已经有一个对象名称 'MyTableName' (#2714)

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

我首先删除 SQL 服务器上的表，然后运行它的查询.如果使用本地表，它会正确删除该表，重新创建它，然后填充它.使用ODBC连接好像不能先删除，导致Make Table没用了.

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.

我在 Access 中将远程表配置为链接表，希望有一种方法可以直接使用它，而无需再次重新指定连接字符串.这似乎也不可能.

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.

正在寻找此问题的解决方案或任何可能的替代方案.我有近 20 个此类查询.

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

推荐答案

针对 ODBC 外部数据库的生成表查询将具有与此类似的 .SQL 属性

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;

因此，我们可以使用一些 VBA 代码来识别生成表查询的类型，将表拖放到 SQL Server 上，然后执行生成表查询.所以，而不是做

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"

其中 RunMakeTableQuery 在标准 VBA 模块中定义为

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

这篇关于使用 Access 执行 Make Table 查询以使用 ODBC 连接创建 SQL Server 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！