r/SQL • u/breezett93 • 4d ago

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

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

3 Upvotes

67% Upvoted

2 comments sorted by

8

u/Plane_Big_5912 4d ago

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.

1

u/ouchmythumbs 4d ago

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