强制 SET IDENTITY_INSERT 从 MS Access 更快生效

Question

我正在致力于将一套 MS Access 后端数据库升级到 SQL Server。我已经编写了 SQL 脚本来在 SQL Server 中创建表架构。现在我正在尝试填充表格。大多数表都有自动编号主键。这是我的一般方法：

For each TblName in LinkedTableNames
    'Create linked table "temp_From" that links to the existing mdb'
    'Create linked table "temp_To" that links to the new SQL server table
    ExecutePassThru "SET IDENTITY_INSERT " & TblName & " ON"
    db.Execute "INSERT INTO temp_To SELECT * FROM temp_From", dbFailOnError
    ExecutePassThru "SET IDENTITY_INSERT " & TblName & " OFF"
Next TblName

第一次插入立即发生。后续插入尝试失败并出现错误：“当 IDENTITY_INSERT 设置为 OFF 时，无法在表 'TblName' 中插入标识列的显式值。”

我为该特定错误添加了一个 Resume 语句以及一个计时器。事实证明，错误持续了整整 600 秒（十分钟），然后插入成功进行。

MS Access 是否每 10 分钟自动刷新一次 ODBC 会话？有没有办法迫使它更快地发生？我是否遗漏了一些明显的东西？

为那些立即想说“使用升迁向导”的人提供的背景信息：
我没有使用内置的升迁向导，因为我需要能够编写从开始到结束的整个操作的脚本。目标是在客户端位置执行切换之前在测试环境中运行它。

Answer 1

我找到了第一个问题的答案。十分钟是隐藏在 Jet 引擎密钥下的注册表中的设置：

'Jet WinXP/ Win7 32-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout

'Jet Win7 64-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout

'ACE WinXP/ Win7 32-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access Connectivity Engine\Engines\ODBC\ConnectionTimeout

'ACE Win7 64-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MicrosoftAccess Connectivity Engine\Engines\ODBC\ConnectionTimeout

它是 此处记录了 ACE：

ConnectionTimeout：缓存的连接在超时之前可以保持空闲的秒数。默认值为 600（值的类型为 REG_DWORD）。

此键设置为默认值 600。即 600 秒或 10 分钟。我将其缩短为十秒，代码的速度也相应加快。

这绝不是完整的解决方案，因为将默认值设置得这么低肯定会在其他地方引起问题。事实上，Tony Toews 曾经建议默认值可能更好使用无 DSN 连接时增加。

我仍然希望找到问题第二部分的答案，即是否有办法强制刷新更快。

---

更新： 这甚至是必要的原因是链接表使用与 ADO 传递查询不同的会话。我使用 SQL Profiler 进行了测试。以下是一些简短的结果：

TextData                               SPID
-------------------------------------------
SET IDENTITY_INSERT dbo.TblName ON       50
SET IDENTITY_INSERT "dbo"."TblName" ON   49
exec sp_executesql N'INSERT INTO "d...   49
SET IDENTITY_INSERT dbo.TblName OFF      50
SET IDENTITY_INSERT dbo.NextTbl ON       50
SET IDENTITY_INSERT "dbo"."NextTbl" ON   49
exec sp_executesql N'INSERT INTO "d...   49

这里发生的情况是我的 ADO 命令与我的链接表 (#50) 在不同的会话 (#49) 中运行。 Access 发现我正在设置标识列的值，因此它会为该表设置 IDENTITY_INSERT ON。但是，它永远不会将 IDENTITY_INSERT 设置为 OFF。我手动将其关闭，但这是在不同的会话中发生的。

这解释了为什么将 ODBC 会话超时设置得较低是有效的。这只是一个丑陋的解决方法，因为 Access 一旦打开表上的 IDENTITY_INSERT 就永远不会关闭它。由于 IDENTITY_INSERT 是特定于会话的，因此创建新会话就像按 IDENTITY_INSERT 上的重置按钮一样。然后，Access 可以为下一个表打开它，并且设置将生效，因为这是一个全新的会话。

Answer 2

有两个想法，虽然不确定是否有用，因为这对我来说是陌生的领域。

“MS Access 是否每 10 分钟自动刷新一次 ODBC 会话？是否有办法强制刷新速度更快？我是否遗漏了一些明显的内容？”

在 Access 2003 选项对话框的“高级”选项卡上，有“ODBC 刷新间隔”的设置以及重试的设置。调整这些有帮助吗……或者有任何效果吗？

我想知道您是否可以通过将 SQL Server 列创建为普通数字而不是自动编号、插入数据，然后在插入数据后使用 ALTER TABLE ... ALTER COLUMN 更改它们来避免此问题。

如果表包含数据，Access 不会让我将数字列转换为自动编号，但 ISTR SQL Server 在这方面更灵活。

Answer 3

我找到了一个方便但不太漂亮的解决方案，将许多访问表导出到sql server并避免identity_insert问题：

1. 我打开一个本地表记录集，其中列出了要导出的所有表，然后循环遍历记录（每个表） 。在每个循环中我...

2. 创建一个访问应用程序对象

3. 在应用程序对象上使用传输数据库方法

4. 终止/退出应用程序对象并再次循环

以下是示例代码：

Public Sub exporttables()
Dim rst As Recordset
Dim access_object

'First create a local access table which lists all tables to be exported'
Set rst = CurrentDb.OpenRecordset("Select txt_tbl from ####your_table_of_tables####")

 With rst
 While Not .EOF
    'generate a new object to avoid identity insert problem'
    Set access_object = CreateObject("Access.Application")
    'with access object open the database which holds the tables to be exported'
    access_object.OpenCurrentDatabase "####C:\yoursourceaccessdb####.accdb"
    access_object.DoCmd.TransferDatabase acExport, "ODBC Database", "ODBC;DSN=####your connection string to target SQL DB;", acTable, .Fields("txt_tbl"), .Fields("txt_tbl"), False, False
    Debug.Print .Fields("txt_tbl") & " exported"
    access_object.CloseCurrentDatabase
    access_object.Application.Quit
    Set access_object = Nothing
    .MoveNext
Wend
End With

Set rst = Nothing
End Sub