asked on

Primary Key Problem

I am using Access97 Front end on SQL 6.5 backend. I have 3 primary keys set on a table, one is an identity column. I am opening up the linked table in access and trying to copy and paste multiple records by using the record selectors. After Updating I get a #DELETED in every field. But after I reopen the table the records that I have just pasted reappear. Shouldn't the identity column update as the records are pasted? If I do the same thing with an access table there is absolutely no problem, everything updates automatically. Help!!

DennisH

This is kind of a sticky problem with Identity fields in SQL Server. You are not allowed to set the value of the identity field. Your best bet would be to open 2 recordsets, one on your Access table and one on your SQL Server table. Iterate through the Access recordset and copy the fields (except the identity field) to the SQL Server recordset.

Another approach would be to create an Access query of your Access table that does NOT include the field that is the identity field in SQL Server. Then create a query or view for the SQL Server table that does not include the identity field. Then try copying and pasting records between the datasheet views of both queries.

manish77

Are you using bound control or are u using your own recordsets through code??

rvindust

ASKER

I am using bound controls to the form recordsource. I don't quite understand your question. The form recordsource in some cases is an access query and other case I set it through using VB open recordset.

ASKER CERTIFIED SOLUTION

manish77

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial