Passing an AutoNumber field from Access to an SQL Identity field

[Guest_imported]

I am trying to pass data from an Access 97 DB through Visual Studio 6.0 to a SQL 7.0 DB. Everything works fine except that the Acess AutoNumber fields don't want to go into the SQL Identity fields (Identity switch turned on).
These fields have to be unique and have to be able to talk. I hope you can help me. Thanks

 

[rac2]

What error messge is generated?

So you have this?

SET IDENTITY_INSERT my_SQLServer_table ON

Then maybe the problem is mis-matched datatypes.

And I wonder, do you really need the same values in the Access Autonumber field that you have in the SQL IDENTITY column? I can imagine that if your application is getting data from both databases, maybe the Autonumber value is being used to retrieve data from the SQL Server database then it would be handy to use the same value. But in this case you could create a new column to store the imported Autonumber values. Can you re-design your tables so that the Autonumber and IDENTITY columns have no meaning as application data? Then you would not need to export those values and you could let the servers assign the values. Just thinking out loud here.

 

[tlbroadbent]

If the Access table is the master table you shouldn't have the SQL column defined as identity. Just make it a numeric column.

If the SQL column must be an identity and you want to insert values into it, you can set IDENTITY_INSERT ON. The syntax is as follows.

SET IDENTITY_INSERT Table_Name ON Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

I recommend that questions on Access and SQL Server be posed in the Access and SQL Server forums. This forum is for ANSI SQL. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.