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.
LVL 2
Alan VargaAccess DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
To my knowledge, this isn't doable.


Can you late bind ? It will be far less troublesome.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It's possible, but requires attention to detail.  Best write-up on all the details is here by Michael Kaplan.

http://www.trigeminal.com/usenet/usenet026.asp

Jim.
0
Mark EdwardsChief Technology OfficerCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Alan VargaAccess DeveloperAuthor Commented:
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?
0
Alan VargaAccess DeveloperAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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.
CheckReferences.accdb
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Jim.
0
ms3930Commented:
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.
0
Mark EdwardsChief Technology OfficerCommented:
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.
0
Alan VargaAccess DeveloperAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This is something you should insist on.  You can't do the job properly unless you develop in A2003.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alan VargaAccess DeveloperAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Jim.
0
Mark EdwardsChief Technology OfficerCommented:
"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?  ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.