'Old' database, now in Access 2021 : Working well on my PC; but NOT working well on someone else's

[alan2013]

Hi. I hope someone can provide some helpful hints or tips as to what might be happening :

A business acquaintance recently asked me to take an 'old' Access database (at least 5 or 6 years old) and make some changes to it, updating it to Access 2021. And that I did. Elements of it weren't easy (not least among them : contending with switchboards, which I'd normally avoid), but I have a copy of the database on my PC, and it works well.

However, the person who asked me to do the work has told me that he is having all sorts of problems with the database now. 'Date picker' not working ("the calendar doesn't open"); supposedly bugs in the VBA, as the debug dialogue pops up here and there.

Can someone give me some guidance as to why this might be happening, please ? A database that works well on my PC, but not at all well on the other person's PC. Both in Access 2021. Might there be something about my acquaintance's PC set-up that is preventing the Access from behaving as expected ?
Thanks in advance

 

[The_Doc_Man]

To analyze this, you need to look for differences in the installations. The presence of bugs in the VBA section gives me a hint. There could be other causes, but ... It might be productive to get into Access on both computers and open up the VBA code screens. From there, using the menu bar, open up Tools >> References on your system (which works) and take notes (seriously: write down what you see) about which libraries are checked, including file name and location. Then open the non-working system and compare what is checked to your list. Also, the ORDER of what is checked CAN be relevant, though unlikely unless you are mixing things like ADO and DAO recordsets. Also look for the annotation on the References dialog box where one or more of the checked libraries has the word MISSING or some other note. It won't matter if there are extra checks in the non-working computer's References list, but any non-checked or missing references will make a huge difference.

THIS IS A GUESS. But it is an educated guess.

 

[alan2013]

To analyze this, you need to look for differences in the installations. The presence of bugs in the VBA section gives me a hint. There could be other causes, but ... It might be productive to get into Access on both computers and open up the VBA code screens. From there, using the menu bar, open up Tools >> References on your system (which works) and take notes (seriously: write down what you see) about which libraries are checked, including file name and location. Then open the non-working system and compare what is checked to your list. Also, the ORDER of what is checked CAN be relevant, though unlikely unless you are mixing things like ADO and DAO recordsets. Also look for the annotation on the References dialog box where one or more of the checked libraries has the word MISSING or some other note. It won't matter if there are extra checks in the non-working computer's References list, but any non-checked or missing references will make a huge difference.

THIS IS A GUESS. But it is an educated guess.

Thank you for your response, The_Doc_Man. Much appreciated....and definitely worth looking into. I've taken a look (and taken a screenshot and handwritten notes) of what References I have activated, and will ask the other chap to compare the Referneces on his side.

 

[CJ_London]

Both in Access 2021

In addition to Doc's comments I would also confirm the versions and bitness you are both actually using.

Assuming you are talking about a .accdb then I would be very surprised that an app developed 5 or 6 years ago (i.e. perhaps using Access 2013 or 2016) wouldn't work in 2021 and therefore required updating. But an app developed in 2021 won't necessarily work in an earlier version.

With regards bitness - a .accdb app developed in 64bit Access should work in 32bit Access (as a .accdb) providing API declarations are done correctly (and visa versa). However I'm not sure (so guessing) if providing a precompiled 64 bit .accdb will work with 32bit Access without first recompiling in 32bit. The default is to compile code as it runs, so it may be that some 32bit compiled code is trying to reference some code still compiled as 64bit (or visa versa).

 

[The_Doc_Man]

CJ's comment is spot on, and at the same time related to my comments. It is because MSFT in their infinite wisdom didn't convert all 32-bit reference libraries to 64-bit formats. Which means that a given library might be checked but it is of the wrong bitness.

 

[alan2013]

CJ's comment is spot on, and at the same time related to my comments. It is because MSFT in their infinite wisdom didn't convert all 32-bit reference libraries to 64-bit formats. Which means that a given library might be checked but it is of the wrong bitness.

Thanks CJ_London and The_Doc_Man

It seems that the same References are activated. (See the attached screenshots) . I didn't receive Location info from him. Is that (ie. Location info) worth pursuing, in itself ?

To confirm : the file is a .accdb

I'm really not sure I understand your guidance regarding bitness. My PC is 64-bit, and I'm pretty sure the Access 2021 I have is 64-bit. I actually don't yet know re his PC. (I'll ask). Is that what you mean here ? Excuse my ignorance, but if he's on a 32-bit PC, exactly how can I resolve this ?
EDIT : Is it best to ask him to go into the VBA code screens and run 'Compile' ?

 

[The_Doc_Man]

OK, same references. It still could be bitness, but I might expect some different libraries if that were the case. The next place to look is whether there is a security issue. Is this a shared back-end case? If so, you can get into the folder, right-click on the back-end file >> Properties >> Security >> Advanced. What you want to check is that you and the other person have the same file permissions. Playing around with the advanced stuff in that security section, there is a way to select a given user to evaluate their effective permissions. You should compare those settings with each other.

In order to properly use an Access back-end, you need MODIFY permissions (broad-brush) for each user on the back-end file AND the folder it is in. It is possible for a difference in such settings to cause things to fail, though now it gets trickier. It could ALSO be trust-center settings. Both of you have to trust the back end. Even though for Access, a proper back-end has no code, Windows doesn't know that when evaluating file security.

 

[alan2013]

OK, same references. It still could be bitness, but I might expect some different libraries if that were the case. The next place to look is whether there is a security issue. Is this a shared back-end case? If so, you can get into the folder, right-click on the back-end file >> Properties >> Security >> Advanced. What you want to check is that you and the other person have the same file permissions. Playing around with the advanced stuff in that security section, there is a way to select a given user to evaluate their effective permissions. You should compare those settings with each other.

In order to properly use an Access back-end, you need MODIFY permissions (broad-brush) for each user on the back-end file AND the folder it is in. It is possible for a difference in such settings to cause things to fail, though now it gets trickier. It could ALSO be trust-center settings. Both of you have to trust the back end. Even though for Access, a proper back-end has no code, Windows doesn't know that when evaluating file security.

Thanks. The database is just being used by one person. No back-end and front-end split.
Not sure what to try next..

 

[The_Doc_Man]

Oh ... is this a case where you are the developer and the other person uses a stand-alone, self-contained database in a single file?

If so, then when the file is copied to that user's PC there can be no permission issues. However, Trust Center issues could occur. Are any of the messages that you see related to the word 'permission' or 'trust'? Is there a way to capture the exact messages you get when the VBA whacks out on you? And can you tell us which line of code is indicated? Those will be invaluable clues.

 

[alan2013]

Oh ... is this a case where you are the developer and the other person uses a stand-alone, self-contained database in a single file?

If so, then when the file is copied to that user's PC there can be no permission issues. However, Trust Center issues could occur. Are any of the messages that you see related to the word 'permission' or 'trust'? Is there a way to capture the exact messages you get when the VBA whacks out on you? And can you tell us which line of code is indicated? Those will be invaluable clues.

Yes, I have the role of 'developer' in this case, and the other person is using a standalone, self-contained database in a single file.
I've requested clips / screenshots of the instances where bugs are encountered.

 

[CJ_London]

I'm really not sure I understand your guidance regarding bitness. My PC is 64-bit, and I'm pretty sure the Access 2021 I have is 64-bit.

It is not about PC bitness, it is about Access bitness

Not sure where exactly you would find this for Access 2021, usually File>Help>About

(version 14 is 2010)

or File>Account>About

EDIT : Is it best to ask him to go into the VBA code screens and run 'Compile' ?

If you are on the same bitness, it shouldn't matter but if you are on different bitness then it's worth a try, but I was thinking more decompile then compact, then compile - see this link

How does one decompile and recompile a database application?

I have an Access database application and I would like to know the proper way of decompiling and recompiling it.

stackoverflow.com

The app is apparently using ActiveX and Common Controls libraries so I would take a look at the components used from those libraries. You mention problems with a date picker which is one of the common control components but is not really required since there is a built in version as standard (has been since 2007 - speculation, the app was originally written in access 2003 or earlier i.e. a .mdb)

But the fact the app works OK for you and not for your acquaintance means that the above is probably not relevant. I would check the bitness to eliminate that possibility and wait for some error messages to give you a clue as to where the problem lies

Edit: One other thought - what is the size of the app (your acquaintance)? if approaching 2Gb, that might throw some unexpected errors