Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: Compact on Close

    Hi all,
    thanks to many of you for your responses on my earlier query (error3799). I *did* end up having to use external software to recover the data and I'm now re-introducing it into an earlier version of the database.

    My latest question:
    - ACC2000 file
    - file is housed on a network drive
    - file is frequently used by multiple users
    - file is password protected (one database password)

    I'm looking to try the "Compact on Close" option (Tools-->Options-->General tab).

    I'm wondering if this will have bad consequences if there are more than one user accessing the file at the same time. What if one quits before the other (as is like to happen)? Will this cause problems?

    Does anyone have other ideas for ways to schedule regular Compact/Repairs to happen other than "Compact on Close"?

    Please let me know.
    Cheers,
    Evan

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the file is used by multiple users, it should be split into a front/back end, and each user should have their own copy of the front end. In that setup, compact on close would be fine since the user has their own copy.

    Your current setup is just asking for corruption.
    Paul

  3. #3
    Join Date
    Sep 2006
    Posts
    10

    thanks

    Thanks, Paul, for a speedy reply.

    If I've split the database into front/back, do I still need to periodically open the back end file and compact it? Or will the fron end databases compact the back? How does that work?

    Evan

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Compacting the front end will not compact the back. I don't think the back end will bloat that much (but I could be wrong since I normally use SQL Server). If it needs to be compacted, I would create a little utility db that ran at night or on a weekend after everyone is gone that did it.
    Paul

  5. #5
    Join Date
    Sep 2006
    Posts
    10

    forcing only one user

    Paul,
    thanks again!

    Here's another related question.

    Without splitting the database, is there a way of restricting access to the file so that only one user can open it at a time? (i.e. force it to be opened exclusively)

    I found an option in Tools > Options > Advanced, but it didn't seem to have the desired effect.

    Ideas?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not sure, if that option doesn't do it. ALL of my apps are split as I described above, so I've never had the need to do that. I suppose you could write a utility db that checked for the ldb file, and only opened the actual db if there was no ldb file. Otherwise it would warn the user and close itself.
    Paul

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    One Instance

    Not sure if this is what you were looking for but it allows the user to open only one instance of that specific mdb application so they can't open it twice. Look at module1 and the function: winCheckMultipleInstances which you could call on opening your app. You may have to do a little tweaking on calling that function.
    Attached Files Attached Files
    Last edited by pkstormy; 09-06-06 at 20:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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