Errors with Compacting Function

[gaRed]

I have several buttons on a form that compact different parts of a database. When clicking on them, the program starts making constant error noises and the message bar at the bottom of the browser blinks different errors so fast that you cannot read them. Upon hitting control, alt, delete, I was able to stop the blinking and the sounds and was given the error message "You can't compact the open database while running a macro or Visual Basic code". I've included the code for several of the buttons. Can anyone help?!



'------------------------------------------------------------
' CompactProdFile
'------------------------------------------------------------
Public Function CompactProdFile()
On Error GoTo CompactProdFile_Err

DoCmd.Hourglass True
DoCmd.Echo False, "Now compacting tufting database production data file"
SendKeys "c:\Tuft\prod.mdb~c:\Tuft\prod.mdb~Y", False
DoCmd.RunCommand acCmdCompactDatabase
DoCmd.Echo True, "Tufting database production data file has been compacted."
DoCmd.Hourglass False

CompactProdFile_Exit:
Exit Function

CompactProdFile_Err:
DoCmd.Echo True
DoCmd.Hourglass False
MsgBox Error$
Resume CompactProdFile_Exit

End Function


'------------------------------------------------------------
' CompactTables
'------------------------------------------------------------
Public Function CompactTables()
On Error GoTo CompactTables_Err

DoCmd.Hourglass True
DoCmd.Echo False, "Now compacting tufting database lookup tables file"
SendKeys "c:\Tuft\tables1.mdb~c:\Tuft\tables1.mdb~Y", False
DoCmd.RunCommand acCmdCompactDatabase
DoCmd.Echo True, "Tufting database Support data file has been compacted."
DoCmd.Echo False, "Now compacting tufting database employees file"
SendKeys "c:\Tuft\emply.mdb~c:\Tuft\emply.mdb~Y", False
DoCmd.RunCommand acCmdCompactDatabase
DoCmd.Echo True, "Tufting database employee data file has been compacted."
DoCmd.Hourglass False

CompactTables_Exit:
Exit Function

CompactTables_Err:
DoCmd.Echo True
DoCmd.Hourglass False
MsgBox Error$
Resume CompactTables_Exit

End Function

 

[VicM]

gaRed:

As the error message indicates, you can not compact a database from a sub or macro. Since that part of the code is running, if a compact were to take place the sub/macro code would be repositioned in memory and it would lose its pointers to the next executable instruction.

I'm also not aware that you can compact individual tables, etc. If you've found some documentation indicating such, I'd be happy to see it.

The only way of compacting a database automatically that I'm aware of is to select the 'Compact on Close' option on the 'General' tab in the Database Options window.

Hope this helps

Vic

 

[gaRed]

The mdb files I am trying to compact are out on the network, and are not a part of the application that is trying to do this. I have corrected the path names (previously they were pointing to the c: drive rather than the network) but I continue to get that error message. My boss said that she knows this worked when the db was in access 97 format, but does not know if it has worked since it was converted to 2000. Any ideas now that I've presented the problem a bit more clearly?
Thanks!