Solved 3622 Error after moving tables to SQL (1 Viewer)

[AmishElectrician]

The company I just started working for has an Access database they have been maintaining for years. They have tasked me with converting the BE to SQL. My knowledge is very limited but I do understand basic concepts of VB and database management.

After having converted the tables to SQL I am, of course, having trouble with the VBA code. On a few of my forms I am getting the following error when opening them in Form View:

"Unexpected error: Error number 3622 - You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."

Obviously the error is telling me how to fix it, so after seeing a few threads here I added dbSeeChanges to my OpenRecordset and Execute commands. The form still opens with the 3622 message, however.

Maybe I am missing something somewhere? Is there something in the interface I need to check in addition to the VBA? The form's Property sheet has a query defined for Record Source if that needs to be looked at. My knowledge of Access is very limited so I might be missing a simple thing.

I have attached the code for the entire form.

 

[CJ_London]

An awful lot of code and no indication where you are having a problem. But in the instances where you have used openrecordset, I don't see anything that looks like a PK (Identity column).

Indenting is patchy so makes it very difficult to see where loops begin and end

and why do you create a db object for the open recordset then use currentdb for the insert? why not just combine and run the one (passthrough) insert query

Also field names like 'description' are reserved words and should not be used for field names Using them can generate strange and misleading errors

Suggest you step through the code and highlight where you have an issue

 

[pbaldy]

Temporarily comment out the "On Error GoTo..." line(s) and run the code. You should be able to hit Debug and see the offending line.

 

[AmishElectrician]

Temporarily comment out the "On Error GoTo..." line(s) and run the code. You should be able to hit Debug and see the offending line.

Perfect answer. It would appear that I did not know how to use the debug tool

Using debug and stepping into the Open event I was able to find OpenRecordset commands that did not include dbSeeChanges. Thank you so much!

 

[pbaldy]

Happy to help!

 

[CJ_London]

Using debug and stepping into the Open event I was able to find OpenRecordset commands that did not include dbSeeChanges

funny, all the openrecordsets in the code you supplied included dbseechanges and the form open event you provided does not have an openrecordset line

 

[AmishElectrician]

funny, all the openrecordsets in the code you supplied included dbseechanges and the form open event you provided does not have an openrecordset line

Yup! That's because I couldn't figure out where the heck the problem was and was just dumping code on ya. My issue was the missed OpenRecordset commands other VBA modules the Open event called. The debug tool stepped me through these calls to see where I missed the commands. The line of code I actually fixed was missing dbOpenDynaset and the dbSeeChanges command.

Keep in mind I am very new to this and did not realize the calls were opening other modules.

Basically what this boils down to is I did not know how to use the tools at my disposal to figure it out.