Import from Access to SQL Server works only for first table in set

I am trying to import MsAccess 2007 data into an SQL Server database. This worked earlier, same code, same machine, except SQL Server Express 2005. After some unrelated problems, I converted to 2008 and now I am having trouble loading the data.

It's VBA code running in Access, first executes a stored procedure named ClearDB, which wipes every table. That still works. Then it loops around through all the tables, maintaining a specific order due to relationship constraints. For each table, the code executes the following three queries:

SET IDENTITY_INSERT TableOfZones ON

INSERT INTO dbo_TableOfZones SELECT Jet_TableOfZones.* FROM Jet_TableOfZones

SET IDENTITY_INSERT TableOfZones OFF

The IDENTITY_INSERT queries are pass-through, the Insert is local. This has recently started working only for the first table, then it crashes claiming that none of the records were inserted due to key violations. It's always the first table that works, regardless of which table that is, as long as it isn't one that requires matching records from another table to already be in place. Sounds like a perfectly normal screw-up in indexing or such, except that if I let it sit for a while (10-15 minutes) it suddenly works again, for one table. That Insert works, then it bombs on the next one. Again, if I let it sit for a while, that next one will suddenly work and crash on the one after that.

I have to use the IDENTITY_INSERT option, since I am importing existing data with relationships already created, else I would simply let SQL Server generate its own ID columns.

Here's the latest try:

SET IDENTITY_INSERT TableOfZones ON

INSERT INTO dbo_TableOfZones(ZoneAutoID, Zone) SELECT Jet_TableOfZones.ZoneAutoID,    Jet_TableOfZones.Zone FROM Jet_TableOfZones               119 

SET IDENTITY_INSERT TableOfZones OFF

 119           119           0 

SET IDENTITY_INSERT TableOfSystems ON

INSERT INTO dbo_TableOfSystems(SystemAutoID, System) SELECT Jet_TableOfSystems.SystemAutoID, Jet_TableOfSystems.System FROM Jet_TableOfSystems                0 

SET IDENTITY_INSERT TableOfSystems OFF

 221           0             221 

Same results, first insert works fine, second one bombs. The first number is the record count in the source table, the second is the record count of the destination table, AFTER executing the Insert query, the third is simply开发者_运维问答 the difference between the two.

In SQL Server 2008, in order to use IDENTITY INSERT you need to specify an explicit field list in both your select and your insert, i.e.:

SET IDENTITY_INSERT TableOfZones ON

INSERT INTO     dbo_TableOfZones (
                ID_Field, 
                field1, 
                field2, 
                field3...)
SELECT          Jet_TableOfZones.IDField, 
                Jet_TableOfZones.field1, 
                Jet_TableOfZones.field2, 
                Jet_TableOfZones.field3... 
FROM            Jet_TableOfZones

SET IDENTITY_INSERT TableOfZones OFF

I would imagine that connections from Access would be subject to the same constraints.

Okay, this looks like better method. Sorry about the soup in the comments. Here's a copy of the commands sent out this time. I wonder if maybe I have to name the source table for each field? I'll give that a try...

SET IDENTITY_INSERT TableOfZones ON

INSERT INTO dbo_TableOfZones(ZoneAutoID, Zone) SELECT ZoneAutoID, Zone FROM Jet_TableOfZones       119 

SET IDENTITY_INSERT TableOfZones OFF

 119           119           0 

SET IDENTITY_INSERT TableOfSystems ON

INSERT INTO dbo_TableOfSystems(SystemAutoID, System) SELECT SystemAutoID, System FROM Jet_TableOfSystems         0 

SET IDENTITY_INSERT TableOfSystems OFF

 221           0             221