What is the best approach for fixing intermittent bugs in Access?

[haggis999]

Ten years ago, I designed a large and complex Access database application for an annual international photographic competition that accepts both prints and digital images. This app is only used seriously for two or three months every year, in conjunction with a website for online entry. The website uses an SQL Server database and the online entry information is downloaded to the Access app.

The heaviest usage of the Access app occurs on three days in late January and early February, when a team of people get together to process all the incoming packages of photographic prints associated with some of the online entries. We also process a small number of entries that use a postal entry form rather than our website. A temporary wired Ethernet network is used to enable data input from several laptops running the front end of my app, while the back end database resides on another network-connected laptop.

During those three unpacking days, my Access front end app has always suffered from an intermittent bug that usually takes about 30 minutes to appear. The bug almost always affects only one laptop at a time. The nature of the problem is that when the primary data input form is cleared to accept the information for a new entrant, one or two of the textboxes still show the information for the previous entrant and unhelpful error messages then soon start to appear. Closing and re-opening the form is the only solution. It may then take another 30 minutes or so before the problem reappears.

I cannot find any flaw in my VBA coding and this bug has NEVER appeared when running this app on my own PC at home (which almost always has both front and back end databases on the same machine, unlike during our unpacking days). I have only just upgraded from Access 2007 to Access 2016 and don't yet know if this will make any difference. I would very grateful for any general guidance on the best approach for tracking down the cause of intermittent problems such as this.

[John_G]

We can't tell you much based on this. What are the error messages? Are they produced by error handlers in the form code or are they something else? If you can isolate a line of code that os producting the errors, that would be a start. My guess is that somehow the problem is data related, so you should look at exactly what is being entered at the time to see if there is something in common among data records that trigger the errors.

Questions:
- Is the data entry form bound to a table, or is data saved with VBA?
- Are the fields that are not cleared bound to table fields?
- How is the data entry form being cleared?

There are any number of things that might be causing this. If you are not using error handlers, then trying to use a form after a runtime error has occurred is asking for trouble, because you don't know what has been left in what state, so closing and reopening is about all you can do.

Please give us a more detailed explanation of what is happening from the users' viewpoint.

[haggis999]

One of the major frustrations here is that the problem only ever occurs when the primary concern is to get the app working again, otherwise the work being done by a group of 20 people would rapidly grind to a halt. This severely limits my ability to investigate the error messages.

To answer your specific questions,

• The form is far too complex to be bound to a single table. Many tables are involved and data is saved via VBA coding.
• From memory, I think my code clears all textboxes by setting the value to ''.
• Virtually all my VBA subroutines have error handlers, but it has never been obvious what has triggered the errors that occur.
• So far, no pattern has been spotted in the type of data being entered just prior to the errors.

It would be good to find a way to save the app in its failing mode for future investigation, but I can't think of a method for doing this.


EDIT: I can't remember the exact error messages that occurred a year ago. I might have made some notes at the time, but I'm struggling to locate this information. However, such notes are unlikely to help in identifying any patterns in data input prior to the error. The primary purpose of this thread is to seek guidance prior to our next unpacking day on 27 January 2018.

[John_G]

OK, so you are using VBA to clear the textboxes by setting them to "". The fact that some controls are not being cleared suggests that a runtime error is occuring (and being trapped) before that process has finished. However, because the error is not consistent, I doubt if it is that. The fact that the errors are (or appear to be) intermittant suggests that it is not a coding error.

Virtually all my VBA subroutines have error handlers, but it has never been obvious what has triggered the errors that occur.

A very common problem when writing error handlers - they can't tell where the error occured, only that there was one.

One thing that might be causing a problem is the temporary network, and I assume you are using linked tables.

How does the data entry form work? 1) does it use SQL INSERT to add records; 2) does it define a recordset when the form is opened and keep it defined until the form is closed, using the .Addnew and .update methods to add records; or 3) does it define a recordset, add the new record, then close the recordset each time a record is added?

I'm am not a technical guru here, but you never know what Access is doing behind the scenes. If the network connection is dropped, even very briefly, and Access checks that it is still valid (because a recordset is open) when the connection is dropped, it (Access) will give you an error.

If it is practical, one thing you can do is disable all the error handlers, so that a runtime error will let you see the code line causing the problem. You don't have to change any code to do this - with any code module open:

- select Tools - Options from the menu bar.
- In the dialog that is shown, click the "General" tab
- In the Error Trapping section on the right, select the "Break on All Errors" option

Close the dialog and the code module. Access will now ignore all the Error Handlers, and and display the generic runtime error dialog, allowing you to use Debug to see exactly where the error occured.

Another thing you could do is examine all your code, looking for cases where it might fail if a variable or form field is still "", meaning required data was not entered.

Another thing you might try is setting the form fields to Null instead of "" when clearing the data.

I know that's a lot to think about, but when you don't really know what's wrong it can take some time to find out.

[SodaPop]

I agree with prior comments especially more info needed. However since you seem to know about where it happens in the process you can insert an error stepper by external sub. Dim the code lines as text called errname and continue to update errname after each new code line or as often as you see fit. When an error occurs combine errname and the err.description and the date. Make errname equal that string. Pass to errorhandle sub which put it in a local table. This is how I build all my db's. Generally only on the module level. But when something fails I quickly know when, what module, and the general failure.

Also when it errors you can you not hit debug? That should give you exactly where the error occurs. I use the above because automation.

If you need help with the trap holler but it seems you know what u r doing.

Sent from my SM-G950U using Tapatalk

[haggis999]

OK, so you are using VBA to clear the textboxes ....................

I know that's a lot to think about, but when you don't really know what's wrong it can take some time to find out.

Many thanks for all that detailed advice. I will be away from home for most of today, but will answer your questions as soon as I can.

I agree with prior comments especially more info needed. However since you seem to know about where it happens in the process you can insert an error stepper by external sub. Dim the code lines as text called errname and continue to update errname after each new code line or as often as you see fit. When an error occurs combine errname and the err.description and the date. Make errname equal that string. Pass to errorhandle sub which put it in a local table. This is how I build all my db's. Generally only on the module level. But when something fails I quickly know when, what module, and the general failure.

Also when it errors you can you not hit debug? That should give you exactly where the error occurs. I use the above because automation.

If you need help with the trap holler but it seems you know what u r doing.

Sent from my SM-G950U using Tapatalk

Thanks for that. I follow the general idea, but I'm struggling to understand the details of your description. One sentence was also incomplete (i.e. "I use the above because automation"). Can you please make another attempt to explain your suggestion, perhaps with a code example. However, please note that there is a lot of code behind this form (and its subforms) and I don't yet know which sub triggers the error.

[SodaPop]

Many thanks for all that detailed advice. I will be away from home for most of today, but will answer your questions as soon as I can.


Thanks for that. I follow the general idea, but I'm struggling to understand the details of your description. One sentence was also incomplete (i.e. "I use the above because automation"). Can you please make another attempt to explain your suggestion, perhaps with a code example. However, please note that there is a lot of code behind this form (and its subforms) and I don't yet know which sub triggers the error.

Sub yoursub()

Dim errname as string

On error goto errhandlr

Errname = "yoursub"

'Your code

Errhandlr:

Errname = errname & " " & err.description & Date()

Call errrecord(errname)
Resume next

End sub

Sub errrecord(errname as string)
Dim strsql as string

Strsql = 'some insert sql to a table of your naming inserting the variable passed "'" & errname & "'"

Docmd.runsql

End sub

Effectively you manually name each module or line of code with "your sub". When an error occurs it grab that name and add the error and timing. This is passed to a routine that appends it to a table. My Db's run when I am not around and designed to never lock up. Some portions may or may not run so I use to see if I had any errors. Hence because automation.

So add the sub routine errrecord to the db you are having issues with. Then creat the sql to append and finally add the error handler to the routines you want to monitor. Sounds like you may want to add to all. With this the erroring mod will be identified in the table when an error occurs. If the code is lengthy you may want to redefine errname in a couple section, maybe by action or just in fourths. This will let you know the error happened at or after that line of code. Just keep moving the errname definition and you will find it as once you go too far the errorname will revert back.

If needed I can put a working example in a db with some error examples in a bit.
This is air code on my phone, just let me know.


You're comfortable debugging through code with f8?

Sent from my SM-G950U using Tapatalk

[haggis999]

OK, so you are using VBA to clear the textboxes by setting them to "". The fact that some controls are not being cleared suggests that a runtime error is occuring (and being trapped) before that process has finished. However, because the error is not consistent, I doubt if it is that. The fact that the errors are (or appear to be) intermittant suggests that it is not a coding error.

Apologies for the delay in responding. I have been very busy over the past two days. The intermittent nature of my problem also makes me suspect that the problem may not lie in my coding.

A very common problem when writing error handlers - they can't tell where the error occured, only that there was one.

One thing that might be causing a problem is the temporary network, and I assume you are using linked tables.

Yes, all the key tables are accessed via links to the back end mdb file. My temporary network has always been a source of suspicion, but I lack any evidence that it is causing my problem. I may raise some questions in a networking forum to help improve my network analysis skills, which are rather limited at present.

How does the data entry form work? 1) does it use SQL INSERT to add records; 2) does it define a recordset when the form is opened and keep it defined until the form is closed, using the .Addnew and .update methods to add records; or 3) does it define a recordset, add the new record, then close the recordset each time a record is added?

It's a Jet DAO database. I can't remember if I use INSERT, but I certainly define recordsets when the form is opened and then make frequent use of .AddNew and .Update methods. The recordsets are normally closed at the same time as the form.

I'm am not a technical guru here, but you never know what Access is doing behind the scenes. If the network connection is dropped, even very briefly, and Access checks that it is still valid (because a recordset is open) when the connection is dropped, it (Access) will give you an error.

If it is practical, one thing you can do is disable all the error handlers, so that a runtime error will let you see the code line causing the problem. You don't have to change any code to do this - with any code module open:

- select Tools - Options from the menu bar.
- In the dialog that is shown, click the "General" tab
- In the Error Trapping section on the right, select the "Break on All Errors" option

Close the dialog and the code module. Access will now ignore all the Error Handlers, and and display the generic runtime error dialog, allowing you to use Debug to see exactly where the error occured.

I might give that a try on one of the laptops during our next print unpacking day on 27 January, as long as it doesn't interfere with normal operation. Thanks for the suggestion.

Another thing you could do is examine all your code, looking for cases where it might fail if a variable or form field is still "", meaning required data was not entered.

Another thing you might try is setting the form fields to Null instead of "" when clearing the data.

In any situation where my code is expecting a real value in a textbox, would Null not simply create the same issue as ""?

[haggis999]

... Effectively you manually name each module or line of code with "your sub". When an error occurs it grab that name and add the error and timing. This is passed to a routine that appends it to a table. My Db's run when I am not around and designed to never lock up. Some portions may or may not run so I use to see if I had any errors. Hence because automation.

So add the sub routine errrecord to the db you are having issues with. Then creat the sql to append and finally add the error handler to the routines you want to monitor. Sounds like you may want to add to all. With this the erroring mod will be identified in the table when an error occurs. If the code is lengthy you may want to redefine errname in a couple section, maybe by action or just in fourths. This will let you know the error happened at or after that line of code. Just keep moving the errname definition and you will find it as once you go too far the errorname will revert back.

If needed I can put a working example in a db with some error examples in a bit.
This is air code on my phone, just let me know.


You're comfortable debugging through code with f8?

Sent from my SM-G950U using Tapatalk

I've finally found time to look at your coding suggestion and I appear to be already capturing similar information in a slightly different way. Unlike your unattended database app, mine only ever runs when I am around. Almost all my subs are designed to generate a MsgBox that lists the name of the sub and the error description. However, I have not yet tried to identify which section of a long sub triggered a failure, so that could be worth investigating.

I'm familiar with the normal Access debugging procedure, but this is unlikely to be of much use for a very intermittent error.

[SodaPop]

I've finally found time to look at your coding suggestion and I appear to be already capturing similar information in a slightly different way. Unlike your unattended database app, mine only ever runs when I am around. Almost all my subs are designed to generate a MsgBox that lists the name of the sub and the error description. However, I have not yet tried to identify which section of a long sub triggered a failure, so that could be worth investigating.

I'm familiar with the normal Access debugging procedure, but this is unlikely to be of much use for a very intermittent error.

Ok so if it is intermittent and buried in code then I'd use a version of my method or the gentlemen reccomending turning off error trapping. Your problem isn't specific enough to solve so all we can do is help you find it.



Sent from my SM-G950U using Tapatalk

[Minty]

I'd second that - turn off all the error trapping and see where it actually bombs put.

[haggis999]

It is definitely my plan to follow John_G's advice to turn off all error trapping on at least one laptop during our next print unpacking session on 27 January. However, if this problem is caused by a networking issue then I'm not sure whether knowing the exact line of code that fails will shed much light on the problem. Time will tell.

[Minty]

Just on a complete tangent - As they are laptops I would actually stab a guess that these are connected wirelessly.
That will probably be the actual cause of the issue - Get a cheap network switch and hard wire a couple of them - see if you only get the issue on the ones still connected via Wi-Fi.

[John_G]

In any situation where my code is expecting a real value in a textbox, would Null not simply create the same issue as ""?

Not always. Variables declared as anything other than Variant will usually generate an error if you try to assign a Null to them, e.g.

Dim strX as String
strX = Null

will produce an error.

Both Nulls and Zero-length-strings can produce errors when adding records to your tables. Look at the "Required" and "Allow Zero Length" properties of your table fields. If "Allow Zero Length" is set to "No", then trying to save "" (Zero-length) to that field will produce an error. If Required is set to "Yes", then trying to save Null to that field will produce an error.

I don't offhand know what happens if you try to save a "" to a numeric field (numeric fields do not have an allow zero length property), but it might produce a type conversion error.

You actually can use those properties to trap data entry errors where a user does not enter a required value.

[haggis999]

Just on a complete tangent - As they are laptops I would actually stab a guess that these are connected wirelessly.
That will probably be the actual cause of the issue - Get a cheap network switch and hard wire a couple of them - see if you only get the issue on the ones still connected via Wi-Fi.

You make a good point, but if you scroll way back to my OP, you will see that we use a temporary wired Ethernet network. I would never have trusted wifi for connections to a back end database.

However, given my strange intermittent problem, I'm not sure if I trust wired networks either! It would probably be safer to run all queries on the back end using SQL Server, but that would require a huge amount of code rewriting.