Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Access File Size explosion (2k/03)

    I have an access 2k backend and an Access03 front end. Everything has worked fine for months/years. However, yesterday we moved the backend to a server, instead of the local disk. The file yesterday morning was approx 100 meg. Today it is 4 gig! The file will not repair through access. I tried creating a new empty database to recover the tables. Again it wants to repair the database, which it is unable to complete.

    Has anyone experienced this before? Any ideas what may have caused it? Any ideas on recovering the data? Should I just write it off and lose yesterday's work product?

    Thanks in advance for your ideas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access File Size explosion (2k/03)

    You might try to import the data into Excel. Otherwise, they are lost - 4GB is much too large (the size limit for a database is 2GB).

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access File Size explosion (2k/03)

    Hans,

    Thanks for the response. Further investigation reveals that:
    a) The users are on a 10 MB switch to get to the server
    [img]/forums/images/smilies/cool.gif[/img] One user had two application/front ends open at the same time, and was accessing the same backend
    c) User had the same back end record open in both front ends
    d) User made change to record in one app then made a change to the same record in the other app
    e) when user went to save the record the second time in the second app received an error message (though not sure what message said)
    f) Default record locking is "Edited record" and "Open databases using record-level locking" is checked in the Tools:Option screen

    Is there a method in Access to prevent this? Is it something that should be handled in code?

    Thanks!

    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access File Size explosion (2k/03)

    Frankly, I don't understand why this should cause such enormous bloat - Access is a multi-user database, after all. But apparently, it got into an endless loop of updating the record.

    API: Preventing multiple instances of a database on the Access Web contains code you can use in the On Load event of the startup form of the frontend or in a macro named AutoExec. If a user tries to open the frontend a second time on the same PC, it will simply close again.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access File Size explosion (2k/03)

    Hans,

    Thanks for the link. I would like to make sure that some of my less sophisticated users do not open more than one instance of the same frontend app.

    However, this was a case where there were two different apps running on the front end that use the same back end mdb file. I thought that the record locking would prevent the user(s) from accessing the same record at the same time.

    I have had this occur a couple of other times, but the only "damage" was one record ended up being corrupted and I was able to run query to get all records before and after the corrupted record, losing only a single record. In the other instances, there was not any obvious change in the size of the backend. But the app and data both resided on the local pc. I suspect that the slow connection to the 100meg backend was somehow a factor.

    I would like to know if there is a mechanism that would prevent two users from accessing the same record concurrently. If you have any ideas or references it would be greatly appreciated.

    Thanks.

    Ken

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access File Size explosion (2k/03)

    Have you checked that the Record Locking property has actually been set to Edited Record for each form in which the users edit data? Even if it is set as default in Tools | Options... it doesn't have to be set that way for each form. [I sincerely hope that the users cannot edit data directly in a table or query]

    It record locking has been set to Edited Record, I don't understand what has happened - even if a single user opens two frontends to the same backend, as soon as (s)he starts editing a record in one of them, that records should be locked for editing in the other one.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access File Size explosion (2k/03)

    Hans,

    Thanks. This gives me more to research. I believe the forms are using queries, but will have to confirm.

    Thanks again.

    Ken

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access File Size explosion (2k/03)

    It's OK if the forms are based on queries. But I wouldn't let end users open a table or query directly - you have too little control over what they do there.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access File Size explosion (2k/03)

    Hans,

    So that it doesn't go unsaid, THANKS!

    Ken

Posting Permissions

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