Reference nightmare (again)

[Bingo]

Simple problem: Database works on one machine. I move it to another,
broken references. I have code to fix these, which isn't running
because Access has to compile the modules first, when it fails with
(guess what) broken references. Which reference isn't important,
since *this* time it's failing on Mid$, believe it or not.

I'd *like* to use something like wzRef, but I need one written in
*literally* THIS century, because Access2002 want nothing to do with
a 1998 add-in.

I'm under time pressure to get this working as a self-installing
self-contained (VBA only) database application. I've search in a lot
of places for an answer and I've found no intelligent solution to
this *major* design flaw in Access. How does anybody ever distribute
a commercial application with this bug?

Anybody? I'm out of guesses.

 

[Dirk Goldgar]

Simple problem: Database works on one machine. I move it to another,
broken references. I have code to fix these, which isn't running
because Access has to compile the modules first, when it fails with
(guess what) broken references. Which reference isn't important,
since *this* time it's failing on Mid$, believe it or not.

I'd *like* to use something like wzRef, but I need one written in
*literally* THIS century, because Access2002 want nothing to do with
a 1998 add-in.

I'm under time pressure to get this working as a self-installing
self-contained (VBA only) database application. I've search in a lot
of places for an answer and I've found no intelligent solution to
this *major* design flaw in Access. How does anybody ever distribute
a commercial application with this bug?

Anybody? I'm out of guesses.

Have you read Michael Kaplan's article?

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

The server seems to be down right now, so check later to see if it's
back up.

 

[Bingo]

Have you read Michael Kaplan's article?

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

The server seems to be down right now, so check later to see if
it's back up.

I probably have, but I'll check it when the server's up.

I figured out how to update the wzRef.mda, but when I tried to run
it I got a "file not found" error, then realized I had to install
the add-in, then got "Add-in manager not installed." Grrrrr.

 

[Albert D. Kallal]

I am just jumping in on this thread, so if I missed something, my apologies.

The problem of broken refs is usually due to you not using late binding, or
that fact that some software on the target pc is missing.

Thus, you do want to take a few general steps to prevent broken refs. I
followed the below, and despite having ms-access code that uses word, and
ms-access code that uses Outlook, I have NEVER experienced a broken
reference since following the below advice:


#1) Use late binding, and REMOVE ALL possible references in the
tools->references that are NOT needed. So, remove outlook remove word, and
in fact, remove everything EXCEPT for:

Visual Basic for Applications
Microsoft Access 10.0 Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

And, if your code uses dao, then you need to include:

Microsoft DAO 3.6 Object Library.

You generally do not need anything more then the above.

I assume for any automation code that uses word, Excel etc, you use late
binding...right?

#2) Ensure that the target pc is running the same updates to the office, and
also MOST important, the same updates to JET. To risk, or miss-matched the
updates is really silly, and in effect means you are running a different
version of office, or a different version JET on the target pc, and thinks
will break. So, simply ensure that the target pc is running the SAME version
of office, and that of course includes the SAME updates to office, or you
are NOT running the same stuff on the target pc.

So, ensure that target pc has the same updates to office, and JET, and you
should be just fine.

 

[Bingo]

I am just jumping in on this thread, so if I missed something,
my apologies.

The problem of broken refs is usually due to you not using late
binding, or that fact that some software on the target pc is
missing.

Thus, you do want to take a few general steps to prevent broken
refs. I followed the below, and despite having ms-access code
that uses word, and ms-access code that uses Outlook, I have
NEVER experienced a broken reference since following the below
advice:


#1) Use late binding, and REMOVE ALL possible references in the
tools->references that are NOT needed. So, remove outlook remove
word, and in fact, remove everything EXCEPT for:

Visual Basic for Applications
Microsoft Access 10.0 Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

And, if your code uses dao, then you need to include:

Microsoft DAO 3.6 Object Library.

You generally do not need anything more then the above.

I assume for any automation code that uses word, Excel etc, you
use late binding...right?

#2) Ensure that the target pc is running the same updates to the
office, and also MOST important, the same updates to JET. To
risk, or miss-matched the updates is really silly, and in effect
means you are running a different version of office, or a
different version JET on the target pc, and thinks will break.
So, simply ensure that the target pc is running the SAME version
of office, and that of course includes the SAME updates to
office, or you are NOT running the same stuff on the target pc.

So, ensure that target pc has the same updates to office, and
JET, and you should be just fine.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

Here's the problem in a nutshell: I have to hand an application
over to customers who barely know what a computer is, and not
knowing What state their machine is in (though presumably having
Access installed). If I'm having this mess on the second machine
in my office running the same operating system, the same version of
Office how are my going to get ever automate this for Joe SIxpack
who's still looking for the "any" key? I can eventually rewrite
some of the code so that is uses Late Binding, but it doesn't help
right now.

I know if Mid$ isn't working, something is painfully wrong.

 

[Albert D. Kallal]

I know if Mid$ isn't working, something is painfully wrong.

In access 97, often just a miss matched JET would cause all functions in the
expression service to break (ie: left, right mid all would break). By
expression service, we mean code that is evaluated at run-time vs compile
time.

Broken refs is a very common question/problem in this newsgroup.

Regardless, simply specifying that a office update, and JET update needs to
be installed is not a big deal here.

I remember delivering the a application to a office with 5 pc's. This
application was a typical ms-access application (split, each user gets a
pre-compiled mde, and the back end was a mdb file on the server - you should
be using this setup also. And, you should be use a mde since un-trapped
errors DO NOT re-set all variables!). Anyway, one of the
pc's was giving WRONG, or different answers for a sales report! In other
words, running the same application on different pc's in the office on the
SAME DATA was giving different results. Worse, was that this report was for
setting the yearly advertising budgets, and it was wrong! Of course, I
traced this problem down to the fact that the problem pc was running a
different version of JET. Thankfully, we caught this error, as in fact
the company would have spent the wrong amount on advertising, and could
have in fact make a real big mistake here. It would have NOT looked
good on my software skills either! Of course, I removed the mde, and
then placed a copy of the mdb (front end) on that pc, and checked
the references. By running code, and testing the query, I found
the prblem was that a expresison in a query was NOT working
(one of those common funtions was NOT working).

Installing a updated version of JET actually fix this problem!

Since that day, I have simply specified that the sr2b and JET35sp3.exe be
installed on all pc. This was of course for access 97. After that day, I
have installed my software on a ton of pc's, and for at least 3 years now, I
have NOT HAD ONE broken reference since that day!

I simply make sure the target pc has the same updated version of JET, and
the same goes for the office update as what I developed the software on. In
fact, at that time, I actually put the updates I needed on a cd. It was a
very simply matter to have the support person, or people simply install
those two things before running my software.

If you think about this, you will quickly realize that you can't try to
develop an application developed on one pc with a bunch of special updates
and code libraries that your code uses, and then expect your applction
to run on antoher pc without those same updates and code on the target pc.
It really can't work any other way. Futher, you may have
been installing all kinds of stuff for a long time that actually makes your
pc work!

So, you need to ensure that your deveopment pc has a contorled set of
udpates, and then simply make sure the target pc has those same updates
also.

I am now just starting to deply stuff with a2002, and I don't have good
deal of feedback as to when, how and what prevetns things from breaking,
but I guessing the same will applity.

You don't menton if you are able to remove some referances, but the less the
better. There is not a develper around who would even consider deplying an
applction with automaton code to other appctions without using late binding.

Here is some tips on dealing with referances:

Allen Browne
http://users.bigpond.net.au/abrowne1/ser-38.html

Doug Steele:
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

Peter Walker:
http://www.papwalker.com/dllhell/index-page2.html


MsKb Articles: 310803, 208218, 209849, 286300

ACC2000: How Access 2000 Resolves Visual Basic for Applications References
http://support.microsoft.com/default.aspx?scid=kb;en-us;248941

ACC2000: How to Resolve Reference Issues in an Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;310803

 

[Rick Brandt]

I know if Mid$ isn't working, something is painfully wrong.

[snip]
I simply make sure the target pc has the same updated version of JET, and
the same goes for the office update as what I developed the software on. In
fact, at that time, I actually put the updates I needed on a cd. It was a
very simply matter to have the support person, or people simply install
those two things before running my software.

[snip]

I have to agree with Albert here. I have never distributed an app that
required a reference beyond the defaults assigned when creating a new file
and have never felt restricted by avoided them. I have had a few occasions
where I was late binding to an external library (CDO or MSXML) and found a
user where those libraries were missing, but it never caused the
"references bug" and was easy to diagnose and resolve because the missing
library caused an error at a point where it was fairly obvious what was
missing.

 

[Bingo]

I know if Mid$ isn't working, something is painfully wrong.

[snip]
I simply make sure the target pc has the same updated version
of JET, and
the same goes for the office update as what I developed the
software on. In
fact, at that time, I actually put the updates I needed on a
cd. It was a very simply matter to have the support person, or
people simply install those two things before running my
software.

[snip]

I have to agree with Albert here. I have never distributed an
app that required a reference beyond the defaults assigned when
creating a new file and have never felt restricted by avoided
them. I have had a few occasions where I was late binding to an
external library (CDO or MSXML) and found a user where those
libraries were missing, but it never caused the "references bug"
and was easy to diagnose and resolve because the missing library
caused an error at a point where it was fairly obvious what was
missing.

Thank you both for your feedback. Some very useful information.

 

[Bingo]

In access 97, often just a miss matched JET would cause all
functions in the expression service to break (ie: left, right
mid all would break). By expression service, we mean code that
is evaluated at run-time vs compile time.

Broken refs is a very common question/problem in this newsgroup.

Regardless, simply specifying that a office update, and JET
update needs to be installed is not a big deal here.

I remember delivering the a application to a office with 5 pc's.
This application was a typical ms-access application (split,
each user gets a pre-compiled mde, and the back end was a mdb
file on the server - you should be using this setup also. And,
you should be use a mde since un-trapped errors DO NOT re-set
all variables!). Anyway, one of the pc's was giving WRONG, or
different answers for a sales report! In other words, running
the same application on different pc's in the office on the SAME
DATA was giving different results. Worse, was that this report
was for setting the yearly advertising budgets, and it was
wrong! Of course, I traced this problem down to the fact that
the problem pc was running a different version of JET.
Thankfully, we caught this error, as in fact the company would
have spent the wrong amount on advertising, and could have in
fact make a real big mistake here. It would have NOT looked good
on my software skills either! Of course, I removed the mde, and
then placed a copy of the mdb (front end) on that pc, and
checked the references. By running code, and testing the query,
I found the prblem was that a expresison in a query was NOT
working (one of those common funtions was NOT working).

Installing a updated version of JET actually fix this problem!

Since that day, I have simply specified that the sr2b and
JET35sp3.exe be installed on all pc. This was of course for
access 97. After that day, I have installed my software on a ton
of pc's, and for at least 3 years now, I have NOT HAD ONE broken
reference since that day!

I simply make sure the target pc has the same updated version
of JET, and the same goes for the office update as what I
developed the software on. In fact, at that time, I actually put
the updates I needed on a cd. It was a very simply matter to
have the support person, or people simply install those two
things before running my software.

If you think about this, you will quickly realize that you can't
try to develop an application developed on one pc with a bunch
of special updates and code libraries that your code uses, and
then expect your applction to run on antoher pc without those
same updates and code on the target pc. It really can't work any
other way. Futher, you may have been installing all kinds of
stuff for a long time that actually makes your pc work!

So, you need to ensure that your deveopment pc has a contorled
set of udpates, and then simply make sure the target pc has
those same updates also.

I am now just starting to deply stuff with a2002, and I don't
have good deal of feedback as to when, how and what prevetns
things from breaking, but I guessing the same will applity.

You don't menton if you are able to remove some referances, but
the less the better. There is not a develper around who would
even consider deplying an applction with automaton code to other
appctions without using late binding.

Here is some tips on dealing with referances:

Allen Browne
http://users.bigpond.net.au/abrowne1/ser-38.html

Doug Steele:
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.
html

Peter Walker:
http://www.papwalker.com/dllhell/index-page2.html


MsKb Articles: 310803, 208218, 209849, 286300

ACC2000: How Access 2000 Resolves Visual Basic for Applications
References
http://support.microsoft.com/default.aspx?scid=kb;en-us;248941

ACC2000: How to Resolve Reference Issues in an Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;310803


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

That's what I've been searching for: the voice of experience! Some
very useful info, as well as a nice horror story. (I have a
similar one where I almost cost the Hartford Insurance copmpany 10
million dollars, except that it *was* my fault!)

The two books I have both recommend early-binding, but when I read
closer, they also warned about the references as a case for late-
binding. To be honest, I'm coming from an Oracle and DB2
background, so this whole Access environment is still strange to
me, and I still this this reference stuff is a major oversight.
But I can only deal with what IS and not should be (got that from a
zen tape!), so thank you.

 

[Bingo]

Does developing the front-end in visual basic (standalone) solve any
of this? Or does it create different but similar problems?

 

[Tony Toews]

Since that day, I have simply specified that the sr2b and JET35sp3.exe be
installed on all pc. This was of course for access 97. After that day, I
have installed my software on a ton of pc's, and for at least 3 years now, I
have NOT HAD ONE broken reference since that day!

To add to Albert's comments:

What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the tips page at my
website for more details including sample code: Verify Appropriate Jet
Service Pack is installed www.granite.ab.ca\access\verifyjetsp.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 Toews]

Here's the problem in a nutshell: I have to hand an application
over to customers who barely know what a computer is, and not
knowing What state their machine is in (though presumably having
Access installed).

Which is precisely why Late Binding is so useful. It isolates your
app and only the offending line(s) of code will cause problems. Not
your entire app.

I can eventually rewrite
some of the code so that is uses Late Binding, but it doesn't help
right now.

Late binding only requires two lines of code per each time you Dim the
reference. Standard blurb follows:

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 stuff. Then once
its running smoothly remove the reference and setup the late binding
statements.

Sample code:
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim objWordDoc As Object
Set objWordDoc = CreateObject(" Word.Document")

For more information including additional text and some detailed links
see my Tips page on this topic.

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

 

[Ron Hinds]

Does developing the front-end in visual basic (standalone) solve any
of this? Or does it create different but similar problems?

No, it doesn't. And although you got some good advice above, if you really
want to solve this problem, invest in a Setup utility such as Wise Installer
(http://www.wise.com). That's what I use to distribute my Access app (also
to "Joe Sixpack") and I've *never* had this referencing problem. You make
the necessary OCX's/DLL's part of the install script. If they don't exist,
or they do but are an older version than you require, they are automatically
installed. Done!

 

[Albert D. Kallal]

Does developing the front-end in visual basic (standalone) solve any
of this? Or does it create different but similar problems?

In fact, any windows developer has to deal with what is known as dll hell.
It simply means that when you deploy your application, and use some library
code, you HOPE that this library code is the same on the target pc. You can
most certainly tell VB to include library code when you deploy, but you
still have to hope that another application does not come along and also
include (and thus deploy) and change one of those libraries. So, this
problem of libraries and references is common to all windows development.

So, while developing in VB is certainly much better, there is STILL the
problem of references and libraries. Dealing with this problem is what ANY
windows developer has to be aware of. So, note how a few years ago I was
VERY frustrated. Then for 3 years straight I NEVER had a problem with any
deployment.

Further, the general consensus in reading about VB applications is that a
VERY LARGE number of VB developers (we are talking VB here, not ms-access
developers!) recommend early binding. In fact, a few months ago when doing
some reading on this subject, I was quite shocked to find a very large
portion of developers recommending early binding, and NEVER was a
distinction made for automation code for things like office. Obviously, this
problem of automation is less in VB...but still does exist.

Without question for library code that YOU develop in VB or c++, then early
binding is to be used. However, for anything like office automaton (ie:
launching word, Excel, etc), then the performance hit is so small as to not
even be a issue. I mean, so what if you add .1 second to the loading of
word. You are not loading word 1000 times. If you were calling, or loading
some library code 1000 times, then that .1 second penalty becomes a 100
seconds, and that is a HUGE performance hit for application.

Thus, most, if not all VB developers will tell you to use early binding. The
real issue here is using early binding for application automation (to a
particular version of word, or Outlook). Since in VB you can, and do write
your own library code and then use a reference to that code, then early
binding is the theme of the day, and most VB developers would not give this
issue a 2nd thought. However, in ms-access we RARELY are wring a library of
code, and then setting a reference to it. Thus, early binding to ms-access
developers is usually not an issue. Further, early binding for office
automaton is simply silly. I mean, setting a reference to a particular
version of word on your pc, and then moving the code to a pc that does not
have word, or is running a different version of word will likely break your
application. I mean, it is far better to accept the 1/10th of a second delay
loading of word by using late binding, and have your code work on all pc's
(that 1/10th of second is just not worth it). Somewhere along the way, this
opinion of VB developers needs to be clarified.

So, yes, using VB would reduce this references problem, but not by much, and
if you have some experience, and learn what to avoid, then VB will not offer
that much more then what you got with ms-access.

However, VB is a LOT better from a distribution point of view, and one MAIN
REASON is that VB is NOT tied to a particular version of office like
ms-access is. ms-access strength is that it is tied to office, and is part
of office. This fact of being part of office is also its downfall when
distributing a software package. For wide distribution, and distribution to
pc's where you don't have control of the install, then VB is better.

There is LARGE number of problems you need to be aware of (or shall I say
avoid) when trying to run a ms-access application on a pc that has a
different version of ms-access! You can however purchase the developers
edition of office, and create a installing package from ms-access. The
installer does have provisions for including things like JET etc. However,
there still is problems when installing to pc's that already have office
installed. The solution for that is then to purchase some scripts from

www.sagekey.com

The above talk about the package and deployment, and use of the developers
edition of office is another whole ball of wax, but I did want you to be
aware of this fact. Setting up a stand alone program that can be installed
on a pc can be a very large amount of work. Further, to use the developers
edition of ms-access, then you need to create things like your own menu
system etc. I would only recommend the developers edition after you learned
the ins and outs of ms-access, and what works, and what does not work.

 

[Bingo]

Which is precisely why Late Binding is so useful. It isolates
your app and only the offending line(s) of code will cause
problems. Not your entire app.


Late binding only requires two lines of code per each time you
Dim the reference. Standard blurb follows:

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 stuff. Then
once its running smoothly remove the reference and setup the
late binding statements.

Sample code:
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim objWordDoc As Object
Set objWordDoc = CreateObject(" Word.Document")

For more information including additional text and some detailed
links see my Tips page on this topic.

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

Installed the beta yesterday after applying the gathered knowledge
of all you salty Access developers. It went VERY well! Thankx!
The only real issue was ADO 2.7 vs. ADO 2.5, but easily fixed!

 

[Bingo]

msjetxx.dll,

Thanks. I'll check this. For customer problem identification, I'm
starting to think about how to gather all this information.

 

[OceanView]

Wise Installer
(http://www.wise.com)

Delayed response: Thanks! Good info. I've been busy. Reference
problems now gone!

 

[Bingo]

Wow! What a minefield! Thanks for the extensive roadmap.
Sorry for the delay in response. I've been "deploying."