Avatar of Alan Varga
Alan Varga
Flag for United States of America asked on

Interrupt Access Launch Before Broken Reference Checking

Is there a way to head off broken reference messages when Access launches?  I need to detect and remove two known references after a database update, launch Access, add the references and launch Access a third time, all without manual intervention.  I wrote a main function and several supporting functions to do that work, but calling the function from my initial form happens too late in the launch sequence.  Running the function from an Autoexec macro is also too late in the sequence.
Microsoft AccessVBA

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
Fabrice Lambert

To my knowledge, this isn't doable.

Can you late bind ? It will be far less troublesome.
Jim Dettman (EE MVE)

It's possible, but requires attention to detail.  Best write-up on all the details is here by Michael Kaplan.


Mark Edwards

The main trick is you can't have ANY modules that load and/or use anything associated with the missing references in your startup.
For example, to check a broken reference for Excel, you have to have a startup procedure that won't load any module that have any code that uses the Excel library.  
Your startup must run first and fix the broken reference BEFORE you ever try to load or use the reference.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Scott McDaniel (EE MVE )

What are the references, and why would you have to handle them in this manner?

Broken references cause problems other than in their own code space, so trying to make something like this that's reliable is going to be very much hit-or-miss. You're much better off handling the references correctly.
Alan Varga

To explain more fully, I am developing an Access 2003 SP2 database that will run on a Windows 7 SP1 computer.  Because another business-critical Access 2003 database is also running there, and we are nowhere near ready to upgrade the code in that one, we are stuck with 2003 for now.

However, my development environment is Access 2016 on a Windows Server 2008 R2 Standard terminal server.  When I finish making changes to my application and compile it, it has references to the Office 2016 versions of Excel and Outlook.  When I copy that to the W7 computer, it fails because it thinks the references should be for Access 2016.  If I manually uncheck the missing/broken references, close the IDE, re-open the IDE and manually select the older references, everything is fine.

I was hoping there was a way to automate this.  The scenario I was going for was to make changes to my copy and store that in a public network location.  When a user on the W7 computer started my application, it would automatically check and fix references, then check if there was a newer version in the public network location, and if found, close down, copy the newer version to the W7 user desktop and restart with references already fixed.

From what I've read and from what all of you have suggested, this probably isn't feasible and I will need to wait until we can upgrade the other Access application (and then Office).  Is that true?
Alan Varga

By the way, I read the article that Jim suggested and played around with the Autoexec macro.  I can Dim ref As Access.Reference, and also loop through each ref in Access.References.  I can detect ref.IsBroken and ref.GUID (but not ref.Name), but I get an "unregistered" error when I try Access.References.Remove ref.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

One method is simply trying to "shuffle" the references. It may work or not, but is simple.
See the attached demo, that has a non-existing reference.

That said, I would strongly recommend to create a VM with the final Access version installed and finalise your application here before deploying it to the server.
Jim Dettman (EE MVE)

However, my development environment is Access 2016 on a Windows Server 2008 R2 Standard terminal server.  When I finish making changes to my application and compile it, it has references to the Office 2016 versions of Excel and Outlook.

  Outside of the reference issue, this is a bad idea.   There are too many things in Access 2016 that are not backwards compatible with A2003 and you can easily start to use them without realizing it  (ie. themes)

  You always want to develop in the lowest common version, not the latest.   VBA will auto update the references for you for a later version, but not do the reverse as you have found.

  Even so, you still need to be careful with what features you use in other products.  Generally though, things are pretty backwards compatible in newer products, which is why you want to develop this way.

Mitch Schwartz

Jim is right about backward compatibility from A2016 to A2003. Why not consider installing a copy of A2003 (preferably in a VM), and do your development there? If you don't have A2003, you can always get it on ebay.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Edwards

I would encourage anyone using references that don't "downscale" (i.e. automatically switch from Excel 2016 to Excel 2003) to learn the joys of "late binding".  Excel and other Office application libraries are not the only ones that don't "downscale".  The "Microsoft Office XX.X Object Library" also doesn't downscale.  (Don't confuse this one with the "Microsoft Access XX.X Object Library")

I always late-bind these non-scaling references, which is easy to do once you know how.  It solves a lot of problems in this area.  Developer's can even use "conditional compilation" to easily switch from early-binding code to late-binding code (and back again) so the developer can use the advantages of early-binding like intellisense for development, then switch back for operational deployment.

I'm not going to try and give lessons on how to do this here, as there is a wealth of "how-to's" already out there.  I will admit that these are the kinds of development tricks that developers with "big boy" development experience learn, but you won't find them in any Access book.  Since Access VBA is a subset of the enterprise-level VB 6.0/VS 6.0 development, you can use a lot of the same tricks you learn there in Access/VBA.

To see some of these tricks in action, go to YouTube.com and search for "Access Application Shell - Get the Code!" and watch the video to see some of the things that CAN be done with advanced VBA techniques.... but don't buy anything... this is NOT for the purpose of selling anything.
Alan Varga

Thanks Mark, I'll check that out.

Jim, I agree with all of your arguments for not mixing environments.  Since I'm a subcontractor and the company has a different subcontractor handling server setup and network permissions, I'm not able to install any software, including multiple versions of Office.  I also can't do anything with VM's.  What I will try to do is get the sys admin to allow more than one account to sign on concurrently to the Windows 7 machine.  If I can use my own user id, I will be able to develop in Access 2003 on that box and the the references problem goes away.

I'll post back here with the result of that request.
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Alan Varga

RESULT: We did manage to find a retired server that still has Office 2003 on it, and I can access network locations from there.

Thanks for your input, everyone.  This was a great learning experience.  Jim, I'm awarding points to you for sending me the link to the initial article on handling references, as well as for your follow-up.

It's puzzling why Microsoft would build in properties and methods for handling references, but handcuff developers in how and when they can be used.  This doesn't seem very robust.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

This doesn't seem very robust.

 Nature of the beast basically...it's living with a product that doesn't have a true executable that's linked, and yet is allowed to interact with external DLL's, Libs, OCX's, etc.

Mark Edwards

"The more capable something is, the more complicated it is, because it's the complications that give it it's extensive capabilities."

You could make Access much simpler by just giving it built-in wizards and macro capabilities for building "codeless" applications (like the AWA's), or make it a complicated, near-enterprise application builder by giving it the ability to work with VB 6.0 development methods and components.
Anything with BOTH capabilities would be too complicated and frustrating for anyone to comprehend on a practical basis....

....wait......what?  ;-)