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!
"When nothing is sure, everything is possible."
~ Margaret Drabble, English Novelist
Access 2000 defaults to using DAO technology, rather than ADO, which is used by '97. With your VBA editor open, click on Tools-->References. See if either ADO is selected or DAO 3.6. If both are selected, put the one that is lower in the list higher by selecting it and clicking the up arrow. If one is selected and not the other, select the other and proritize it in the same way. Experiment which one works properly. (I'm guessing on the ADO, but I'm not sure). Oh, don't forget to save your changes.
As a matter of good programming practice (and to slow the pulling out of your hair), explicitly define your Recordset objects at all times.
In Access 97, you could get away with
Dim rsDat As Recordset
because the DAO was implied. In newer version of Access, the implied is ADO, and the DAO stuff doesn't work any more if the object variable is ADO.
I work with clients who run Access 97 through XP and 2003 (I refuse to work with Access 2.0). To save myself the headache, I generally develop in 97 and then convert as needed. To solve the DAO vs. ADO problem, I explicitly define my recordsets like so:
Dim rsDat As DAO.Recordset
I very rarely have issues with forward compatibility when I do this.
PS: If any Access 2K components (even run-time) are installed, then Access 97 (even run-time) experiences all kinds of issues. Make sure the install of the new version of Office does not include any Access components if you want to retain Access 97. That being said, there is so much sharing between MS Office applications, it is not a good idea in general to have mixed Office Versions.
Sam Landy: I put MS ActiveX Data Objects 2.5 Library above DAO and now I get a data type mismatch in an earlier module when I am trying to select the quote to be re-run.
tcace: I understand ... however, originally I did not have ADO selected in the reference list.
To me it does not seem like a reference issue b'cuz it's crapping out on running an update query not code. The update query is being run from a macro.
Now one fix would be to go into the actually query design and set the Unique records property to 'No' ... HOWEVER, I have about 150 queries running throughout this macro to add the materials to the estimate.
tcace: "(and to slow the pulling out of your hair)" - hilariously NOT funny at the moment I am speeding up into gear 4 .... HELP!! haha aha ha!
I think I am going to do a MS uninstall clean up and see if I can resolve the issue that way .... have a few utilities I downloaded ... wish me luck ... otherwise any other suggestions are highly valuable at this moment as our east coast sales rep. is hot on my tail !!! aAhhhhh!
Sorry 'bout the attempt at humor. By explicitly referencing your object dims, it eliminates the issue of what order the references are in because you're telling it specifically whayt library to use.
I wouldn't start swapping around data object libraries - as you saw, it may break something else outside of hwere you are struggling. fyi: once you start fiddling with references, any sort of error can come up, even on unrelated objects that are installed correctly.
Seperate "multi-version" issues from any other issues. If you can, get a seperate workstation that is *clean* and fresh install only the version of Office your app is written in. Make sure everything works in that environment first.
I have this DB running on about 5 systems at this moment ( Win98 & XP ).
I have 2 other systems in question, now 3 since I recreated the error on a local laptop we had hangin' around.
Both systems in question were built and had either Office 2000 or Office 2003 installed by the tech. I do not like having dual versions installed ( to avoid BS ) .... so out with the new and in with the old ... and what do I have ... ERROR: Must use an updateable query.
So here is the rest of my spiel ...
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!
Troubleshooting 101: every now and then I get something that did work, that should work and no matter what I do, it doesn't work, no matter what. Eventually, I write it off as "Another Microshaft Winblows mystery" and start over ... sort of. Every time MS goes through an "Update Spree" I lose hours upon hours troubleshooting things that used to work.
Create a blank DB and import the tables in question. Then, recreate the query that isn't working. With all the other *noise* out of the way, it will either mysteriously start working, or you'll better be able to see why it isn't working.
Incidently, do you have a backup version from before you started fiddling with the references?
ok, I will do so ... create new db, transfer over my 50 tables, 300 queries, 10 macros and 15 modules. Just for kicks ... cuz ... this estimate db is update quite often, usually once to twice a week and I must be able to just put it on our FTP server for our rep to be able to update to the latest version.
I really want to know what is causing this issue ... this is crazy!
HAH AH HAHAHA ! You just made me LMAO! "PS: make sure you have a designated driver this evening ....."
A little comic relief every once in a while works wonders .... back to the grindind stone I go ... with a smirk on my face !! haha !