Identity_Insert won't turn off

[snooka9]

I'm attempting to append data from an Access table to an SQL 2005 linked
table in Access (ODBC) that has an identity field. So I found that you can
run the following command in SQL Server Management Studio Express (SMSE) to
allow inserts to tables with an identity column:

SET IDENTITY_INSERT [dbo].[TableName] ON

When I run this query in SQL SMSE it works fine, then I run my append query
in Access and it works fine and then I run the following in SQL SMSE:

SET IDENTITY_INSERT [dbo].[TableName] OFF

and it works fine....or so it seems (no error). BUT when I try to run the
ON command again for another table it says its still on for the previous
table. Any ideas why it isn't actually turning it off, even thought the
command completes successfully?

Thanks,
..

 

[Guest]

What version of Access are you using? What are you trying to do?

I think you probably have some kind of connection caching
problem, and you probably have to think about a different
way of doing whatever you are trying to do..


(david)

 

[snooka9]

What version of Access are you using? What are you trying to do?

~ Access XP and SQL 2005 ~

I'm in the process of converting my Access FE/BE into an Access FE / SQL BE.

I've created the tables in SQL to mirror the Access tables. I created a
blank Access DB and added linked tables to my Access BE and SQL BE (ODBC).
From there I created append queries to append the data from the Access table
the the SQL table. So far, so good....

My append queries work fine for all of the tables that do not have an
autonumber (identity) field. *** THE PROBLEM *** I need to maintain the
numbers generated from the autonumber field so I asked a few weeks ago if
there was a way to do this. A kind person told me about the command:
SET IDENTITY_INSERT TableName ON/OFF

So I run it from the SQL Server Management Studio Express (SMSE) on my first
table and it works fine...turns it ON, append data works, turn it OFF.

BUT, when I try to turn it back ON from the SQL SMSE it gives me the error
that its already on for the first table I tried. I realize it can only be
used on one table at a time, but the OFF command says executes sucessfully
but doesn't seem to be turning it off. If I wait for a pretty good chunk of
time and run the ON command again then it works fine.

What can I do about it? Compact/Shrink the databases after each append?
Close and re-open the DBs each time? Set my computer on fire?

I'm lost and starting to dislike SQL SMSE. Any help would be greatly
appreciated.

Thanks in advance.
..

 

[Guest]

Yes, I think that the cached connection is the problem.

I don't know anyone who upgrades using that path :~)

We did our upgrades using DTS or the Access upgrade
wizard. I think the upgrade wizard applies the constraints
after importing all the records - I think you can do that in
SQL Server (contrasting to Access, where you can't make
a column auto-number after you have added records).

If you don't want to script the construction of your tables,
I think that you could create your append queries in SQL
Server, or you could close Access each time, so that you
aren't using a cached connection for the append.

If I was going to do that, I would try using IsolateODBCTrans
and a new workspace clone for each append, rather than
a new copy of Access. If that didn't work, I would go on
to creating a new dbEngine for each append procedure/query

(david)