I have a front end and a back end. The back-end grows substantially through
some very complicated calculations. Can I compact the back-end through VB
code while I am still in the front end as long as there are not any other
users on the system? If so, does someone have the VB code to do so? I'm using
Access 2003.
--
M. Shipp

You can use the CompactDatabase method of the DBEngine object.

Check whether or not the locking file (.ldb) exists: if it does, you can't
compact.

This is untested air-code that should work:

Dim strCurrentLDB As String
Dim strCurrentMDB As String
Dim strTempMDB As String

' strCurrentMDB is the full path to the database that needs to be
' compacted.
' strCurrentLDB is the full path to the locking file associated with
' the database that needs to be compacted
' You'll rename the database that needs to be compacted. The
' renamed file will be what's stored in strTempMDB

strCurrentMDB = "C:\MyFolder\MyData.MDB"
strCurrentLDB = "C:\MyFolder\MyData.LDB"
strTempMDB = "C:\MyFolder\MyData" & _
Format(Date(), "yyyymmdd") & ".bak"

' Check whether the locking file exists.

If Len(Dir(strCurrentLDB)) = 0 Then

' The locking file doesn't exist. Rename the database.
Name strCurrentMDB As strTempMDB

' Compact the renamed database to the "proper" database

DBEngine.CompactDatabase strTempMDB, strCurrentMDB

' It's up to you whether you want to keep the renamed copy.
' I do: it's my backup.

Else

' The locking file does exist: you can't back up

MsgBox "Cannot compact"

End If
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"SHIPP" <(E-Mail Removed)> wrote in message
news:B703240E-9255-4E49-AC98-(E-Mail Removed)...
>I have a front end and a back end. The back-end grows substantially through
> some very complicated calculations. Can I compact the back-end through VB
> code while I am still in the front end as long as there are not any other
> users on the system? If so, does someone have the VB code to do so? I'm
> using
> Access 2003.
> --
> M. Shipp