Compacting A database

[gblack]

I would like to reset the AutoID fields in some of my tables on a periodical basis using the repair and compact feture within Access.

I would like to automate this process so that I don't have to manually go into the database to do it.

I know (or at least think) I cannot run a repair and compact from VBA within the database file itself, but is there a way to send a command from one Access database file to repair and compact another Access databse file?

If so, might someone have a snipet of code which does this? Or tell me how I'd go about doing this. I'm lost at how I would point one database to open and compact the other...

Thanks
G

 

[ssteinke]

Microsoft makes a free Jet Compact Utility, does this help?

Jet Compact Utility

 

[ghudson]

This is a common command line that can be executed from the Run... prompt or a Shell() command or from the target field in a custom shortcut to repair and compact a db. All of this goes on one line...

"C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE" /wrkgrp "C:\MyWorkgroup.mdw" "C:\My.mdb" /user UserName /pwd MyPassword /excl /repair /compact

 

[gblack]

Thats what i was looking for,
Thanks!

 

[ghudson]

You are welcome!

 

[tkpstock]

There is also the Application.CompactRepair command - check it out...

 

[John Nelson]

Do you guys happen to know if it would work to run the compact/repair (in access '03) command using the DoMenuItem method? What is the most recent menu version? I used acMenuVer70 and I'm not sure if that's right, because it doesn't seem to see that command.

Thanks.

 

[ghudson]

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

I run this function to only compact the db on close if it exceeds my preset amount when the db is being closed.

'Used with setting the status bar message
Public vStatusBar As Variant

Public Function CompactOnClose()
On Error GoTo Err_CompactOnClose

    If FileLen(CurrentDb.Name) > 5000000 And CurrentUser <> "programmer" Then '5 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

Exit_CompactOnClose:
    Exit Function

Err_CompactOnClose:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CompactOnClose

End Function

 

[boblarson]

Do you guys happen to know if it would work to run the compact/repair (in access '03) command using the DoMenuItem method? What is the most recent menu version? I used acMenuVer70 and I'm not sure if that's right, because it doesn't seem to see that command.

Thanks.

Do NOT use the DoMenuItem methods anymore. What you just stated is why you do not want to do that. Move to the DoCmd.RunCommand items and then you won't be limited due to version.