Solved

Access 2010 frontend with linked tables to SQL backend 3622

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 33
SQL - Copy data from one database to another 6 19
Unable to Uninstall Visual Studio 2015 7 25
Opening forms with an input box 5 17
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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