Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    3

    Unanswered: Problem accessing large MDB file

    Hi Everyone.

    I've been having a problem with our main Access-based application. The back-end resides on a Win NT server and the front ends are on individual client machines. The back end actually consists of two MDB files. One current and another for archived purposes. Some of my users have been experiencing large delays (upwards of 20 minutes) before getting access to the larger Archive.MDB (144 mb) but they experience no delays with the DATA.MDB file (60 mb). The problem happens when first accessing the archive file. If I watch on my server, I can see that the users who are experiencing the problem show the archive file open as a Read resource, but it doesn't list the archive.ldb until after a long wait. This seems to happen whether the archive.ldb file exists upon first entering it or not. It doesn't happen all the times and is not unique to any particular workstation. Sometimes, it'll go right in even if it didn't the day before. Other times, my users experience a wait. I've tried compacting/repairing the database file, but it doesn't seem to have an effect.

    I just can't figure this one out and any help would be appreciated. If a user does let his computer sit for the 20 minutes or so before it manages to open the archive, it acts quite speedily. Also, if I have sucessfully accessed the Archive.MDB file and then login under my name on a second workstation, that workstation does not suffer the delays. I assume this is cause the server already has me down as using the archive.ldb and .mdb files.

    Regards,
    Adam

  2. #2
    Join Date
    Jun 2002
    Location
    Central New Jersey
    Posts
    69
    Is there any logical way for you to split up the archive database, maybe by year? 144MB is quite large. HTH
    "There's never enough time to do it right, but there's always enough time to do it over!"

  3. #3
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Does the server have compressed volumes? Maybe the archive.mdb rarely gets accessed letting the server decide to compress it and making it neccessary to decompress it when it's accessed again.

    Just a thought...

  4. #4
    Join Date
    Feb 2002
    Posts
    3
    Thanks for the help, guys!

    Jdbgeorge, I could do so, but it would be undesirable. As an experiment, I reduced the archive size to around 100 megabytes and it has not seemed to make a difference though I know this is rather large.

    Ariel, the NT server is not running any compressed volumes and the archive MDB file is still accessed several times a day as soon as a project is completed, the results are sent and then it is archived, but thanks for the thought.

    All this being said and on the basis that the company is doing much more business than we were in previous years, we have simply outgrown the LIMS system that we have in place in both terms of amount of raw data (by law we are required to keep analytical data for 5 years) that it has to handle & it's outdated featureset. The powers that be have decided to move the lab to a new system which will not run Access.

    It still bothers me that I do not know how to fix it though. I don't like being defeated. It may be a server performance issue, but I can not nail it down and nor does it ever seem to be straining while all this is going on.

    Thanks though guys. I'm always looking for tricks to add to my bag.

    Regards,
    Adam

  5. #5
    Join Date
    Feb 2002
    Posts
    403
    Using Access on a server the entire MDB is communicated to the front end, before data processing can commence, sort of.

    Lets say your users are accessing the Archive files for all clients sales over $10.00.

    The way Access would do this is move every single transaction from the backend to the frontend to populate the query. As your database is 144 mb, this could conceivably take a few seconds.

    However, I would think the network may be causing a bottleneck, being the speed of the machines accessing, and how many users are accessing. You could prove this by trying an archive query from one machine, being the only machine on line, and then introduce more machines and so on until the problem can be replicated.

    Strongly recommend upsizing the backend to SQL of some sort and running the queries on the backend.

Posting Permissions

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