sql server - Force SET IDENTITY_INSERT to take effect faster from MS Access

Question

I'm working on upsizing a suite of MS Access backend databases to SQL Server. I've scripted the SQL to create the table schemas in SQL Server. Now I am trying to populate the tables. Most of the tables have autonumber primary keys. Here's my general approach:

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

The first insert happens immediately. Subsequent insert attempts fail with the error: "Cannot insert explicit value for identity column in table 'TblName' when IDENTITY_INSERT is set to OFF."

I added a Resume statement for that specific error and also a timer. It turns out that the error continues for exactly 600 seconds (ten minutes) and then the insert proceeds successfully.

Does MS Access automatically refresh its ODBC sessions every 10 minutes? Is there a way to force that to happen faster? Am I missing something obvious?

Background info for those who will immediately want to say "Use the Upsizing Wizard":
I'm not using the built-in upsizing wizard because I need to be able to script the whole operation from start to finish. The goal is to get this running in a test environment before executing the switch at the client location.

Answer 1

I found an answer to my first question. The ten minutes is a setting buried in the registry under the Jet engine key:

'Jet WinXP/ Win7 32-bit:'
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesODBCConnectionTimeout

'Jet Win7 64-bit:'
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftJet4.0EnginesODBCConnectionTimeout

'ACE WinXP/ Win7 32-bit:'
HKEY_LOCAL_MACHINESOFTWAREMicrosoftAccess Connectivity EngineEnginesODBCConnectionTimeout

'ACE Win7 64-bit:'
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftAccess Connectivity EngineEnginesODBCConnectionTimeout

It is documented here for ACE:

ConnectionTimeout: The number of seconds a cached connection can remain idle before timing out. The default is 600 (values are of type REG_DWORD).

This key was set to the default of 600. That's 600 seconds or 10 minutes. I reduced that to ten seconds and the code sped up accordingly.

This is by no means the full solution, because setting the default that low is sure to cause issues elsewhere. In fact, Tony Toews once recommended that the default might better be increased when using DSN-less connections.

I'm still hoping to find an answer to the second part of my question, namely, is there a way to force the refresh to happen faster.

---

UPDATE: The reason this is even necessary is that the linked tables use a different session than ADO pass-through queries. I ran a test using SQL Profiler. Here are some brief results:

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

What's going on here is that my ADO commands are running in a different session (#49) than my linked tables (#50). Access sees that I'm setting the value for an identity column so it helpfully sets IDENTITY_INSERT ON for that table. However, it never sets IDENTITY_INSERT OFF. I turn it off manually, but that's happening in a different session.

This explains why setting the ODBC session timeout low works. It's just an ugly workaround for the fact that Access never turns off IDENTITY_INSERT on a table once it turns it on. Since IDENTITY_INSERT is sessions-specific, creating a new session is like hitting the reset button on IDENTITY_INSERT. Access can then turn it on for the next table and the setting will take effect because it's a brand new session.