Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Location
    Melbourne
    Posts
    8

    Unanswered: Access shuts down when running query on SQL table

    I am running Access 97 databases on a Win 2000 Server
    using linked tables from an SQL server. When I run an
    update query using a VBA function to update records in a
    linked SQL table, Access closes by itself without any
    error or warning. If I run the same query on a local
    table, it works fine. I can open the linked table up and
    edit records manually, and I can edit records using update
    queries that dont use VBA functions without a problem.

    The odd thing is I have two other servers running the same
    databases without error. The main difference between the
    three servers is the first two that do work were installed
    as Windows 2000 Server SP2 and upgraded to SP3 including
    hotfixes. The third server was installed as Windows 2000
    Server SP3.

    This problem has me at a stand still and I have 40 staff
    waiting to move out of the head office but can't until
    this server is up.

    I would appreciate any help I can get.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A strange case. Have you tried relinking your tables, refreshing links, compacting and repairing your database, and recompiling your code?

    Is your Access database located on a network server or is it on the local machine? Access is not very robust when run from a network server, and you may find that you don't get the error if you run a local copy of the database.

    FYI, move the file to a local machine before compacting and repairing it. The compact/repair process runs extremely slowly for moderate to large databases over a network.

    blindman

  3. #3
    Join Date
    Jul 2003
    Location
    Melbourne
    Posts
    8

    Access shuts down when running query on SQL table

    Originally posted by blindman
    A strange case. Have you tried relinking your tables, refreshing links, compacting and repairing your database, and recompiling your code?

    Is your Access database located on a network server or is it on the local machine? Access is not very robust when run from a network server, and you may find that you don't get the error if you run a local copy of the database.

    FYI, move the file to a local machine before compacting and repairing it. The compact/repair process runs extremely slowly for moderate to large databases over a network.

    blindman
    Appreciate the feedback, however, I have built a new database with new table, new query and new VBA code and the problem still occured.

    The databases are run on local machines and runs fine, but they are also run on two other network servers (we are running terminal server) and they run fine there as well. It is only on the newest machine that the problem occurs.

    The odd thing is I don't even get an error. It just closes.

    Paul

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Just grasping at straws here....

    Could it be the ODBC driver on the one machine is a different version,
    or maybe configured differently?

  5. #5
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    I have experienced simular problems lately. I had a few linked tables which I could open to view their data. I could even run queries that modified the data. But I couldn't delete the tables anymore! Plus I experienced a number of other very weird errors, like, indeed, Access quiting suddenly.

    I found that the Connection String of these linked tables were pretty large (+140 characters). These Connection Strings need to be smaller than 128 characters, as far as I know. Moving the origin of the linked tables (another MDB file) to another location and relinking the tables solved my problems. You might want to look into the Connection String of your linked tables too.
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  6. #6
    Join Date
    Jul 2003
    Location
    Melbourne
    Posts
    8

    Connection string length

    Originally posted by JeroenNL
    I have experienced simular problems lately. I had a few linked tables which I could open to view their data. I could even run queries that modified the data. But I couldn't delete the tables anymore! Plus I experienced a number of other very weird errors, like, indeed, Access quiting suddenly.

    I found that the Connection String of these linked tables were pretty large (+140 characters). These Connection Strings need to be smaller than 128 characters, as far as I know. Moving the origin of the linked tables (another MDB file) to another location and relinking the tables solved my problems. You might want to look into the Connection String of your linked tables too.
    The connection strings are indeed longer than 128 chrs, however this does not explain why the same database works on another of the servers and on local machines.

    I would like to try and reduce the string length just to try it, but as they are linked SQL tables, how do I reduce the length of the connection string?

    Paul

  7. #7
    Join Date
    Jul 2003
    Location
    Melbourne
    Posts
    8

    SQL Driver

    Originally posted by RedNeckGeek
    Just grasping at straws here....

    Could it be the ODBC driver on the one machine is a different version,
    or maybe configured differently?

    RedNeckGeek,

    The ODBC driver is indeed different, in that it is a later version. I tried to overwrite the driver with the same one as on the other machines, but it wont let me. I just get errors relating to dependancies.

    How do I install aan older driver?

    Paul

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Dang! I was hoping you weren't going to call me on that answer!

    Possibly, the newer driver may not be the problem. It could be that
    it's just 2 different drivers that's causing the problem. This is the point where I would call in the IT people. I tend to dork things up if I try to mess with installing things myself.

    Sorry...
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jul 2003
    Location
    Melbourne
    Posts
    8

    Access SQL problem

    Thanks for your help guys.

    It seems no-one has a definitive answer to the problem, though I have discovered it is possible data type/integrity related, although this does not explain why all other installations of it work O.K. Maybe the current drivers are a little more finnicky with data integrity.

    I have discovered a work around, by forcing a format on the data in question eg format([field1], "dd/mm/yy") if it is a date field. This has worked so far, but I hope there are not too many of these queries in the multitude of databases we have.

    I am going to try to downgrade the drivers, or failing that, re-install windows from sp2 onwards.

    Paul

  10. #10
    Join Date
    Apr 2003
    Posts
    280
    How do you downgrade the driver? I have try this by going through the registry and erasing the file and installing it again but it didn't work. I would install back the previous one even though I installed the older version of the driver.

    Any suggestion on how to do this.

Posting Permissions

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