Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Dec 2004
    Posts
    35

    Unhappy Unanswered: Access 97 - 2000+ Conflict

    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
    __________________
    ----------------------------------------------
    "When nothing is sure, everything is possible."
    ~ Margaret Drabble, English Novelist
    ----------------------------------------------

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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.

    Sam

  3. #3
    Join Date
    Dec 2004
    Posts
    35
    Ok, I will give that a whirl ....

    QT = )

  4. #4
    Join Date
    Dec 2004
    Posts
    35

    Unhappy

    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.

    Anyone for takers on this one ????

    Muchly appreciated ...

    QT

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try selecting Microsoft ADO Library ver 2.5 as well, and see if you can prioritize it over the DAO.

    Sam

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Note for anybody with DAO vs ADO blues

    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
    Code:
    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:
    Code:
    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.
    Last edited by tcace; 09-28-05 at 14:43.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Dec 2004
    Posts
    35

    Exclamation

    Ok ....

    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.

    GGGRRRRRRrrrrr !!!

    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!

    ANYONE ????

    QT

  8. #8
    Join Date
    Dec 2004
    Posts
    35

    Lightbulb

    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!

    QT

  9. #9
    Join Date
    Dec 2004
    Posts
    35

    Thumbs down

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

    QT

  10. #10
    Join Date
    Dec 2004
    Posts
    35

    Arrow

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

    QT

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    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.

    good luck

  12. #12
    Join Date
    Dec 2004
    Posts
    35

    Unhappy

    tcace: It was actually funny! But painfully true!

    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 ...

    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

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I think I have to go back to basics
    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?

    tc

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    PS: make sure you have a designated driver this evening .....

  15. #15
    Join Date
    Dec 2004
    Posts
    35

    Unhappy

    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 !

    I'll let you know the outcome ....

    Cheers!
    QT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •