?
Solved

Access 2010 frontend with linked tables to SQL backend 3622

Posted on 2014-09-26
2
Medium Priority
?
346 Views
Last Modified: 2014-10-03
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?
0
Comment
Question by:Sasha42
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40347059
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
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40347314
> 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

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question