error when compacting backend database

[leeroi]

Our agency uses a MS Access 2000 database which is split into a front/back end configuration. The main data tables are on the backend is a shared network folder. Each user has a front end copy on their local machine. The problem deals with compacting the back end database.

When I open the backend database directly, and click "compact", the database compacts. At the end of this, I get an error message which says "Unrecognized database format "s:\databasename_be.mdb.

I click OK and get a prompt stating that the database needs to be repaired or isn't a database file. I click "repair", which is successful, and the database opens.

Why am I having these problems? I would like to set up both front end and back end databases to compact on closing, but with these problems I can't do that with the back end component.

Lee

 

[Junior1544]

it's usualy not a good idea to have a database automaticly compact on close... the reason for that is exacly what you are finding... any time a user exit's it, it will compact, and if another user is in it that can cause problems... when you click on the compact button, is that through them tools menu, or a command button?? also, is there any users in the database while you do this??

--Junior
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[ninfan]

I had the same problem recently. Unfortunately, in my case the MDB could not be repaired and I had to rebuild it. My research into this said that it is risky to compact an MDB that is on a network drive. If there are any performance problems on the network, corruption can occur.

The solution seems to be to copy the MDB to a local drive, compact it, then put it back on the network. I know... I don't like that much either.

Troy

 

[Junior1544]

one solution tho, is being that it's on a server, schedule a batch file or a script to compact it automaticly at night... around 3 am i would think... but a good time when no one is in the database... then it compact's it automaticly... just make sure to thoughly test this befor automating it...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Vidar13]

If you have Access installed on the server, have you tried compacting it locally? I would think they are right in saying that compacting over a network is risky.

This is probably why a lot of people switch to back orifice when it comes to back-ending an Access database, so that network traffic isn't involved in such mission critical functions as compacting a database.

I would say that you need to find a way to compact it locally or at a minimum, make sure it's when no one could possibly be in.

 

[leeroi]

I tried saving the database file under a different name (in the same shared folder), and compacting that one. Clearly, no one was accessing it since I had just created it, but I still had the same problem.

Saving it to my local hard drive, compacting it and then saving it over the original back end file may be the answer, but what a pain! I'll have to pass on the batch file idea since that is outside my skill level.

Lee

 

[Junior1544]

well, i would be happy to help you with making a scheduling batch file to do what you need to... email me at
junior1544@yahoo.com
--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.