Compact/Repair not working on network

edster

Registered User.
Local time
Today, 23:37
Joined
Sep 25, 2004
Messages
31
Hi
I've just up loaded a database onto a company server to allow all employees to use it. The problem is that now if i try to compact it a warning comes up about not being able to find a file or something (i forgot to write down what it said!!) I seem to remembe reading somewhere that this was due to it being on a multiuser server. Is this correct? Does anyone know why its happening? More to the point does anyone know how to fix it?

Many thanks

Ed
 
I just bring it over to the local machine - It's a good bit faster...

kh
 
Alrite Ken,

do you mean move the database compact it then replace it on the server?
because i'm afraid this didn't work. i'm not back at the company until next week so i cant get hold of the error message and will post it then. i was hoping that someone might have come across the same problem.
cheers
 
My best guess is that someone still has it open. To verify this you can look for the exsistence of a .ldb file. Also, When I compact, it makes a new .mdb so I would have to do some file shuffling anyway...

kh
 
Ed you have discovered number one rule never ever ever run the compact on the server - shift it off - compact and copy back
 
If you are using Access in a multiuser enviroment You can set the database to compact on close in the general tab of options. the db will then compact when the last user quits it. The problem with copying and replacing the database is the potential for data loss if anyone uses the originl db between copy and paste.

Peter
 
Pat,

May I ask the reasons for not compacting on a server?

Andrew
 
Note that Pat McMaster only made 4 postings since 09/2004.

I have never had an issue with compacting a multi-usr db on a server. I only turn on the compact on close option when the db has exceeded a specific limit I set for each db. That prevents the db from compacting everytime the last user has closed the db when it really does not need it that often. Here is the code I run in my "quit" applicaiton routine...

Code:
Public vStatusBar As Variant

Public Function CompactOnClose()
On Error GoTo Err_CompactOnClose

    If FileLen(CurrentDb.Name) > 5000000 And CurrentUser <> "programmer" Then '5 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

Exit_CompactOnClose:
    Exit Function

Err_CompactOnClose:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CompactOnClose

End Function
 
Hi Ed - I concur with the others - DON'T run compact on the the server unless you are very certain no one is still in the DB. I learned the hard way - we had problems with data disappearing when I set the Compact on Close - even though it should not run if the .ldb indicates someone is still using the file. There seemed to be a gap when a user exited, still don't know exactly why. We lost lots of data! I have now done what I should have done from the start - split it into a FE (Front End) and BE (Back End) system.

Yours - Dika
 
If your db is split and you don't create temporary tables or replace the entire contents of tables in the be, the be shouldn't bloat so it shouldn't need compacting more than once or twice a month. I have never experienced data loss when compacting on the server but the process is certainly slower and will fail if the server is low on space so I avoid it when possible.

If you can plan your compact schedule, do it locally. To avoid data issues, rename the db on the server so no one can access it while you have it offline.
 

Users who are viewing this thread

Back
Top Bottom