Compact database

[WSF]

Billy, your response suggests an option in Access97 that does not exist. You
say
"In Access 97, it did not have this option. If running standard FE/BE setup
compact on close will only compact the FE, not the shared BE database"
(commented as a comparison to Access 2000 or later?)

I do not se this option on my version of Access97??

WSF

 

[ReidarT]

I have an FE/BE application with 5 users.
I want to compress the BE-databse when the last person closes the
application
(There should be no compressing while several users have open recordsets.)
Is this possible?
regards
reidarT

 

[Andrew Smith]

I've managed to do this, but unfortuantely I don't have the code on this PC
so can't post it. The way the code works is as follows:

- The database has to be closed using a "Quit" button on the menu form (I
don't allow this form to be closed in any other way, and this effectively
stops the database being closed in any other way either).
- The code behind the "Quit" button closes all open forms and then opens a
small unbound form that has the message "Do you really want to quit?", and
"Yes" and "No" command buttons. This necessary in order to ensure there is
no connection to the back end - I found that the .ldb file is not deleted
whilst the code on the Quit button is still running, even after all forms
have been closed.
- The code on the "Yes" button checks for the presence of the .ldb file
using the Dir function. If the .ldb file is found, then the database just
closes. If one is not found then code is run to compact the back end, and a
small form is displayed that says what is happening.

 

[Billy Yao [MSFT]]

Hi Reidar,

Thank you for posting in the community. I really appreciate Andrew's assistance on your
issue!

From your description, I understand that you would like to compact the BE database when
all the connections to the BE-database are closed. This compacting action should be
performed right after the last person closes his application that connects to the database.
Have I fully understood you? If there is anything I misunderstood, please feel free to let me
know.

Based on my experience, if you're using Access 2000 or later in the standard FE/BE setup,
you can use the setting of "Compact on Close" to realize this task. After enable this settings
on both FE and BE in Tools -> Options -> General Pan -> Compact on Close, the
compacting operation ONLY occurs when the "last" users exits the database.

In Access 97, it did not have this option. If running standard FE/BE setup compact on close
will only compact the FE, not the shared BE database.

Reidar, does this answer your question? Please feel free to let me know if this help solves
your problem. If there is anything more I can do to assist you, please feel free to post it in
the group.

Best regards,

Billy Yao
Microsoft Online Support

 

[Billy Yao [MSFT]]

WSF,

Thank you for your reminder!

As I mentioned in my previous message: "In Access 97, it did not have this option.". Access 97
doesn't introduce this function so that you can not see it in the Options Settings.

The following comments are really based on the scenarios of Access 97 BE database and
Access 2000 (or later) FE:
"If running standard FE/BE setup compact on close will only compact the FE, not the shared
BE database"

Thanks again for the clarification!

Regards,
Billy Yao
Microsoft Online Support

 

[Andrew Smith]

Billy,

I was very interested to read your message as it had taken me quite some
time to come up with a way of compacting the back end database
automatically!

However, I've tested your suggestion and it does not seem to work. To do the
test I did the following:

- In the back end of a shared database I imported a load of forms and tables
from other databases, then then deleted them. Naturally this caused an
increase in size of the back end file.
- I then opened the front end database, and opened a linked table so that a
lock file was created to the back end.
- I checked that both back end and front end databases had the "compact on
close" option checked, and then closed the back end file. Naturally it did
not compact as there was still a connection to the front end.
- I then closed the front end. The front end compacted itself, but not the
back end.
- Finally I reopened and the closed the back end file, and the compact ran
properly, and the size of the back end file shrank back to its original
size.

This is what I would have expected to happen before I read your message. Are
you sure that the back end file is supposed to compact automatically?

Andrew

 

[Billy Yao [MSFT]]

Hi Andrew,

Thank you for kind clarification and the detailed testing steps! My apologize not to specify
the detailed steps to implement that method on how to compact the BE databae.

I agree with you that if the back-end database is not manually opened, it will not be
compacted with the "Compact On Close" option unless we issue VBA code to compact the
BE database directly. In this scenario, I assumed that you and Reidar will fire a user
function/application which first checks all the connections closed and then compact the BE
database.

We don’t provide application coding, so I just described the way to realize this. Sorry for any
misunderstanding and convenience brought to you!

For detailed steps:

1. Set the "Compact On Close" on both FE and BE (set in BE for the scenario that you can
launch the instance, open the database and close it for compacting after all the users exit
the database.)

2. Use a user fired function/application which will first checks if there are no users on the BE
database:

198755 HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000
http://support.microsoft.com/?id=198755

3. On the next place, compact the database directly using ADO/OLE DB method as below:

230501 HOWTO: Compact Microsoft Access Database Through ADO
http://support.microsoft.com/?id=230501

230496 HOWTO: Compacting Microsoft Access Database Through OLE DB
http://support.microsoft.com/?id=230496


Another recommendation is to compact the database at a scheduled time instead of
everytime when the last user exits the database. This scheduled method is easier to
implement as long as you can make sure all the users exit the database at that time or
check the user on the database first.

158937 How to Compact Databases at a Scheduled Time
http://support.microsoft.com/?id=158937

Reidar and Andrew, please feel free to let me know if there is anything unclear about the
above steps. Thank you for posting in the community!

Best regards,

Billy Yao
Microsoft Online Support