Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2006
    Posts
    103

    Unanswered: problem on accessing old verison db

    Hi all,

    i retrieve data from access database (2000), using excel vba (2000) in microsoft window 2000 (Professional), this works fine.

    I encounter problem when i then move the excel vba to XP window to run, means excel now at 2003, but access on the server still maintain as 2000 version. i notice my reference changes, from

    Microsoft Office 9.0 Object Liabary, Microsoft Excel 9.0 Object Liabary to
    Microsoft Office 11.0 Object Liabary, Microsoft Excel 11.0 Object Liabary, i tried to change them back to 9.0, but the system prompt me that the reference is in use, cannot be removed.

    for accessing database, i use Microsoft Data Object 2.5 Liabary. For this i check XP does has this also.

    The funniest thing happen is not accessing every table will cause error, only some, moreover, the error is running around, some times it has error accessing table A, sometimes table B, and each machine behave differently (XP machines), Win2000 works all the time.

    Please tell me what make XP fail to access 2000 access database, i thought we won't have problem to access old version of application (which from same company's product) by using new verison of OS.

    And the funniest thing is it only cause error accessing some tables, and the error running around, eg. sometimes it cause error accessing table A, but not table B, sometimes only Table B has error, and every machine (XP machine) behave differently, but Win2000 machine works all the time

    Thanks in advance.
    Last edited by mkggoh; 07-19-06 at 06:35.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Firstly, set Access on all machines with Access 2003 to run in 2000 format by default.

    Second, references only allow 1 version jump without changing the reference automatically. For example:

    App has Office 9.0 (which is Office 2000) referenced.

    User on a PC with Office 10.0 (which is Office XP) opens file. Reference remains Office 9.0 so that a user with Office 9.0 can still use it.

    Then, user on PC with Office 11.0 (which is Office 2003) opens file. Reference now becomes Office 11.0 and both previous users can no longer use the file.

    This is not exactly what you are describing, but is probavbly in the same realm of issues.

    Possible solution:
    Make the back end (data file) in the oldest version Acces in use. Heck, even Access 8.0 (Access 97) will work.

    Do the same for the front end, using the olderst Access Version and the oldest Excel version (so use Access 2000 and reference on a machine with Excel 2000). Then, distribute the front ends so that each PC has it's own copy.

    let us know how it goes ...

    tc

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by tcace
    Firstly, set Access on all machines with Access 2003 to run in 2000 format by default.

    Second, references only allow 1 version jump without changing the reference automatically. For example:

    App has Office 9.0 (which is Office 2000) referenced.

    User on a PC with Office 10.0 (which is Office XP) opens file. Reference remains Office 9.0 so that a user with Office 9.0 can still use it.

    Then, user on PC with Office 11.0 (which is Office 2003) opens file. Reference now becomes Office 11.0 and both previous users can no longer use the file.

    This is not exactly what you are describing, but is probavbly in the same realm of issues.

    Possible solution:
    Make the back end (data file) in the oldest version Acces in use. Heck, even Access 8.0 (Access 97) will work.

    Do the same for the front end, using the olderst Access Version and the oldest Excel version (so use Access 2000 and reference on a machine with Excel 2000). Then, distribute the front ends so that each PC has it's own copy.

    let us know how it goes ...

    tc
    er...sorry, how to set the XP machines to run Excel 2000 by default?

    using Access 2000 for database is a must, because i can't change the server to run 2003.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I assume when you say XP, you mean Office XP, not the operating system (the OS is irrelevant).

    Find a PC with the oldest combination of software - running Excel 2K and Access 2K. Setup the app there with all the correect references and then distribute as I said earlier.

    The folks with newer versions of Excel will end up with "custom" front ends since the PC's running Excel 2003 will rewrite the references automatically the first time the user opens it. This is not an issue, however, if they each run the front end locally.

    Access (any version after 97) can use linked tables to a seperate MDB file that is the same version or older. So, if your back end is in Access 2000, then any front end linked to it running Access 2000, XP or 2003 can read the data in the linked tables. Just make sure nobody converts the back end to a newer version!

    clear as mud? try a few rounds of a good tawny port and it will make perfect sense!

    tc

  5. #5
    Join Date
    Jun 2006
    Posts
    103
    Yes, i created my database and application (excel) on Office 2000, however, when new pc which has office 2003 (with XP OS) has problem accessing my database. this is because, the excel application i created, when running on office 2003, it will use the latest version of dll. to access database (which is 2000). If i can default the excel to run on old version of dll. then i might solve the problem, but how to make office 2003 to run old version of dll, because when it started, it used version 11 instead of 9.0, and i cant change it.

    Thanks.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    when running on office 2003, it will use the latest version of dll
    This is as expected and shouldn't be a problem. I can't think of anything off the top of my head that worked in Excel 2K, but will not work in Excel 2003.

    It is also not a problem for Access ver 9 to reference Excel ver 11. In fact, I have a client who, for various reasons, still uses Access ver 8 (that's Access 97) and it works ok with Excel ver 11 on PC's with XP.

    but how to make office 2003 to run old version of dll
    You can't. Sorry

    Something else is causing your problem, I think, and it just looks like it is Excel giving you the error. Can you remove just the Excel part of the program, compile it in the Office 2K environment and then run it on the XP machine with Office 2003 and test it out?

    tc

  7. #7
    Join Date
    Jun 2006
    Posts
    103
    Er...what do you mean by compile, my code is excel marco. When i develope it, is on win2000, office 2000, is already compile on office 2000.

    do u mean, i should develope it in office 2003? hm...then i m doing it all over again for the XP version.

  8. #8
    Join Date
    Jun 2006
    Posts
    103
    hi all,

    ok, i found that is the office 2003 that causes problem, i have one WinXP machines, running office2000, is running my program just fine.

    so any comment how to solve this? i try to post a question on microsoft and c what their reply...

    please do keep me update if any of you have any idea how to solve this.

    Thanks.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    my code is excel marco
    Access switched from prefering DAO to prefering ADO at version 9. I wonder if Excel made the change later?

    Are you able to break the code and see where it specifically stops working?

    tc

  10. #10
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by tcace
    Access switched from prefering DAO to prefering ADO at version 9. I wonder if Excel made the change later?

    Are you able to break the code and see where it specifically stops working?

    tc

    is a function which make a call to

    Command As ADODB.Command
    Command.CommandText = CommandText 'here is where i provide the SQL statement
    Command.Execute(....)

    here is where it prompt the error when i executing the SQL statement, and the weird thing is, not every time it fails, it will success accessing some tables inside the same database, and the failure accessing table will be differ from machines.

    eg. machines A might only can access table A, but machines B can only access table B, but successful to get data from table A (in the same database), my guess is the new version of dll (version 11, if i m not mistaken, this dll is for office 2003) cannot be used on office 2000 (to use access 2000 db might have a problem)

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Perhaps it would be more robust if you "pushed" the data rather than "pulling" it. Is moving the operation to Access and dumping the data into Exel an option?

    What you described does not make sense to me - I have not ever seen it happen as you spelled out. I don't think it is simply the dll because you said PC 1 can get tables A but not B while PC 2 can get B but not A. The dll would choke on the SAME tables in each case if that was causing a compatibility issue.

    I'm going to repost this as a new thread HERE to put it in a different light and get some fresh attention.

    tc

Posting Permissions

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