Compacting mdb Automatically (1 Viewer)

[LNewton]

I am compacting a database everytime it is opened with the following code: DBEngine.CompactDatabase str2Print_TDI_Path & "NewTDI_be.mdb", strTempPath
Kill str2Print_TDI_Path & "NewTDI_be.mdb"
Name str2Print_TDI_Path & "NewTDI_be1.mdb" As str2Print_TDI_Path & "NewTDI_be.mdb"

The problem I ran across is another mdb has a linked table from the mdb I am compacting. If that mdb is open the compact routine errs indicating that someone has the this database open. Is there anyway to get around this problem?

 

[Rob.Mills]

In the options menu you can choose to have it compact on close.

Save you some time on the programming.

 

[LNewton]

I do not see that option in access 97.

 

[Rob.Mills]

Don't know if it's in 97. I use 2000. In there it's on the General tab if that helps.

 

[KevinM]

It's not in A97.

Even if you had A2K you will still have problems if the db is opened by more than one user and you try to compact when closing.

 

[Autoeng]

KevinM:

If you will read the help files on compact on close you will note that it does not work if there are more than 2 people in the db.

From Access help

Note Compacting does not occur if you close a multiuser (shared) database while another user has it open.

Autoeng

 

[KevinM]

Which is more or less what I implied in my previous post

 

[Autoeng]

I apologize. I misunderstood your post. If reading it by itself it seems to be in error but when put in contezxt of the thread you are correct. Again, my apologies

What I meant was if LNewton got rid of the db OnOpen compact event he wouldn't get the errors anymore because the other db is open. More than likely he will get at least one compact per day using the compact on close.

Another thing to do is to issue a command to log users out of the other db after a set period of time from start up of the db needing compaction then after that time period fire the compact then release the hold on the other db.

Not a good method either way you go but I don't know of any other way around his problem because of the linked table.

Autoeng

 

[LNewton]

Thanks for the input. Both databases are not used by to many people at once. For now they will have to live with it.

 

[KevinM]

Compacting dbs has always been a problem on the ones i've created at work.

I've found that the only way in a multi-user environment is to do it 'out of office hours' when everyone has gone home. (ooh er matron!)

I have thought of the 'log out' method before, but you try ASKING our admin staff to kindly log out for a few mins!!

Some of them open a particular db every morning and keep it minimised ALL DAY, regardless if they are going to actually use it or not

I dread the day when they go onto A2K, which means exclusive only access for the most simplest of design changes.

 

[Autoeng]

You don't have to ask them to log out, you can kick them out! Check out www.rogersaccesslibrary.com for a example of how to kick users out of a db.

I use it when I have to make a change to the db be and it works great.

Autoeng

 

[KevinM]

Yeah i've toyed with this one before.

Think I might give it another look...time to get tougher on 'em.

Cheets Autoeng.