I use this, called from my AutoExec(), to compact a database that holds all
my temp tables:

Public Sub CompactMCardSys()
 ' Comments  : Compacts the local database file that holds all working
tables
 '             On TerminalServer, will run if no other users are logged in.
 ' Version   : 5.3.11
 '
On Error GoTo CompactMCardSys_Err
   Dim strDB As String
   Dim strDBSys As String
   Dim strDBTemp As String
   Const cMsgTitle = "Compact MCardSysCAN"
   Const cDBTempFile = "MCardSysCANTemp.mdb"

   DoCmd.Hourglass True
   Call SysCmd(acSysCmdSetStatus, "Compacting system database...")
   strDBSys = SysMDB()
   If Len(strDBSys) = 0 Then
       MsgBox "Could not determine path to MCardSys.mdb", vbInformation,
cMsgTitle
       GoTo CompactMCardSys_Exit
   End If

   ' Create path to compact destination
   strDBTemp = PathFromPathFile(strDBSys) & "\" & cDBTempFile
   If FileExists(strDBTemp) Then
       Kill strDBTemp
   End If

   ' Compact RCompSys.mdb
   Call DBEngine.CompactDatabase(strDBSys, strDBTemp)
   If FileExists(strDBSys) Then
       Kill strDBSys
   End If
   ' Rename the temp file
   Name strDBTemp As strDBSys

CompactMCardSys_Exit:
   On Error Resume Next
   DoCmd.Hourglass False
   Call SysCmd(acSysCmdClearStatus)
   DoEvents
   Exit Sub
CompactMCardSys_Err:
   Select Case Err.Number
       Case 3356
           ' database in use
       Case Else
           Call ErrorHandler(mcModuleName & ".CompactMCardSys", Err.Number)
   End Select
   Resume CompactMCardSys_Exit
End Sub

Function PathFromPathFile(ByVal pstrFullPath As String) As String
   ' Strips path from a full path
   ' In:   full path
   ' Out:  path name

   Dim intI As Integer
   Dim intSize As Integer

   intSize = Len(pstrFullPath)
   For intI = intSize To 1 Step -1
       If Mid$(pstrFullPath, intI, 1) Like "[\:]" Then
           PathFromPathFile = Left$(pstrFullPath, intI - 1)
           Exit Function
       End If
   Next intI

End Function

Patrick.

Hello All,

I am not sure if this is being posted in the correct group.  I am looking to
compact a database via code.  When I try I am told that the compact and
repair can not be accomplished via code.  Is there some way to run this by
using VBA?

Thanks,

Rob

If you're using Access 2K or newer to compact the current database, use this
code:

http://www.mvps.org/access/general/gen0041.htm

If you're using Access 97, you can't compact the current database via code,
but there are alternatives.  See this page:

http://www.mvps.org/access/modules/mdl0030.htm

Chris
Microsoft MVP

If you want to compact your backend database using code,
you can do that using DAO, you just need to be sure that you
don't have any tables open.

But if you want to compact your frontend database because
you have been creating and deleting reports, you have to use
an Access object to do that (unless you are using A97)

Since A2000, an Access Project is stored inside an object
inside a database, rather than as records inside a database.

You can compact the database using DAO, including
compacting the records and cleaning out discarded records,
but that doesn't compact the forms and reports stored in a
single record in the Project object.

DAO code for compacting your backend database looks like this:

application.DBEngine.CompactDatabase OldName, NewName

Note that this always creates a new copy of your database.
This is better than compact-in-place, because when
compact-in-place fails, you sometimes loose your database.
But having done this, you need to rename the files, (or relink
to the compacted file).

If you are running this in VB script or VB6, you have to create
a dbengine object instead of using the application.dbengine
   set dbe = createobject( ... )

(david)

Please visit www.atnas20@hotmail.com/blog
I am starting up a business and need new customers. Any help that you could
give me, I would really appreciate it.  Im not spaming anyone, so please let
me know if there are anyone you know who lives in the london area and would
be interested in the purchase of medical uniforms.

Im trying to get as many customers as i can. i need help tho. Please pass
this email on to whom ever you know who would be interested. I would really
appreciate it.

Here is a little about it:

We are a business that makes Medical Uniforms which are made for Nurse's,
Doctors, and any Medical team that there is out there like PSW's.

You can find more out about it at: www.atnas20@hotmail.com/blog

We have flexible plans to accommodate growth. All size's, styles and colors
that you can think of

We are located in London, Ontario

Thank you for your time.

Margaret