Serious Issue ...

[QTNM]

My client runs an estimating DB in Access 97 ( I KNOW ... to no avail, I have failed to make them upgrade ) ... anyhow ....

Here is my issue .... it has happened on 3 different systems so I am pretty sure I know what is causing the problem.

Any system that has, or has had Office 2000 and up, installed on it along with Access 97 ( which the DB needs to run ) causes havoc on this DB.

When an estimate runs, a seriers of update queries are executed. I get an error "This must be an updatable query".

So, I try to uninstall all Office programs and install 97 alone, but the same thing happens.

If I install 97 on a clean machine, the program works fine unless Office 2000+ is installed afterwards.

CAN ANYONE TELL ME WHAT IS HAPPENING HERE. WE HAVE A SALES REP IN QUEBEC AND I NEED TO GET HIS ESTIMATING SOFTWARE BACK UP AND RUNNING !!!

ANY SUGGESTIONS / FIXES / COMMENTS / ARTICLES ARE VERY APPRECIATED!

THANKS !
QTNM

 

[QTNM]

I am positive. It's occured on 3 different systems.

I currently run the db on my PC, if I install Office 2000 or +, I will receive the error message. Then I uninstall the Office 2000 or + version and the error message appears! If I format the system, reinstall O97 it works great!!

I DON'T GET IT !! I have completely removed the folder! I have even REINSTALL OFFICE 97 ... to NO AVAIL !! GRrrr!

I know MS changed the data access from ADO to DAO, but I'm not sure that would affect it. It seems it's refering to a file ( possiblly a .dll ), I don't know, but it's contents refer to Office 2000+.

Any takers ? I'm just about to scream! I need to get this guy up and running ... I also need to figure it out cuz the production manager has a home PC and it has the same issue ( bought a new computer with Office 2003, I uninstalled it, installed Office 97 and NOPE, does not want to run the update queries.

I LITERALLY need to go into every query and set the Unique Records. But I have about 140 queries running ..... NO THANKS! Plus this DB is updated quite often.

Anyone ??? It would be greatly appreciated! REALLY !

Cheers!
QTNM

 

[boblarson]

Yes, it CAN happen on 3 different machines and you should do as Pat has said (you haven't been posting long enough to know that Pat Hartman knows what she's talking about).

Check the references for each and move DAO above ADO in the list.

 

[QTNM]

I never insinuated Pat did not know what she was talking about ...

I am familiar with missing reference issues, and all references are in check.

Now can it be that the reference are mapped wrong? Would they have been overwritten by the Office 2000+ installation ? How can I force them to run the original 97 references? I don't understand ? I would assume that the reference files would be overwritten when O97 was reinstalled, no?

If not, please advise.

QTNM

 

[QTNM]

(you haven't been posting long enough to know that Pat Hartman knows what she's talking about).

FRANKLY ... it has nothing to do with how long I have been posting. I take all comments with a grain of salt based on my experience and current knowledge. All comments are welcome, however, I resent the fact you had to put that in my face.

 

[boblarson]

Don't get all in a quiver - I made that comment due to the fact that you had 25 postings at the time and so it didn't look like you had been around the group long enough to know about Pat.

As for reinstalling Access 97 over 2000 - In case you didn't realize, Access 2000 would have several "updated" dll files which, if in place, don't get automatically overwritten by an older program. If a newer file exists during installation, the program will usually bypass that file.

So, have you tried raising the DAO reference in priority over the ADO reference on the 3 machines where you are experiencing this problem?

Remember Access 2000 put ADO over DAO and even if you reinstalled A97 after 2000, it would likely NOT change that back, unless you do it manually.

 

[QTNM]

Will try ...

I don't have the reference list with me ( there were 4 in total ) ... but I know I have already tried to force the refresh on two of the references. The other two would not allow me to do so, stating the DB required them to run.

.... Pat you are right .... I believe there was only a DAO ref. in the list ( need to check again ) ... should I add the ADO ?? DAO must be referenced before ADO, correct ??

Now ... if I have uninstalled O 2000+ and reinstalled '97, will it actually overwrite these .dll, .ocx, type library, etc. files, or keep the other ones that were originally installed with O 2000+ ??? - as per what boblarson stated ? ---> "As for reinstalling Access 97 over 2000 - In case you didn't realize, Access 2000 would have several "updated" dll files which, if in place, don't get automatically overwritten by an older program. If a newer file exists during installation, the program will usually bypass that file. "

If the files were not overwritten, how can I force them to overwrite with the original O97 type libraries ?

Please advise!

I do appreciate your input, this very frustrating for me!

QT = )

 

[QTNM]

Gotcha ... I'll check the knowledgebase ... thxs.

QT

 

[WindSailor]

How to Completely Uninstall Microsoft Access 97
Here

How to Completely Remove Microsoft Office 97
Here

Description of the Windows Installer CleanUp Utility
Here

 

[QTNM]

See, I think the issue if completely removing the OFFICE 2003 version. That's what I think may be causing the problem.

Since none of these systems are accessible to me at the moment, I am going to recreate the issue on a local laptop. O97 is installed. I am going to install the database, check it. Then install O2000, check it. Then uninstall both Offfice products and try to reinstall 97 alone again.

Any suggestions before I reinstall O97?

I am unable to find a cleanup utility for 2000+ ....

QT

 

[WindSailor]

I believe the Windows Installer CleanUp Utility is for Office 2000+

 

[QTNM]

I read through quickly and it sounded to me like the same utility that is used when you choose to Add/Remove programs. This is what I have been using all along.

Hmmm, ok, I have O97 cleanup tool and the Windows Installer CleanUp Utility. I going to try to re-create the problem before I start messing with the clients.

BRB ... wish me luck!

QT

 

[QTNM]

Hhmmm....

Alrighty ....

In my references list, I have ( in order of Priority ):

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.5 Object Library

I cannot move the priority on the DAO priority.

When I run the update queries attempt to run, I get this error:
Run-time error '3073': Operation must use an updatable query.

PAT: I have refreshed the references list .. I added ADO ref. ... swapped them up and down, below and above each other ... to no avail.

Now the path for the MS DAO 3.5 Object Library reference is in: "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO350.dll"

This I am thinking is the file that has not been overwritten by O97 when reinstalled. Should I keep flaking with the references or do you suggest I go ahead with the clean-up utility ??

Please advise ... Thank you!

QT

 

[QTNM]

Installed the Windows Install clean up utility and Office 2003 OR office 97 does not show up in the list !!!!!!

QT

 

[QTNM]

HOW THE !@#$%^ DO I COMPLETELY REMOVE ALL REMINANTS OF OFFICE 2003 ??????? GEEEEEEEZZZZZZ! I'M REALLY GETTIN' TICKED NOW!

FOLDER IS STILL THERE WITH ALL KINDS OF .DLL's ... PLUS I HAVE NO IDEA WHAT OTHER FILES ARE ON THE SYSTEM THAT ARE BEING REFERENCED ....AHHHHHHHHHHH!!!!

QT

 

[QTNM]

OK, i FOUND THE O 2003 RESOURCE KIT ... THIS SHOULD DO THE TRICK ...

QT

 

[QTNM]

AAAAAARRRRRRGGGGGGG!!!!!

I HAVE COMPLETELY REMOVED 2003 using the MS Office 2003 Resource Kit >> Completely Uninstalled Office 97 using Office 97 Eraser tool >>> Ran Registry Mechanic >>> Reinstalled Office 97 >>>> AND NADA!!!!

SAME ERROR !!!!!

WHAT THE ... HECK! DID OFFICE 2003 install that I cannot for the life of me overwrite, remove, reinstall ..... agghhh !

I have this DB running on several Win XP machines, so XP is not the culprit ... I'm really at wits end now ....

Anything else you guys can suggest? I think I have to go back to basics ... the only thing I am absolutely forbidden to do at this time is format this !@#$ system.

Suggestions, comments, references, MS blasts .... are welcome!

QT

 

[WindSailor]

Ok... I may not be the most experienced here to respond to this, but since you are in a hurry and looking for ALL options...

After a brief search, AND going on the assumption that ALL references etc ARE intact. I went another direction.

Core components.

I believe A97 uses JET 3.5; A2000 uses JET 4.0.

In JET 4.0, I believe the sort order has changed from 3.5.

I'm NOT sure if MDAC components would play a part in this, so I will leave this alone for now.

Is there a possibility you are using JET 4.0 with your A97 database after you install Office 2000; is that even possible?

Here are some generic info links...


Here

Here

---------------------Edit----------------

I believe if you had it (Jet4.0); it (Msjet40.dll) would be in the Windows\System32 folder or in the Windows\System folder.

 

[The_Doc_Man]

First, the .DLL files changed somewhat between AC97 and AC2K. You have to take into account that the DAO library changed versions. I am about 580 miles from my reference material so I can't look up the name differences right now. But a reference might be satisfied incorrectly because of version changes and the result would be catastrophic.

Second, you should also consider that starting with AC2K, Windows Updates (visiting the MS Win Upd site) acts differently because of the different way service packs work starting with Win2K. I.e. a WINDOWS issue, not an AC issue per se.

Third, most of the solutions I have seen for running AC2K and AC97 together involve installing AC97 after AC2K and also selecting alternate folders for everything. The registry will handle this correctly but will not handle the two if they are installed in the wrong order. (Despite what Office Installer says, it sometimes LIES when it asks if you want to replace files and you say NO.)

Fourth, depending on service packs will lead you astray. Service packs look at the version you have installed, but if you have one version, it stops looking for the other version and just does a nice update for you. So if you have ever installed a service pack on your Win2K (or later), you might be running into a variant of .DLL HELL due to the Windows Update process. By any chance do you have Automatic Update enabled (for Windows) on any of these systems? Because the Windows Update site WILL cover Office products as well as Windows itself.

 

[The_Doc_Man]

Another question: For the queries that suddenly became "not updatable" ... Do any of them involved a VBA function? You might need to recompile the function once the references get shuffled even if all the files are still present. I believe the references include information that relates to an incarnation of a .DLL file, not just a version. That is, if you remove a .DLL file and then put it back in the same folder, its incarnation is not the same. Something to do with NTFS internal structures. Anyway, this is enough to break a reference.