Solved: Compact and Repair

[tebule]

Good morning all,

I found a difference from 2003 to 2007 version of Access and I am hoping that someone can help. In 2007, the compact and repair functionality renames the compacted database with a new name. The processes that my team users are automated and we want the user to do a compact and repair on exit of the application. I can't find anything online about this, but is there a way to code a compact and repair to do a save instead of a save as and saving a new database as "Database1.mdb".

I have tried some suggetions from other message boards to no avail. I have imported all of my objects into a new 2007 DB from my 2003 DB and that didn't work. I am hoping someone else has come across this.
Thanks in advance.

[mattj]

This shouldn't happen unless the compact and repair is not being completed. Make sure that all the code in the db compieles without any errors, and make sure that the C& R is not being performed over a network.

HTH
Matt

[tebule]

It compiles with no errors. I have tried it both ways with the app directly on the C drive and located on a network drive and both happen. Is this a bug in 2007?

[mattj]

From a post at Experts Exchange from a user who had the same issue (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22793464.html):



So, after much time on the phone with Microsoft (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22793464.html#), the tech and I were able to determine it was a permissions problem. A weird one at that...

Specifically, if the "Full Control" box isn't checked for the user/group on the "sharing" tab for the UNC share (regardless of the ACL permissions), then this behavior occurs. Granting full control to the network share instantly solved the problem.

Of course, we don't want all users to have full control of the folder, but apparently restricting the "full control" rights has to be done at the ACL level, not the Shared-Folder level.

Who knew?

Will award the points to the only responder. :)

[tebule]

Oh my gosh, thank you soooooo much.. :cloud9: I guess I didn't put it directly on the c drive.... I sware I thought I had. I must have put it in a linked folder in my "my documents" It worked if I put it directly on the c drive. "C:\". Three beers for you..

[DarkSprout]

Or...
You could use a VBScript to compact the MDB.


Dim objShell, strCommand
On Error Resume Next
Set objShell = CreateObject("Wscript.Shell")
strCommand = Chr(34) & "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" & Chr(34) _
& Chr(32) & Chr(34) & "J:\.....PathAndFileName" & Chr(34) & " /compact"
objShell.Run(strCommand), 1, TRUE
WScript.Quit


Change: "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
To reflect the path of your local copy of MSACCESS.EXE

Save File in a TextFile as Compact.vbs then dbl-click to run.