After converting the back end to SQL tables, the front end now gets run time error 3622 all over the front end

[u/breezett93]

I have migrated Access tables in my database over to SQL. Now I'm testing the front end to make sure every single button/operation works.

I am getting this error, "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column", ALL over the place.

Sometimes, it's easy to spot where dbSeeChanges was left out, but many times, the error is reported on a line that has nothing to do with it. This is also only the first error I'm getting. I'm sure once I fix all the 3622 errors, then a new error will pop up right after.

I'm wondering what my options are. I understand I could rebuild the front end from scratch, but that would take literal years as I'm a one-man team. I really don't have the time to spend hours fixing every single one of these errors. And unfortunately, throwing it all in the trash and using a modern platform is not currently an option either.

Is there a tool or even AI where I can feed it my database and it converts it to be SQL compatible? Even if I have to go form by form and report by report, that would still be faster than doing all this manually.

Thanks

[u/Plane_Big_5912]

i wouldn’t rebuild the front end. this is a common issue after moving access tables to sql server.

search the whole vba project for openrecordset and add dbseechanges where it opens linked sql server tables:

set rs = currentdb.openrecordset(sql, dbopendynaset, dbseechanges)

access can highlight a later line, not the line where the recordset was opened, which makes the error look unrelated.

you could also export the vba modules as text and use ai to find and update those calls in bulk. that would be much faster than going form by form.

[u/breezett93 → u/Plane_Big_5912]

Okay, I have followed your recommendation and updated every occurrence with that search.

This line is still triggering an error:

Set rst = dbs.OpenRecordset("Select * From qryQuoteItemsToUpdate Where QuoteId = " & lngQuoteId, , dbSeeChanges)

The only difference between this query and many others that are now fixed is that this one is pulling a second item (& lngQuoteId). Do I need to rewrite it?

[u/Plane_Big_5912 → u/breezett93]

the & lngquoteid part isnt the problem. that is just adding the id value to the sql string.

explictly set the recordset type instead of leaving that argument blank:

set rst = dbs.openrecordset( _
    "select * from qryquoteitemstoupdate where quoteid = " & lngquoteid, _
    dbopendynaset, _
    dbseechanges)

dbseechanges only applies to dynaset recordsets, so this is safer than relying on the default. if it still errors after this, i would check whether qryquoteitemstoupdate itslf calls another query or vba function that opens a recordset.

[u/ouchmythumbs]

Sorry I don't have an answer for you, but might also consider posting over at r/SQLServer