Code running in .accdb, not on all .accde installations - i empty procedures the problem?

BAnders
BAnders used Ask the Experts™
on
Hi,
Previously, I have asked for a right-click (shortcutmenu) problem on a treeview which always works with .accdb but not for .accde on some PC. My all customers have that problem and not I, but a while ago, one of my Pc started to have the same problem (likely after an update - that PC is seldom used). So far I have focused on the environment like anti-virus, windows etc as well as ask for Microsoft support. Today I found that empty procedure can cause this type of problem, see

https://social.msdn.microsoft.com/Forums/office/en-US/fb52e127-a8de-458f-9199-5579ed108327/vba-doesnt-work-in-accde-but-does-in-accdb?forum=accessdev

I am running Access 2016 (Office 365 bundled) and use Windows 7 and 10 both Pro.

Since I am develop a new version, I have some empty procedures - old stuff that might be use. And, it is the same shortcutmenu .onaction code that is not called.

Is the empty procedures a serious candidate to cause .accde not to excute some code?

Thanks in advance.

B Anders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Is the empty procedures a serious candidate to cause .accde not to excute some code?>>

 It is really hard to say.   No one outside of Microsoft would really know the answer and even they might not.

 An empty procedure should just be ignored and that's it, but there may be unforeseen consequences.

 I've never had a problem with empty procedures existing, but then I don't make a habit out of leaving them behind.   If I see one, I always remove it.  

 The critical thing though that most ignore is their app not compiling.   I constantly compile and make sure I am error free as I develop.  As a result, I have had very few instances of problems with code in executing or creating a .accde.

Jim.
BAndersConsultant

Author

Commented:
Thanks Jim.

Frequently, at least daily I compile, and if the file starts to grow, I compile with the /decompile switch set. But, then compiling, is it just loaded Forms and the Modules object that are compiled?

I have a Releasebuilder that is among many tasks set building number and a couple more const statements and compile. If reply to the question is yes, I might should modified the Releasebuilder to open every form and compile.

B Anders
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Short answer is yes.  Yet another bug.

http://www.devhut.net/2018/10/31/ms-access-bug-vba-code-doesnt-run-as-accde/

It doesn't happen in all cases, but does happen and I doubt we'll ever get an explanation as to the exact scenario it causes an issue.  You simply have to always perform a cleanup before deploying.

The bigger issue here is that this has nothing to do with compilation.  The database will compile just fine, and Access will generate the ACCDE, but the ACCDE will not run the VBA code.  So relying on the compiler to flag the issue in this instance is useless.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Frequently, at least daily I compile, and if the file starts to grow, I compile with the /decompile switch set. >>

 I would keep compiling frequently, but I myself would not use /decompile routinely.   I know many do, but I've seen databases messed up using it.   Although that has been VERY rare (a couple of times in the past 20 years), I have seen it happen.   Only time I ever /decompile is if I see code not executing correctly and I don't do it as a matter of routine.

 For routine work, I like to create a new DB container and then import everything in to clean things up.

<< But, then compiling, is it just loaded Forms and the Modules object that are compiled?>>

  No.  All VBA code is compiled. But with that said, the compilation process is complicated and "compiling" doesn't do everything from start to finish each time.   At one point, there were over 16 different states of compilation that a piece of code could be in.

Jim.
Mark EdwardsChief Technology Officer

Commented:
There is a difference in how an Access app runs in a full version of Access or in the runtime, and as an .accdb or an .accde.
One clue should be all the extra "stuff" that you have with a full version of Office installed versus just the Access runtime installation.
Another clue is the fact that you can only create an .accde with the same version of Access that the app/file was made with (at least in every case I've tried....).

If your app is dependent on "stuff" that you have with your development installation of Office, but it's not on the machines with the runtime, then you are obviously going to have problems that you won't get until you actually run your app.  Installed components can change with updates, upgrades, or rebuilds.  A tree control is one such example.

One of the reasons for running a packaging process for your .accde app that you plan to use with the runtime is to pick up any dependent, external files, ocx controls, or libraries that it needs.  The installation program is designed to make sure your app has all the pieces it needs to run.  The runtime is more-or-less designed to run Access apps that were made with the same version of Access, although, with proper care and planning and Microsoft's best attempts to keep older files working on newer versions of Access, you can get them to run with other versions.

It takes some planning and skill on all fronts to get a hodge-podge of file versions, VBA versions, Access versions and OS versions to run without incident.

Ignorance is a blissful path to frustration, but not if you are aware of it....
Hi,

I'm surprised to hear about the "empty procedures" problem - I frequently use ACCDE because I would never deploy anything else to end users. In the examples of the links above it is all about event procedures. The VBA compiler automatically removes all event procedures which have no contents (which is why I often insert an empty comment row to make sure it doesn't while I'm still developing...). And to create an ACCDE you need to successfully compile the frontend first so there should be no remaining empty procedure, at least event procedures. Maybe it happens if you have empty "normal" procedures like a procedure in a standard module but in the last 20 years of developing with Access I never had that issue. It is more likely that the database file itself is corrupt and I also agree with Jim that I would use decompile very carefully and also only if you really have no other choice. I also successfully destroyed database files with this switch which is officially not supported by Microsoft.
I rather use the (also undocumented and unsupported) "Application.SaveAsText" and "Application.LoadFromText" procedures to export anything and import it into a new database to remove any background "binary trash" which works better than directly importing it using the import wizard.

Some things which can be programmed in ACCDB simply doesn't work in ACCDE, i.e. you cannot program anything which has to do with the design of the database, like opening a form in design mode.

Additionally, especially the ActiveX modules like the TreeView had a lot of issues after several updates of MS which is the reason why I programmed a TreeView with Access methods (TreeTable) which don't need any ActiveX. To be dependent on external DLLs is always a risk if you have a lot of target computers with not exactly matching contents (Windows version, DLL versions, installed updates etc.).

It's always a good idea to compile an ACCDE and test it completely before deploying it, also using a runtime version on a separate computer. Sometimes the runtime behaves different than the full version, one known thing is that a not handled error simply crashes the runtime without any error message where the ACCDB would present the row in the sourcecode where it happened and shows an error message.

Cheers,

Christian
BAndersConsultant

Author

Commented:
Sorry, the delay.

The solution is the following. If you compile an accdb under win 10 it might work under win 7. If you compile an accdb under win 7 it will work under win 7 and 10.  We have done a major test and now it works well.

Lesson learned: As long any customer run win 7 you must have an PC with win 7. That one has extended support. In the future I just wonder if the compile will be win 10 build dependent? I have asked Microsoft, reply is the support plan.

Many thanks to all of you
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
The solution is the following. If you compile an accdb under win 10 it might work under win 7. If you compile an accdb under win 7 it will work under win 7 and 10.  We have done a major test and now it works well.

 Interesting...thanks for posting that.    but that does follow the general rule of thumb with Access (and any software for that matter); always develop/work in the oldest version and bring it forward.   Don't go the other way.

First time I've heard though of the OS getting in the way.

 Just out of curiosity, was the Win 10 install running a 64 bit edition of Office?  or was it 32 bit as well.

Jim.
Consultant
Commented:
Sorry, late answer. I have been busy with other things than coding.

I only use 32-bit edition of Office.

Access is last version but it must be complied on the previous OS (Win7 and since the customers used Win7, eventually they use Win10 now). It seems to be the "compiler" that under Win 10 pickup items changed since Win 7 and therefore it will not work with Win 7. I wouldn't be surprised if it is the MsCOMCTL.ocx that cause the problem.

I agree with the rule of thumb, I have until a year ago followed that one. I just wonder if we must take care of Win10 build versions?

B Anders
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<< I just wonder if we must take care of Win10 build versions?>>

 This is the first time I've seen the issue come up, and not having a definitive answer here, I'm not sure I would say "yes"  to Win 10 being a problem.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial