How to compact a database using macro or vba?

I have a DB tht loads a form when opened. I want to run a compact DB every
time I open and close the DB.

Is there a away to accomplis that using macro?

Hi Rodrigo

You don't need a macro.

Use
Tools
Options
General
Compact on Close.

It would be pointless compacting on open in this case - as the DB has
already been compacted last time it was closed.

--
Wayne
Manchester, England.



"Rodrigo Borges" wrote:

I have a DB tht loads a form when opened. I want to run a compact DB every
time I open and close the DB.

Is there a away to accomplis that using macro?

The question as I understood it asked HOW to compact and repair in Access
2007 not should you!

I am a Developer and would regularly compact the database in 2000-2003
format since I move alot of data around.

QUESTION: How does one compact and repair a database with a macro or vba

hi Mick,

Mick wrote:
The question as I understood it asked HOW to compact and repair in Access
2007 not should you!
???

I am a Developer and would regularly compact the database in 2000-2003
format since I move alot of data around.

QUESTION: How does one compact and repair a database with a macro or vba
GIYF:

http://support.microsoft.com/kb/230501
http://msdn.microsoft.com/en-us/library/bb237197.aspx



mfG
-- stefan --

Request how to add Compact and Repair action to Macro.
Trying to create Macro that deletes many old and large tables, then runs
Compact and Repair, then imports new tables through ODBC.
Currently:
Step 1: Delete old tables.
Step 2: Run the compact and repair from the tools menu
Step 3: Import.
Intent is to combine all three steps above into single button, one click
macro.
Thankx

"Wayne-I-M" wrote:

Hi Rodrigo

You don't need a macro.

Use
Tools
Options
General
Compact on Close.

It would be pointless compacting on open in this case - as the DB has
already been compacted last time it was closed.

--
Wayne
Manchester, England.



"Rodrigo Borges" wrote:

I have a DB tht loads a form when opened. I want to run a compact DB every
time I open and close the DB.

Is there a away to accomplis that using macro?

This VBA function compacts a db:
Public Function pjsCompactDatabase( _
dbDataBase As DAO.Database _
) As Boolean
'Comments : Closes the database and compacts it
Dim strDBFileName As String, strDBFileNameTemp As String

'Get current database name and create name to compact into
strDBFileName = dbDataBase.Name
strDBFileNameTemp = strDBFileName & ".tempCompactNewData.mdb"

'Close the existing database and flush all writes to disk
dbDataBase.Close
Set dbDataBase = Nothing
DBEngine.Idle dbRefreshCache
DoEvents

'Compact the database to a new, temporary file name.
DBEngine.CompactDatabase srcName:=strDBFileName,
dstName:=strDBFileNameTemp
DBEngine.Idle dbRefreshCache
DoEvents

'Delete the existing db and rename the temp file to the existing name.
Kill strDBFileName
Name strDBFileNameTemp As strDBFileName

'Indicate success
pjsCompactDatabase = True
End Function

"Maj_USA_Ret" wrote in message
...
Request how to add Compact and Repair action to Macro.
Trying to create Macro that deletes many old and large tables, then runs
Compact and Repair, then imports new tables through ODBC.
Currently:
Step 1: Delete old tables.
Step 2: Run the compact and repair from the tools menu
Step 3: Import.
Intent is to combine all three steps above into single button, one click
macro.
Thankx

"Wayne-I-M" wrote:

Hi Rodrigo

You don't need a macro.

Use
Tools
Options
General
Compact on Close.

It would be pointless compacting on open in this case - as the DB has
already been compacted last time it was closed.

--
Wayne
Manchester, England.



"Rodrigo Borges" wrote:

I have a DB tht loads a form when opened. I want to run a compact DB
every
time I open and close the DB.

Is there a away to accomplis that using macro?