Access 2010 frontend with linked tables to SQL backend 3622

Error 3622. I have read that you must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column

We took an Access backend and uploaded it to SQL Server.  Is there anyway it can be uploaded without an IDENTITY column?  Out of the 30 or so tables many have autonumber fields as the PK.  The Access Frontend linked to the SQL Server uses DAO. Please tell me that I won't have to go to each OpenRecordset and add dbSeeChanges.  Can something be done in SQL Server instead?
Sasha42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
Sorry, I don't think so. SQL is creating the new identity value, and Access needs to get that value, which is what dbSeeChanges is doing. Two possible hacks that might work but are probably not worth the effort:

1. Turn the SQL identity field into a basic INT field. Have Access VBA create a "fake" identity value: get the largest fakeID and add 1. Then pass the ID field along with the rest of the new record. Since it's not an Identity field in SQL, you shouldn't need dbSeeChanges. Can lead to all kinds of problems if more than one person can access the system, though.

2. Keep the Identity field in SQL and create a SQL stored procedure to add new records. Rather than tying your add_record form directly to the SQL table, the unbound form would fire the proc with the form fields as parameters. After it's fired you would need to refresh any open forms that are bound to the table.

I really don't recommend either, just giving some possibilities.
0
Gustav BrockCIOCommented:
> Please tell me that I won't have to go to each OpenRecordset and add dbSeeChanges

But that's what you have to. And it's no other problem than just doing it.

> I really don't recommend either, just giving some possibilities.

I would even say, they are far out, though creative. Only for emergency situations.
If you wish to use SQL Server as the backend - and many good reasons exist for that - you have to play by the few extra rules that introduces.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.