用新数据替换 SQL 数据表 [英] Replacing SQL Data Table with New Data

问题描述

我有以下代码，它将数据从访问权限上传到 SQL Server 表.问题是当我尝试更新它时它不会覆盖/替换表.我收到一条错误消息，指出该表已存在.我需要添加一些代码来删除表格，以便可以上传新表格，或者添加一种将新/编辑过的记录附加到表格的方法.

I have the following code, which uploads data from access to a SQL Server table. The problem is it wont overwrite/replace the table when I try to update it. I get an error saying the table already exists. I need to either add some code to delete the table so the new one can be uploaded or a way to append the new/edited records to the table.

Public Function Update()
DoCmd.TransferDatabase _
        acExport, _
        "ODBC Database", _
        "ODBC;" & _
            "Driver={SQL Server Native Client 10.0};" & _
            "Server=SERVER;" & _
            "Database=DB;" & _
            "UID=ID;" & _
            "PWD=PW;", _
        acTable, _
        "CDData", _
        "AC_CDData", _
        False
End Function

所以 AC_CDData 表是需要替换的表

So the AC_CDData table is the one that needs to be replaced

推荐答案

如果你想在转移新的目标表之前删除现有的目标表，那么代码会更像这样:

If you want to DROP the existing destination table before transferring the new one then the code would be more like this:

Option Compare Database
Option Explicit

Public Function TransferTableToSqlServer()
    Dim cdb As dao.Database, qdf As dao.QueryDef
    Dim err As dao.Error
    Const DestinationTableName = "AC_CDData"
    Const ConnectionString = _
            "ODBC;" & _
                "Driver={SQL Server Native Client 10.0};" & _
                "Server=(local)\SQLEXPRESS;" & _
                "Database=YourDatabaseName;" & _
                "UID=YourSqlUserId;" & _
                "PWD=YourSqlPassword;"
    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = ConnectionString
    qdf.sql = _
            "IF EXISTS " & _
                "(" & _
                    "SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
                    "WHERE TABLE_NAME='" & DestinationTableName & " '" & _
                ") " & _
            "DROP TABLE [" & DestinationTableName & "]"
    qdf.ReturnsRecords = False
    On Error GoTo TransferTableToSqlServer_qdfError
    qdf.Execute dbFailOnError
    On Error GoTo 0
    Set qdf = Nothing
    Set cdb = Nothing
    DoCmd.TransferDatabase _
            acExport, _
            "ODBC Database", _
            ConnectionString, _
            acTable, _
            "CDData", _
            DestinationTableName, _
            False
    Exit Function

TransferTableToSqlServer_qdfError:
    For Each err In dao.Errors
        MsgBox err.Description, vbCritical, "Error " & err.Number
    Next
End Function

这篇关于用新数据替换 SQL 数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！