Compacting On Close

[ashg657]

Hi all.
I used to have a button within my application which when clicked would set
of the compact & repair procedure however, users were not using this so I
decided instead to check the box within Access settings to automatically
compact on close.
Works a treat............except.........
After monitoring a few of my user's disk drives I noticed a number of db's
being created as follows:
db1
db2
db3
db4.....and so on.....in one case......
db56!!!
Why is this? It seems to create a new db file everytime it compacts on close
and i have no idea why.
Many thanks in advance.
Ash.

 

[Dale Fye]

Were these files created before or after you started using the CompactOnClose
checkbox? Are the users all using the same copy of the database on a
networked file server, or is your database split, with each user having their
own copy on their local PC?

I strongly discourage using Compact on Close, especially in a multi-user
environment. It will frequently cause database corruption, and can wreak
havoc on your database environment.

If your frontend is structured correctly, you should almost never need to
compact on close anyway. The exception to this is when you are using a lot
of temporary tables. In this case, I would have a third mdb file
(TempTables.mdb) installed on each of my users PCs. I would create all of
the temp tables in that database, link them to each users frontend
application, and then insert and delete from those tables, rather than
creating and deleting tables from the front-end.

Dale

 

[ashg657]

Dale, many thanks for quick reply.
These files were only created AFTER i have used the CompactOnClose checkbox.
Each user has a copy of the database on their local system.
This isnt something which is required, I just thought it would be a good way
to streamline the system so that users werent required to perform the compact
& repair themselves.
Do you see that there is any other alternative? Basically, we had the
situation arise the other day where the database had blown right up, which I
had to sort out, now this should NEVER happen, so im trying to think of ways
to avoid it, I took the obvious option by using the CompactOnClose, so any
other suggestions more than welcome.....thanks.
Ash.

 

[Dale Fye]

As mentioned in my original post, if you have the front end structured
properly, you should almost never need to compact it. The only exception to
this is if you are creating and destroying temp tables on the fly. In which
case, I strongly recommend the method I mentioned in my earlier post. If you
are really sold on creating and deleting temp tables rather than inserting
and deleting records, you could run make table queries that create the new
table in a Temp.mdb file, then use the TransferDatabase method to link those
tables to your frontend, then delete them from the temp db when done. If you
use this method, you can actually create the Temp.mdb file when the front end
loads, and delete the file (kill command) every time you close the front end.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.