App Fails From Missing Reference

[Guest]

The following sequence causes a total Access app failure:
(1) develop Access app with Excel automation on machine with Excel
(2) deploy app to machine without Excel installed
(3) launch app - FAILURE

Alternatively, this sequence at least permits the app to open:
(1) develop Access app with Excel automation on machine with Excel
(2) remove Excel reference (prevent full app compile)
(3) deploy app to ANY machine
(4) launch app - SUCCESS

Of course, in the second case, any of the Excel functions in the app fail,
regardless of whether Excel is installed, because the reference is removed.

So, what is the solution ?
BrerGoose

 

[Tony Toews [MVP]]

The following sequence causes a total Access app failure:
(1) develop Access app with Excel automation on machine with Excel
(2) deploy app to machine without Excel installed
(3) launch app - FAILURE

Late binding means you can safely remove the reference and only have
an error when the app executes lines of code in question. Rather than
erroring out while starting up the app and not allowing the users in
the app at all. Or when hitting a mid, left or trim function call.

You'll want to install the reference if you are programming or
debugging and want to use the object intellisense while in the VBA
editor. Then,. once your app is running smoothly, remove the
reference and setup the late binding statements.

For more information including additional text and some detailed links
see the "Late Binding in Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Allen Browne]

The safest choice is to avoid using any references that may not be on
machines you will deploy your app to.

This would mean avoiding the use of Excel functions. You would need to write
your own VBA function that performs the same functionality. I suspect most
seasoned Access developers use only minimal references to ensure their apps
work. If you are not sure what references you need in different versions of
Access, see:
http://allenbrowne.com/ser-38.html

If you really want the Excel functionality, and you are certain that your
target machines will have some version of Excel on them, you can use late
binding. This involves omitting the Excel reference, and declaring the Excel
structures As Object instead of referring to them explicitly. VBA will then
sort them out at runtime instead of at compile time. This approach is quite
common where an application must work with multiple versions of the Office
products. Here's an introductory article about that:
http://support.microsoft.com/kb/210111/en-us

 

[Guest]

Thank you very much Allen and Tony for your detailed and prompt replies.

Late binding was sort of my thought, but my concern was the removal of the
Excel reference, for the following reasons:

(1) once you remove the reference, you cannot compile the app
(2) might affect save as MDE
(3) won't a machine with Excel fail without an Excel reference ?

And the app definitely needs the Excel automation, as that is one of the
main features !!

 

[Douglas J. Steele]

If you do late binding properly, your app will compile.

1) You have to remember to change any occurrences of

Dim xlApp As Excel.Application

to

Dim xlApp As Object

2) You need to ensure that you're not using

Set xlApp = New Excel.Application

Instead, you need to use

Set xlApp = CreateObject("Excel.Application")

3) If you're using intrinsic Excel constants (they all start xl...), you
need to either replace the constants with their actual values, or else
declare them in your application.

Since the application will compile, there's no problem converting the MDB to
an MDE.

Yes, the code will fail on a machine that doesn't have any version of Excel
installed. You need to ensure that you trap the error 429 that occurs when
you try to instantiate the Excel object.

 

[Guest]

Thank you very much Douglas. Not only did you answer my follow-up questions,
but you even anticipated a few more and answered those as well !!

BrerGoose

 

[Guest]

Well, new twist....

Original app includes Excel 9.0 reference.
Modified app excludes Excel reference.
No other differences.

Original and modified app launches on Excel 9.0 machine, as expected
Original app fails, modified app launches on Vista machine without Excel, as
expected
Original and modified app fails on Vista machine with Excel 2007, unexpected

What do you make of that ?
BrerGoose

 

[Douglas J. Steele]

What errors do you get?

 

[Guest]

Actually, one of my customers is the one with the error on launch. His new
Vista machine has Office Pro 2007. This is the thread to the error situation.

http://www.aimoo.com/forum/postview.cfm?id=721811&CategoryID=503475&startcat=1&ThreadID=2877324

Thank you.
BrerGoose

 

[Guest]

You mentioned an error could result from mid, left, or trim. Why is that an
issue ?
Also, my startup function uses Left$ a couple of times.

Thank you.
BrerGoose

 

[Tony Toews [MVP]]

You mentioned an error could result from mid, left, or trim. Why is that an
issue ?

Because when the reference is missing those functions frequently won't
work.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Actually, one of my customers is the one with the error on launch. His new
Vista machine has Office Pro 2007. This is the thread to the error situation.

http://www.aimoo.com/forum/postview.cfm?id=721811&CategoryID=503475&startcat=1&ThreadID=2877324

So your customer is stuartbrody and the error is 2950. No, that
states "After is gives the error discribed before. I click ok on that
and another box comes up with ACTION FAILED AS THE HEADING"

Forget it. I'm not reading through that unthreaded stuff trying to
figure out what is going on.

Please post the text of the error message here.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/