2000/2003 Reference Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am in a mixed environment. Some users have 2000, others have 2003. I have
2003 and do all the VBA coding. The Keeper of the DB is on 2000.
I am working with my mdb is 2000 format; however, my references are for
Office 20003.
I have tried exporting from my copy to the "Master" and I have tried the
Keeper importing from my copy to the "Master". In both cases, when the
Keeper opens the Master, it corrupts the database and we have to reload from
backup.
The work around I have come up with is tedious. I copy all code to a
wordpad file and if it is from a form or report, I delete the code and set
the Has Module property to false. The Keeper then imports the objects and
pasts the code from the wordpad files into the appropriate objects and
compiles the database.
This avoids the corruption, but the must be a better way. Had I not already
read all I can find on references and their problems, I would not be posting.

Thanks for any suggestions.
 
Try decompiling the mdb using 2003 before giving the copy to the keeper.

To decompile, enter something like this at the command prompt while Access
is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact the database:
Tools | Database Utilities | Compile.

Access actually keeps 2 copies of each module:
- the text version (what you read), and
- the compiled version (the code that runs.)
Each version of Access uses different binaries for the compiled version, and
it is *very* common for Access to corrupt the mdb when it is trying to
manage different versions.

By decompiling in A2003 before you convert back, and then compacting the
database, you are discarding the A2003 binary. A2000 will then create its
own binary, and so the problem is solved. We have found it essential to do
this every time a database has been opened in a later version, before trying
to run it in an older version again.
 
Thanks, Allen. I will do some testing. I had thought of that, in fact, but
had not really tried it because there are significant differences between the
reference library file locations and names between 2000 and 2003. If I use
this approach, will the machine that is still on 2000 keep its references or
will it try to find the 2003 references?
One other thing I had done to try to resovle this was changing all the
modules that are manipulating Excel from early to late binding, but that did
not resolve the problems.
 
If you use only the essential libraries:
VBA
Access
DAO
then Access will take care of sorting out the changes as you switch
libraries.

If you add other libraries, such as the Calendar Control, MS Graph, Excel,
you are likely to run into problems. You certainly want to use late binding
for the other Office apps.

Decompiling is a separate issue to references.
 
I'm in a similar situation with a 2000/2002 mixed environment and ran into
the References issue. I'm using the 2000 and have some users with 2002. My
workaround was to open a new database on a 2002 PC, set my references, save a
copy to my PC and then export everything into 2002 creating a 2002 "master"
using the 2000 format. I code in 2000, and export updates to the 2002
master. The 2002 master can be opened on my PC so I can test it.

I'm able to require all the users (small environment) to have the reference
file locations standardized. That is a bigger issue in your environment.
Without knowing the number of users have two potentially painful suggestions -
1) have the reference file locations copied/moved to the same path on the
pertinent users PCs
2) identify the various paths of the files, copy the reference files on your
PC to those folders and setup duplicate links

Without a standardized environment, no easy solutions. Isn't reality fun!
 
Okay, it did work without blowing up completely. We had two issues. First,
I have a reference to the Office Object Library 11.0, where in 2000 it is
9.0. When we first opened the mdb on the 2000 machine, 9.0 is referenced,
but 11.0 shows as missing. No problem, we just delete the reference and go
on. The other problem is that in the modules I have added, I specify Option
Explicite and apparently there is some code in the mdb that uses undimed
variables, so now I will have to go through and clean that up.

Thanks for the pointers.
 
Therein lies a big part of my problem. I work where Desktop Support is
responsible for imaging and configuring all computers. I have found that
they are not consistent in the way they do that. I have even found instances
where no Office libraries have been installed at all. Nothing I can do about
it. Oh, and Access is not a "supported product", so they will do nothing to
cooperate.

Just to show you how silly they are around here, we are going to Lawson
which is on Oracle. That is where our data will be coming from, but we will
not update Lawson. I will not be allowed to connect to the Oracle database
to download my data. They are insisting I use Crystal Reports to download
the data into my Access application.

Would you happen to know where a convenient bridge is?
 
You're having as much fun as I am. Originally the backend was stored on a
local server that our network IDs had read-only rights. The server team
thought that was reasonable. It now sits on server in a State far, far away,
but we do have edit capability (the dial-in users love the response time and
I've gotten quite good at optimization). I've used Crystal in the past, but
not as a datafeed! Are they requiring the users to use Crystal as the
datafeed? If so, I'd suggest two things -
1) set up the users with ODBC connections to the Crystal Reports server
2) send the Access users the individual emails/phone numbers of Desktop
Support ;)
 
The users don't need this. They only use the Access database. The source
data now is on another server (in a state far far away). Every month we
import a transaction table we massage into the tables needed to do our work.
As of Jan 1, it will go away and we will have to get our data from an Oracle
database. The owner of the Oracle database will not allow us to connect
using Access. (How the hell does Oracle know what is on the other side of an
ODBC connection). They insist we use Crystal to tranfer the data from Oracle
to Access. All I am hoping is they don't come up with any ideas dumber than
that.

I am seriously considering lying to them and telling them "You betcha we are
using Crystal" and go ahead and link with Access. But, then again, Employment
- good, Homeless - bad.
 
Similar to my situation. I had to use BusinessObjects to extract data from a
Sybase db and a DB2 db and then import the extracts into Access. Once I was
able to prove that the extracts were failing for the Sybase db I was given
drivers and access. But, we don't "own" the DB2 db (we don't even own the
version of BO used for this db) so I still have to rely on BO extracts. When I
show that the data from these extracts are questionable at best, the powers that
be just throw up their hands and say, "What can we do?"

Dig this: every month we get a bunch of text files. These are imported into
Access and parsed into various tables. Extracts from these tables are then sent
to another worker who uploads them into an Oracle db (our data warehouse). We
then report from the Oracle db using BO! GAAAA!!!!!

Drives me up a wall!

Oh, and we have three versions of Access (97, 2K and 2K2) and they're in the
process of rolling out 2K3.

Time for a prolonged visit to an Irish pub.
 
So, the users are not using live data? In this case, you're being required
to run the Crystal Report to download the data and import into Access? What
version of Crystal will you be using? I seem to vaguely remember
Crystal/Access having some form of link capability (probably thinking of
something else; I can research tonight if you want). Hopefully, there are no
memo fields; anything larger than 255 would preclude Excel as the
intermediate file. Obviously, the human factor is making this more difficult
than it need be. On a previous project, with our mgmt support pushing back
we were able to get the Oracle folks to agree to ONE person being allowed to
link to an Oracle query to export the needed data to our database (and she
had to promise to keep her eyes closed when she ran it). This way it was a
one-way process that in no way compromised security. Maybe they would be
amenable (with your manager and two-levels ups support) to this type of
compromise.
 
Klatuu said:
I am in a mixed environment. Some users have 2000, others have 2003.
I have 2003 and do all the VBA coding. The Keeper of the DB is on
2000.
I am working with my mdb is 2000 format; however, my references are
for Office 20003.
I have tried exporting from my copy to the "Master" and I have tried
the Keeper importing from my copy to the "Master". In both cases,
when the Keeper opens the Master, it corrupts the database and we
have to reload from backup.
Click to expand...

Is this the dread "Error accessing file. Network connection may have
been lost" corruption, as documented in KB article 304548:

http://support.microsoft.com/default.aspx?scid=kb;[LN];304548

? If so, has the "Keeper of the DB" updated that installation of Access
2000 with the latest service packs, as directed in the KB article?
 
I feel better now, knowing that our management is not that much crazier than
some others. The scaries part is I work for one of the world's largest IT
providers!
Thank goodness there are no memo fields, so I don't have to face that.
Our users do not work with "live" data. Our accounting system is currently
Ross. As of Jan 1, 2006, we will be going to Lawson. Neither Ross or Lawson
will support the reporting and analysis we do of the data. Currently we
download an Access table that is prepared for us at another site. That table
is then parsed into the tables we need to do our reporting.
When we go to Lawson, which is on Oracle, we will have to do our own
downloads. This is where it gets goofy. Since the powers that be have
ordained that Access is an evil, unreliable, unclean application, we will not
be allowed to connect to Oracle vai Access. We will be required to dowload
using Crystal 9.0.
So, they are going to pay about $3,000 US to train me on Crystal so I can do
the downloads.
You raise an issue I was not considering. I don't currenly know Crystal,
but you said something about Excel. Are you saying that I will have to go
from Oracle to Crystal to Excel and then to Access. I hope I misunderstood.
That would make it even more stupid.
The interesting part is that Oralce will not know what is on the other side
of an ODBC connection. They will give us access to Oracle, but not allow
Acces to be used to do the download.
 
Didn't mean to scare you. I used 8.5 that didn't have direct link capability
to Access. That feature was added in Version 9. It is amazing the hoops
we're required to jump through in the name of security(/ignorance). A
one-way datafeed is a one-way datafeed whether to Crystal or to Access - oh
well! Sounds like you're doing the best you can in the environment you're
in. Good luck!
 
Went through that months ago, Dirk. We have that under control. This had to
do with reference problems between versions. We tested this procedure, and
it is working fine.
1. Use only late binding
2. When I complete my development, I export the modified objects to a
temporary mdb.
3. Decomplie the temp mdb
4. Compact and Repair the temp mdb
5. Keeper with the 2000 version imports the objects from the temp mdb
6. Keeper compiles and distributes the new production mdb.

Dirk Goldgar said:
Klatuu said:
I am in a mixed environment. Some users have 2000, others have 2003.
I have 2003 and do all the VBA coding. The Keeper of the DB is on
2000.
I am working with my mdb is 2000 format; however, my references are
for Office 20003.
I have tried exporting from my copy to the "Master" and I have tried
the Keeper importing from my copy to the "Master". In both cases,
when the Keeper opens the Master, it corrupts the database and we
have to reload from backup.
Click to expand...

Is this the dread "Error accessing file. Network connection may have
been lost" corruption, as documented in KB article 304548:

http://support.microsoft.com/default.aspx?scid=kb;[LN];304548

? If so, has the "Keeper of the DB" updated that installation of Access
2000 with the latest service packs, as directed in the KB article?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Klatuu said:
Went through that months ago, Dirk. We have that under control.
Click to expand...

Good. I was just checking, as that's the only scenario I'm familar with
in which just importing objects corrupts the database. If you have a
different, reproducible scenario that isn't already in the KB, maybe
Microsoft should be notified.
 
Good suggestion, Dirk, I will look into that. My campaign now is to get the
company to purchase the toolkit so I can deliver a 2003 mde and 2003 runtime,
DLLs, etc.
That is an uphill battle, because when I came here I was astounded to find a
place where everything that can be done wrong has been.
Both FE and BE are on the same server and all users use the same FE.
There are no relations set in the data
The month end processing is all in the BE (forms, queries, etc)
All imports and exports to and from Excel are done manually. (Why is our
data so bad?)
Every table that is created uses the default text field size of 255.
They are proud that they have 435 queries to do the processing and each
month about a dozen of those have to be modified to change the current month
and the calculation for the YTD amounts (which the store in 5 different
places).
My challenge here is not as technical as it is political. For example, as
of 1/06 we are moving to a different accounting system from which we will
pull our data. The business unit that "owns" the Oracle database on which
the Accounting system is based refuses to allow us to connect directly using
Access. We will be required to pull the data from Oracle using Crystal into
an Excel spreadsheet we will then have to import into Access.
Their reasoning is that "we have had too many problems with Access, and we
don't want to use it." Duh!
They did an analysis to determine what it would cost to replace our Access
system and came up with $1.5 megabucks. Guess what they decided. "A new
system is too expensive, but we don't want you using Access."
I need a nap.
 
Klatuu said:
Good suggestion, Dirk, I will look into that. My campaign now is to
get the company to purchase the toolkit so I can deliver a 2003 mde
and 2003 runtime, DLLs, etc.
That is an uphill battle, because when I came here I was astounded to
find a place where everything that can be done wrong has been.
Click to expand...
[description of incredible morass snipped]

Wow. You've got your work cut out for you. And of course it'll be hard
to get them to authorize the work to fix all this, because "It's
working." -- except it isn't, really. I guess if I were in your place,
I'd be looking to document how much valuable time is being wasted by the
current setup, so as to make a business case for fixing it.
I need a nap.
Click to expand...

Good luck when you wake up.
 
You got it! I have been fighting that battle for 10 days less than a year.
Had they let me at it, it would be done by now. The response is "Well, we
know it isn't perfect, and some day we will get to that, but right now can
you do this other report for us?"
My analogy to them has been "You have a house with a crumbling foundation
and you are asking me to add a second story."

Dirk Goldgar said:
Klatuu said:
Good suggestion, Dirk, I will look into that. My campaign now is to
get the company to purchase the toolkit so I can deliver a 2003 mde
and 2003 runtime, DLLs, etc.
That is an uphill battle, because when I came here I was astounded to
find a place where everything that can be done wrong has been.
Click to expand...
[description of incredible morass snipped]

Wow. You've got your work cut out for you. And of course it'll be hard
to get them to authorize the work to fix all this, because "It's
working." -- except it isn't, really. I guess if I were in your place,
I'd be looking to document how much valuable time is being wasted by the
current setup, so as to make a business case for fixing it.
I need a nap.
Click to expand...

Good luck when you wake up.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top