Solved

Access 2010 frontend with linked tables to SQL backend 3622

Posted on 2014-09-26
2
337 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
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 49

Accepted Solution

by:
Gustav Brock earned 500 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 32
Access 2010 Query Syntax 5 23
SSRS troubles 4 20
C# guarantee sql connection close 6 27
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now