Compact Database from External Database 2007

[Ross]

Hi All,

I have three large external databases that I can open from my current
database. I can run delete queries to empty the 3 external databses and
after these queies run, I want to programatically compact each one before
closing.

I don't want to use the on close option in the external database setup
options because when the database is full and I close it, it takes forever to
compact.

How can I compact and close an exteranl database from the current database?

Thank you

Ross

[Douglas J. Steele]

The On Close option wouldn't do you any good anyhow: it only applies to the
front-end database.

Use the CompactDatabase method of the DBEngine object.
http://msdn.microsoft.com/en-us/library/bb220986.aspx

Note that there can be no connections to the database you're trying to
compact.

My normal advice is to rename the database and compact the renamed database
to the "proper" name.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Ross" <Ro...@discussions.microsoft.com> wrote in message
news:E3DCAFF1-35CC-4D9B...@microsoft.com...

[Ross]

Douglas,

Douglas,
Correct the "on close" did not works from outside of the datase. I tired it.

Thank you!

Ross

"Douglas J. Steele" wrote:

> .
>

[Ross]

This code Works for 2007

'***************************
Public Sub CompactExtClosedDB()
'***************************
Dim strDbNameOld As String
Dim strDBNameNew As String

strDbNameOld = "C:\Documents and Settings\rcri007\My
Documents\A_Pricing_Combine\CompactDB1.accdb"
strDBNameNew = "C:\Documents and Settings\rcri007\My
Documents\A_Pricing_Combine\Ross.accdb"

DBEngine.CompactDatabase strDbNameOld, strDBNameNew
Kill strDbNameOld
Name strDBNameNew As strDbNameOld

End Sub

"Douglas J. Steele" wrote:

> .
>

[Douglas J. Steele]

I'd advise putting in a check that the database isn't in use.

'***************************
Public Sub CompactExtClosedDB()
'***************************

Dim strLockingFile As String

Dim strDbNameOld As String
Dim strDBNameNew As String

strLockingFile = "C:\Documents and Settings\rcri007\My
Documents\A_Pricing_Combine\CompactDB1.laccdb"

strDbNameOld = "C:\Documents and Settings\rcri007\My
Documents\A_Pricing_Combine\CompactDB1.accdb"
strDBNameNew = "C:\Documents and Settings\rcri007\My
Documents\A_Pricing_Combine\Ross.accdb"

If Len(Dir(strLockingFile)) > 0 Then
MsgBox "Someone's using " & strDBNameOld
Else
If Len(Dir(strDBNameNew)) > 0 Then
Kill strDBNameNew
End If

If Len(Dir(strDBNameOld)) > 0 Then

DBEngine.CompactDatabase strDbNameOld, strDBNameNew
Kill strDbNameOld
Name strDBNameNew As strDbNameOld

Else
MsgBox strDBNameOld & " does not exist."
End If
End If

End Sub

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Ross" <Ro...@discussions.microsoft.com> wrote in message

news:62BF0A3E-B487-4FBF...@microsoft.com...

[gcomyn]

If you are completely emptying the backend database, then what I do is I have
a
empty template database (a database that has the exact tables as the backend,
but is never used), and I delete and copy the template to be the correct name
before using it.

That way, the backend database is always "new" when I use it, and the
front end doesn't get bloated.

Gcomyn