Solved

Access 2010 frontend with linked tables to SQL backend 3622

Posted on 2014-09-26
2
327 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
Comment Utility
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
Comment Utility
> 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 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

6 Experts available now in Live!

Get 1:1 Help Now