Problem Inserting Records to SQL table

[Eric Heinold]

I have some code that runs to copy records from one table to another.

For some reason, I get ODBC - call failed when I try to copy records for a
specfic table. The problem appears to be a field that is an Identity field
in SQL server. If I ignore the field when I'm importing, it works
correctly. The problem is that I need to specficy the value of the field,
since in the code later logic depends on the field being the same before and
after copying. I have other tables that appear to work correctly when using
the Identity column. I looked and did not see any differences between this
field and the tables that will copy correctly.

I'm fairly new with the SQL side, and any suggestions would be appreciated.

Thanks,

Eric Heinold

[Douglas J. Steele]

I don't believe you can specify values for Identity fields. Can you ignore
the field when importing, and then check its value after the import?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Eric Heinold" <er...@REMOVEadaptasoft.THIScom> wrote in message
news:O39Vx2rG...@TK2MSFTNGP04.phx.gbl...

[Banana]

You could try SET IDENTITY_INSERT <table_name> OFF....

However, I should caution that without knowing what exactly 'copy
records from one table to another' entails, it's possible that this is
not the best way of doing things and can lead to trouble.

So, caveat emptor.

[Eric Heinold]

The SET IDENTITY INSERT statement is the problem. With some other help, I
have determined that when using ADO and setting the Identity field, the
IDENTITY_INSERT statement is set to off for the first table automatically,
but after that table is closed, no SET IDENTITY_INSERT <tablname> OFF is
issued, so when the procedure tries to set the next table to ON, it cannot,
so the insert ends up failing. Now I just have to figure out how to get ADO
to trun it off when I'm done with the table.

"Banana" <Banana@Republic> wrote in message
news:4A820597.9030708@Republic...

[Banana]

I've never done a Identity insert via ADO before, but my hunch is that
it may be preferable to wrap the whole thing in a single stored
procedure that also handles the identity insert so the only thing to be
done in ADO is to call the stored procedure & passing any parameters if
needed.

Also, for multiple statements, you can only have one table set at a
time, so between update statements, you have to turn insert for the
previous table off before you can turn on the insert for next table.

HTH.

[Sylvain Lafontaine]

Are you sure that you need an Identity field for that table?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

"Eric Heinold" <er...@REMOVEadaptasoft.THIScom> wrote in message
news:O39Vx2rG...@TK2MSFTNGP04.phx.gbl...

[Jeff Boyce]

Eric

Others responding in this thread have asked, but I haven't seen your answer
yet.

Why? What business need are you attempting to satisfy by "copying records
from one table to another"?

This approach is common ... when using spreadsheets.

This approach is fraught with issues ... when using Access.

More specific description could lead to more specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Eric Heinold" <er...@REMOVEadaptasoft.THIScom> wrote in message
news:O39Vx2rG...@TK2MSFTNGP04.phx.gbl...

[Eric Heinold]

Jeff,

The reason is that we have an Access Database that is used as a "carrier" to
copy records into a SQL database. Bascially, we use a table in SQL to store
a "master list" of reports that are available to our clients. However, they
need to be able to modify the records and not have the changes get
overwritten, so we cannot store them in our Access database. The users can
also add their own records to the table, so we reserve a section of the
IDENTITY records for our own use (numbers less thatn 10,000), and anything
over 10000 are records they have added. Because of this, when I copy
records from the temporary Access database, I have to make sure that the
IDENTITY is below 10,000.

Sorry that I have not followed up on this thread. I found that I can use
"SET IDENTITY_INSERT <tablename> ON", then insert my values, and turn that
back off to do what I wanted.

Thanks,

Eric

"Jeff Boyce" <nons...@nonsense.com> wrote in message
news:eYV2Jp2J...@TK2MSFTNGP02.phx.gbl...

[Jeff Boyce]

Eric

Thanks for the clarification.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Eric Heinold" <er...@REMOVEadaptasoft.THIScom> wrote in message

news:e$YS2ZZLK...@TK2MSFTNGP02.phx.gbl...