Compact back end database file

[Daniel Richards]

I have a front end Access 2000 dbase with links to data
tables in another Access 2000 file. I want to give the
user of the front end the ability to compact the back end
data file from the front end. Any ideas?

Thanks
Daniel

 

[Douglas J. Steele]

Make sure you have a reference set to DAO. (With any code module open,
select Tools | References from the menu bar, scroll through the list of
available references until you find the one for Microsoft DAO 3.6 Object
Library, and select it.

Determine what the backend database name is. (Since you have a reference to
DAO set, this can be easily done by checking
CurrentDb().TableDefs("SomeTable").Connect, where SomeTable is the name of a
linked table in your database). For the sake of discussion, call this
strOldFile.

Replace the .MDB from the end of strOldFile with .LDB, and check whether or
not that file exists (Len(Dir$("C:\My Documents\MyFile.ldb")) will equal 0
if the file doesn't exist)

If the LDB file exists you can't compact the database, as it's in use.

Otherwise, come up with a new file name (a good approach is to add today's
date to the MDB's name: something like strNewFile = strOldFileWithoutMDB &
Format$(Date(), "yyyy-mm-dd") & .MDB).

Rename the backend database to the new file name you just created. (Name
strOldFile As strNewFile)

You can now compact the database using DBEngine.CompactDatabase strNewFile,
strOldFile

Once you know it's successfully compacted, you can either delete strNewFile,
or you can keep it as a backup. (My advice would be the latter)

 

[Arvin Meyer]

I won't say it's impossible, but it is unlikely. What compacting does is to
rewrite the database to a new file, thus getting rid of any bloat and some
possible corruption. Once the file has been successfully rewritten, the
current file is deleted and the new one renamed. To do that the database
needs to be closed. A utility can be easily written in Access to close the
current front-end database, open a new non-connected database, then compact
the back end with code like:

DBEngine.CompactDatabase oldname, newname
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access