Handling ODBC errors in VBA

[PhilippeRG]

Hello All
I have a problem that is reallly stumping me.
I am developing an Access Runtime front end, linking to a SQL Server 2005 back end database in the cloud.
Everything was working until recently, when users started reporting seemingly random ODBC errors. These manifest by an error message, usually during an update, then the program shuts down.
I have error handling everywhere in the code, both in subs and functions, and in the on error event of all my forms. The error is handled correctly but the program still crashes on exiting the error handling. I cannot recreate the errors and there seems to be no pattern to them.
Does anyone know how I can get the program to not crash like this? Has anyone experienced disconnects like this?
Any ideas appreciated
Philippe

 

[MarkK]

Are you using DAO? ADODB? Is it the runtime version that crashes? Does it crash in the development version, the accdb file? Do you have the description of the "seemingly randon ODBC errors"

 

[PhilippeRG]

Hello Lagbolt.
I'm using DSN-less connections to the back end SQL Server to create linked tables at runtime. I've experienced one crash with the accdb but I never use it on the live database in a multi-user environment for any length of time. The users report the crashes (maybe 3 a day) and they use the accdr only. The errors are all ODBC related (3151, 2146). There's no particular form or action that seems to trigger the errors except that they are updating records. The errors never occurred until recently. I have upgraded everyone to Runtime 2013 and it seems to have improved things but still happening too often.

 

[MarkK]

OK, but once you have the tables linked, don't you then need to use DAO or ADO to run the updates? And the reason I ask is that DAO doesn't raise errors by default, you need to explicitly use the dbFailOnError option, and I'm not sure about ADO.
To me it sounds suspiciously like the error is in a routine or process where there is no error handling, or not within the scope of Form_Error(), and in an accdr that just kills the app. What I would try to do is run the accdb and see if you can get the end / debug / help dialog, and break into code, and take a look at the call stack, and try to find the origin of the problem that way.

 

[PhilippeRG]

I just let Access handle the updates, my understanding is that DAO is the default (only?) for ODBC linked tables. The thing is, the errors are being handled by my error handling code, but instead of resuming it crashes.
I have tried replicating by disconnecting the network and in this case the error handling works fine.

 

[MarkK]

How do you know so much about the errors if they happen in an accdr? You can't trace code execution. Is this scenario possible? Let's say a data operation fails and your code handles the error gracefully, resumes, and tries to open a form to the newly created record. But that's the operation that just failed, so the record doesn't exist, the form opens with no records, and code sets focus to a control. But that causes an error in a form with no records, which you might have never expected to happen, and that crashes the app.

Is that a possible scenario?

And you say you just let access handle the updates. What does that mean? Like, do you bind a form to a linked table, and let the user make updates in the form?

 

[PhilippeRG]

With regards to Access handling the updates, yes that's exactly what I mean.
For the errors, I know the error codes and description from the error handling and whether they occur on the form or sub/function.
With regards to your scenario it's possible but actually most of the situations are a lot more basic than that, for example they'll just be updating a bound form. As they leave the record they get an ODBC error message, go through the error handling then it crashes. Sometimes they will get a 'can't set the focus to...'-type message if that's what the form is trying to do, but it's rare. If I try to recreate the errors by disconnecting the network, the error handling works fine and I can get back to the program every time.
As a side issue (?), when doing that sort of test it's then impossible to get the connection back, even if I delete the table defs and recreate the connection (which it'll let me do), Access will still return an ODBC error if I try querying a table. Only if I close and reopen the runtime will I be able to query the tables again.