AutoNumber problems with Upsizing (1 Viewer)

I want to upsize data from an access database to a SQL Sever database, using code in an access database. (Note that I prefer to do this in code, although I also tried using the SQL Migration wizard, and had this same problem)

The problem comes with tables with autonumbers.

When I try to do this, it fails on tables with autonumber

insert * into sqltable select accesstable.* from accesstable

so, I try to set the identity insert flag

SET IDENTITY_INSERT sqltable ON
insert * into sqltable select accesstable.* from accesstable
SET IDENTITY_INSERT sqltable OFF

The problem is that the off statement does not work immediately. I have to close the database, and reopen, and then I can process the next table. Any ideas how I can fix this, please.

I think I have tried to solve this before unsuccessfully, but this perhaps explains the problem more clearly.

not sure if this makes a difference, but I am using a dsnless connection string for the .connect property that looks like this.

dsnless = "ODBC;DRIVER={SQL Server};Trusted_Connection=Yes;Server={INSTANCE\SQLEXPRESS};DATABASE=SQLDatabase"


[edit]
this link gave me some help.

http://sqlmag.com/database-administration/15-steps-convert-access-data-sql-server

I think maybe you need two connections - one to set the identity insert, and another to clear it - but I am not sure how to arrange that.

Dave,

You haven't stated the error or what the exact role of your ADO connection is.

I would imagine that you are doing this in one VBA module:

ADO_DSNLess.Execute "SET IDENTITY_INSERT sqltable ON"
CurrentDb.Execute "insert * into sqltable select accesstable.* from accesstable"
ADO_DSNLess.Execute "SET IDENTITY_INSERT sqltable OFF"

The only thing that I can see is that since there can be only 1 table with
Identity_Insert on at a time; you might have encountered an error and missed
the off command. If that was the case, it would fail on the Identity_Insert ON
statement.

hth,
Wayne

I enjoyed reading this. My process is probably a little backwards if it was an automation process.
- use SQL Server Migration Wizard for MS Access (it will create the identity column)
- Then in SSMS, go to new table, right click and create table script
- Once I had the table definition scripted, would build a procedure to call the SQL procedure to drop the table and re-build it with the script. Then from Access connect as a linked table and populate the newly formed linked table.
Not promoting this for a solution. My ADO is weak and the T-SQL is something I want to learn more about. The solution above is something I might have to try.

Then again, since all I own is a Hammer, eveything tends to look like a Nail.

WayneRyan said:

Dave,

You haven't stated the error or what the exact role of your ADO connection is.

I would imagine that you are doing this in one VBA module:

ADO_DSNLess.Execute "SET IDENTITY_INSERT sqltable ON"
CurrentDb.Execute "insert * into sqltable select accesstable.* from accesstable"
ADO_DSNLess.Execute "SET IDENTITY_INSERT sqltable OFF"

The only thing that I can see is that since there can be only 1 table with
Identity_Insert on at a time; you might have encountered an error and missed
the off command. If that was the case, it would fail on the Identity_Insert ON
statement.

hth,
Wayne

Click to expand...

I thought thats what it should do, but the set flag OFF fails, so that the next flag ON then fails, and the records dont get added.

A link i found seems to i dicate that the ON and OFF commands need to use different connections, which may be a solution. Closing and reopening the dbs resets the flags, so that the next set ON works, so thats my work round.