访问& SQL-Server：通过表擦除重置自动编号字段

发布于 2024-11-28 15:18:52 字数 3181 浏览 9 评论 0原文

我试图在 SQL Server Express 表和生产 Access 版本之间保持活动表的副本同步。在其他表中，我能够擦除 SQL Server 表的内容，然后将整个乱七八糟的内容插回以捕获所有更改。但是，当我尝试对此表执行此操作时 - 我得到了信息 - 但自动编号字段从上一次迭代中最后一个未使用的数字开始递增。经过十几次“同步”操作后，我已经危险地接近用完该字段的自动编号了。

我尝试从 Microsoft Access 前端应用程序发出“DBCC CHECKIDENT”，这会引发错误，指出该语句不是以 SELECT、DELETE、PROCEDURE、DROP、ALTER 或类似内容开头。当从 SQL Server 管理控制台（我在当前测试环境中确实可以访问该工具，但在应用程序投入生产时可以访问该工具）发出此命令时，该命令确实有效。

然后，我使用 DoCmd.RunSQL 命令尝试“TRUNCATE TABLE”查询，它抛出了链接表不支持该操作的错误（大约）。该表链接到 Microsoft Access 前端（后端表位于 SQL Server Express 上）。

因此，快速总结一下：

前端是一个 Microsoft Access VBA 应用程序
数据存储在远程计算机上的 SQL Server Express 上
数据表在前端应用程序中链接
我将无法使用此应用程序时的 SQL Server 管理控制台命令投入生产后，它需要自己进行内务处理。
DoCmd.RunSQL 和 CurrentDB.Execute 似乎不允许使用 TRUNCATE TABLE -或 - 'DBCC CHECKIDENT' 命令。
当从 SQL Server 管理控制台发出时，上述两个函数都可以工作 - 请参阅上文了解为什么这不是一个可行的选项。
-除此之外，所有其他表的行为方式均符合我的预期，并根据需要重置其标识字段。

-- 编辑于 08/08/2011 @ 15:08 --

好吧 - 我已经尝试了多次尝试基于 VBA 的直通查询，所有这些都导致 ODBC -- 调用失败错误。以下是我创建的用于处理传递的模块的代码（从 dbforums.com 借用了代码）：

Function RunPassThrough(ByVal ConnectionString As String, ByVal SQL As String, Optional ByVal QueryName As String)

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef
    With qdf
        .Name = QueryName
        .Connect = ConnectionString
        .SQL = SQL
        .ReturnsRecords = (Len(QueryName) > 0)
        If .ReturnsRecords = False Then
            .Execute
        Else
            If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
            dbs.QueryDefs.Append qdf
        End If
        .Close
    End With
    Set qdf = Nothing
    Set dbs = Nothing

End Function

因此，我需要指定一个到数据库的连接字符串；以下所有操作均失败：

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;Uid=<my username>;Pwd=<my password>;"

结果：运行时错误 ODBC -- 调用失败（错误 #3146）

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;TRUSTED_CONNECTION=YES;"

结果：运行时错误 ODBC -- 调用失败（错误 #3146）

strConnect = "ODBC;DSN=instkeeper_beta;"

结果：询问我一次数据源名称使用数据源管理面板指定时，我收到运行时错误 ODBC -- 调用失败（错误 #3146）

strConnect = "ODBC;Server=ENV980-067\ENVIRON_TEST;Database=instkeeper_test;User ID=<my user name>;Password=<my password>;Trusted_Connection=False;"

结果：运行时错误 ODBC -- 调用失败（错误 #3146）

-- 已编辑08/08/2011 @ 16:41 --

连接字符串迭代出现更多失败，我正式对如何让这个野兽工作失去了想法。在尝试了之前的方法之后，现在在请求 DSN 后，使用该接口进行的任何传递都会失败。无法修复，必须恢复它们才能调用链接表并通过 JET 运行。

strConnect = "ODBC;DATA SOURCE=instkeeper_test;"

结果：运行时错误 ODBC -- 调用失败（错误 #3146）

strConnect = "ODBC;DRIVER=SQL Server;SERVER=ENV980-067\ENVIRON_TEST;"

结果：运行时错误 ODBC -- 调用失败（错误 #3146）

strConnect = "ODBC;DRIVER=SQL Server;Server=ENV980-067\ENVIRON_TEST;Database=instkeeper_test;User ID=<my user name>;Password=<my password>;"

结果：运行时错误 ODBC -- 调用失败（错误 #3146）

原文

I am trying to keep a copy of an activity table synchronized between a SQL Server Express table and the production Access version. In other tables, I am able to wipe the contents of the SQL Server table, and insert the whole mess back in to capture all of the changes. However, when I attempt to do that with this table - I get the information - but the Autonumber field increments from the last unused number from the previous iteration. After a dozen or so 'sync' operations, I am dangerously close to running out of autonumbers for this field.

I have tried issuing the 'DBCC CHECKIDENT' from the Microsoft Access front end application, which throws an error that the statement didn't start with SELECT, DELETE, PROCEDURE, DROP, ALTER or something like that. This command DOES work when issued from the SQL Server management console (a tool I do have access to in the current test environment, but will NOT when the application goes production).

I then tried the 'TRUNCATE TABLE' query using the DoCmd.RunSQL command and it threw the error that the operation isn't supported on linked tables (approximately). This table is linked to the Microsoft Access front-end (the back end table is on SQL Server Express).

So, for a quick summary:

Front End is an Microsoft Access VBA application
Data is stored on SQL Server Express on a remote machine
Data tables are linked in the front end application
I will NOT be able to use SQL Server Management Console commands when this application goes production, it needs to run it's housekeeping on it's own.
DoCmd.RunSQL and CurrentDB.Execute do not seem to allow the use of the TRUNCATE TABLE -or- the 'DBCC CHECKIDENT' command.
Both of the aforementioned functions -DO- work when issued from the SQL Server Management Console - see above as to why this isn't a viable option.
-ALL- other tables behave the way I'd expect them to besides this one, resetting their indentity fields as appropriate.

-- Edited 08/08/2011 @ 15:08 --

Alright - I have tried a number of attempts at a VBA-based pass-through query, all resulting in an ODBC -- call failed error. Here is the code for the module I created to handle pass-throughs (borrowed the code from dbforums.com):

Function RunPassThrough(ByVal ConnectionString As String, ByVal SQL As String, Optional ByVal QueryName As String)

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef
    With qdf
        .Name = QueryName
        .Connect = ConnectionString
        .SQL = SQL
        .ReturnsRecords = (Len(QueryName) > 0)
        If .ReturnsRecords = False Then
            .Execute
        Else
            If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
            dbs.QueryDefs.Append qdf
        End If
        .Close
    End With
    Set qdf = Nothing
    Set dbs = Nothing

End Function

As such, I need to specify a connection string to the database; all of the following have failed:

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;Uid=<my username>;Pwd=<my password>;"