Access Front end works fine for most users but one user keeps having a variety of problems

Question

I have a handful of MS Access 2010 databases that are used to keep track of various things for my group. Each Database has a dedicated back end and each user has a local copy of the front end (*.accdr) and 2010 access runtime. Only three of us have full versions of Access. Each DB has up to 6 users and some users have multiple DB's they use regularly. One user in particular has multiple problems with these DB's. One of them has a form where you put criteria in some text boxes, click a button and another form opens displaying data. Everyone else has this perform seamlessly. On hers, it throws up a dialog box asking for the criteria a second time. This is pretty universal across the DB's that she uses. On another DB, Clicking a button triggers a macro to export of a query to a MS Excel spreadsheet. This will generate an unspecified runtime error and then shutdown the entire frontend. Again, this works fine for other users.

I have systematically gone through and tried each DB from each user's computer. I have checked and rechecked the source *.accdb files I generate the front ends from. The problem seems to exist only on this user's computer. She does have a full copy of Access 2010, but she doesn't ever use it. She also has 2010 runtime. All of our machines are connected by Ethernet to the server where the Back ends are stored.

I would expect the front end to behave the same way on her machine without unexpected pop ups or runtime errors since it behaves as it should on every other user's machine. I don't know what to look for now, and I am not inclined to throw up my hands and blame a bad setup on her machine. Is there some logical steps I can take now, since IT support is one place no sane person in my office wants to do (bad for the blood pressure). Any help, advice, or even Mystical Incantations would be appreciated.

Answer 1

This is a common occurrence.

The HUGE MASSIVE tip-off is that the accDB works, but the accDE (pre-compiled) does not.

And the next HUGE MASSIVE tip-off is compiling the accDB to an accDE on that particular machine ALSO works.

The reason and problem for this is that the version of Access running on that machine does not match the save version that you are running on other machines. (Specific the machine used to compile the accDB into the accDE).

While of course you are running access 2010 on all machines, the problem is the SP update version (Service Pack(s) installed).

Keep in mind that the runtime is NOT updated by windows update.

Keep in mind that running the office SP update will NOT update the runtime (but this will only apply to runtime only machines).

So, on your dev computer? Well automatic updates can roll out a SP update to office 2010.

However, automatic windows updates NEVER update the access 2010 runtime. You must install the 2010 runtime SP updates manually. So with a mix of runtime and full editions?

Well, the machines with full edition will wind up with SP updates occurring (they over write the runtime on those machines). In fact, you can’t install both full and runtime on the same machine. The installer allows this, but it is a “fake” install, and installing the 2010 runtime on machines with full edition in fact does NOTHING!! (Well, it does create a “fake” entry in the list of programs installed – but it DOES NOT actually install the runtime, since it would overwrite the full edition that already exists on the machine).

On computers with the full edition, then installing the SP updates to office, or even allowing windows update to do this will NOW cause the 2010 version to be DIFFERENT then your developer machine.

The reason why the accDB works is because Access (even the runtime) will detect that the “sp version” is different, and re-compile the VBA on the fly. Even the stand alone runtime version is able to re-compile the source VBA code.

However, with an accDE?

The code is pre-compiled, and thus no on-the fly re-compile can occur. There is no source code. The accDE should and often MUST be run + consumed by the SAME sp update version.

To reduce, or all but eliminate this issue?

Well, on your dev machine, make sure the sp2, or sp3 update to office has been applied.

On the target computers? If they are runtime only, then you MUST install the sp2 or sp3 update to the access runtime. I cannot stress that you MUST download and install the SP2 or SP3 update for the access runtime. The office sp update WILL NOT work nor will it update the runtime version on runtime only machines.

Because of the above?

I recommend you download the 2010 runtime. Download the sp3 2010 runtime update, and “slip stream” the sp3 update INTO the 2010 runtime installer.

You can then provide the customer site (or your site) with a folder on the server with the runtime to install, and WHEN you install the runtime, then the sp3 update will be included in that install.

If you (or your IT department) does NOT know how to slipstream in the sp3 update, then simply ALWAYS have then install the 2010 runtime, and then ALWAYS install the sp3 update for the 2010 runtime.

Doing the above will eliminate the issue of the AccDE having been created and compiled with a different release version of access.

Last but not least?

No question you want to continue using a compiled accDE, since with the runtime, then any un-handled error with an accDB will not only spit out an error message, but shutdown the whole application.

So: With accDE: Errors NEVER re-set global or local variables.\ Errors will NEVER cause a shutdown of the runtime. Even un-handled errors will NOT cause a shutdown of your application. Un-handled errors will NEVER re-set local, or global variables they will ALWAYS no matter what retain their values for the duration of the appcation session.

With accDB and runtime: Any un-handled error will blow out all local and global variables. Any un-handled error will then shut down the runtime after display such errors.

Bottom line: Using an accDE is thus vastly far more reliability then an accDB when using the runtime.

Answer 2

OK, First of all, thanks for all your suggestions in the comments. We figured a method to keep my user working, so I will put it here.

We reasoned that since the executable ran fine on multiple machines, there may have been some sort of unknow quirk in my users machine that was causing the issues. I started my re-making the front end in the normal way and pushing it out to just the one user. It failed just like before.

Since she had a full copy of Access 2010, we opened the source *.accdb file directly on her machine. That time, It worked just fine.

From there I went, possibly a little overboard. But it worked out. I opened all the forms in design view. Double check for errors, then save each form in turn. After that, I did the same with the macros. Not making changes, but checking the work.

Next I ran a compact and repair, from the affected machine.

Then I used the affected machine to create a new front end executable.

Lo and Behold, it worked. The affected user now has a completely functional front end.

This is going to make updating the front end a pain in the keister moving forward, but at least now I know what will actually work.

Thank you for your help