HELP! Access 2007/2010/Window 7 multi-user app with random errors

[BoulderRidge]

I’ve built successful Access apps for years, but this one is throwing more errors than all my previous projects combined and it’s still in User Acceptance Testing. I’m looking for any and all angles to explore with this.

App is built and maintained in Access 2007 on Windows Vista home premium but users are running on Access 2010 and Windows 7 (they upgraded mid-development). Typical front end app linked to back end tables with a couple local tables in front end that don’t update or need sharing. Front end has forms, subforms, queries and modules. Multiple users each run their local front end on their laptop or tablet while connected wirelessly to the back end living on a network share.

Problem: Front end users “randomly” get errors (which I trap) and lose functionality and report entering one value but later seeing a different value in its place. None of this can be replicated on demand and none of the functionality consistently throws these errors. It all works 99% of the time and so far we can’t identify a pattern with the other 1%.

I’ve been thru the code a dozen times, and of course can’t rule that out but the inconsistency points to environment rather than coding issues. What type of environment questions should I be asking the IT staff to see if we can rule that in or out? Users are required to lock their machines when they step away; could this induce a state that could cause corruption or connectivity issues? Is there any type of network activity/maintenance that could be impacting the backend database? One user has been running the app in ‘Read-Only’ mode (I just found this out) which I fear may impact her results, but could this be affecting the backend as well in some way? Other users running normally are also having issues.

I know this is vague, but going into all the details will take pages so if you have specific questions ask away and we can start there. Many thanks,

BoulderRidge

 

[kjv1611]

Yeah, you're going to have to get into specifics, and let us see if this is truly random errors, or just several different errors with specific reasons.

If it really is random errors, then my first guesses are:
1. Database file corruption - here's a good resource on that:

http://allenbrowne.com/ser-25.html

2. Different versions of Access using the same database - particularly if one is running a compact/repair on it... I've had issues running compact/repair on a database saved for 2007, when running the compact repair from a 2010 install, then others with Access 2007 not being able to access the database any longer - didn't have same effect when converted to old mdb format.

Otherwise, we need to diagnose why you're getting the errors, so you can properly handle them.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[BoulderRidge]

Hi kjv1611,

Thanks for your post, sorry I didn't see it sooner, I didn't realize my notifications were turned off

The update is that I decided to completely eliminate the few global variables I was using to share data between forms. I've created a local UserData table within the front end app that I temporarily write to and read from when i need these values. This change (along with newly requested functionality) should go to users tomorrow (July 9) so we'll see what happens.

Question: I still have global constants that I define in a single module and reference throughout the app. Do these introduce any risk when the user temporarily loses their network connection or hits an untrapped error? (not many unhandled procs left but still cleaning up the short scraps of unhandled code).

The primary outstanding issue is not error messages (these I can get my hands around) but the undocumentable reports of "I entered value X in my results popup and now the main screen 'changed' my value to Y!" Since they can't anticipate this happening, they can't provide a screen shot showing 'before' and 'after' values but this has been reported at least twice so I have to consider the possibility they saw what they saw. This is what prompted me to eliminate the global variables as that seemed like the only place where a user-entered value could be changed between entry on the popup and saving on the main form. I'm now saving the values while still in the popup instead. Does this sound like even a possible cause/effect/fix?

Appreciate any thoughts and will respond faster next time.

-- BoulderRidge B-)