Compact Question - Redirecting

[Ben]

I have a database that is at 10Mb. When I choose Compact on Close, it
creates a new db1.mdb file. I am assuming the database has hit it's limit.
However, since it is creating this new db1.mdb file, can I force the database
to move that new file to a different directory?

Right now, the main database is in the Master_Database Folder. After I
compact it, i want the new "db1.mdb" file to be saved to "Database Backups."

How can I do this?

Ben

 

[Jerry Whittle]

At only 10 mb, you are about 1,990 mb short of the limit. IOW the file size
limit is 2 gb.

The db1.mdb is being left because something is preventing the complete
compact and repair from happening. Check the Master_Database folder and make
sure that you and all users of the database have read, write, create, and
delete privileges for the entire Master_Database folder. Not just the
database but the folder also.

Also turn off Compact on Close. If your database is under 50 mb, you don't
need to compact it at all. If more than that, do a compact and repair
manually when no one else is using the database. I seldom do a C&R more than
once a month unless there is a corruption issue.

As for saving a backup, go to Tools, Database Utilities, Backup Database.
That should allow you to create a copy of the database to a different
location. By default, it should put the date in the file names so that you
can keep multiple copies in the same place.

 

[Tom Wickerath]

Hi Ben,

10 MB is well within the limits of Access. Access 97 can handle up to 1 GB
(ie. 1024 MB), and all later versions of Access can handle up to 2 GB. I
personally would not exceed 60~70% of this capacity, because doing so might
be asking for trouble.

The db1.mdb file that you see should be temporary only, if everything is
working okay. You cannot use the built-in Compact on Close feature to compact
to a new database in a different folder. However, as long as the database is
closed, you can use the CompactDatabase method from another "Helper"
application. Check out the article that former Access MVP Jeff Conrad has
posted here:

Creating A Customized Login Screen For A Secured Database
http://www.access.qbuilt.com/html/custom_login.html

How to compact the current database automatically when it
reaches a certain size.
http://www.access.qbuilt.com/html/vba1.html


Also, check out this posting from Access MVP Doug Steele:

http://groups.google.com/group/micr...hread/thread/9f386f39b995a25/acc15f9e554b6d77


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Jerry,

As for saving a backup, go to Tools, Database Utilities, Backup Database.

This, along with File | Backup Database, will only work in Access 2003. It
is not available in Access 2002 and earlier. I'm sure there is an equivalent
in Access 2007 that involves starting with the Office button thingy, but I
don't have 2007 running at the moment.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Anthos]

In Access 2007, click on the Orb, (The big Round thing) then click
manage, then Backup Database.

Hope that is of assistance,

Kind Regards
Anthony Moore

IT Excellence.

 

[Tom Wickerath]

Thanks Anthony.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________