Compact / Repair Access 97 'Not Responding'

[cariengon]

I created the below code to compact an Access 97 database, which resides on a network server, from a linked database on my local C:drive. When I attempt to run the compact from the linked database, it seems as if the compact starts, then freezes. When I look at the Task Manager, it says that the db is Not Responding so I have to End Task.

When I open the database I'm trying to compact normally and select Tools/Database Utilities/Compact Database, the compact works fine. Is there something in my code that is causing this? Or is this a timing issue between the local drive and the network server?

I also have an issue if I try to Repair the database using the normal procedure. It ALWAYS freezes and my Task Manager says it's not responding. I am not trying this from a linked table.

My Code from the linked table:
Private Sub CompactDB()
Dim MyOrigFile, MyCompactDB

'locate ART_Backend
MyOrigFile = "\\IC2\DATA\WKGROUPS\Auto Recon Tool\ART_Backend.mdb"
'name temporary compact db name
MyCompactDB = "\\IC2\DATA\WKGROUPS\Auto Recon Tool\db1.mdb"
DBEngine.CompactDatabase MyOrigFile, MyCompactDB
Kill MyOrigFile
Name MyCompactDB As MyOrigFile
MsgBox "Compact Complete!", vbOKOnly, "Compact Complete"
Me.cmdExit.SetFocus

End Sub

Has anyone had this problem and know of a solution? The backend database has to reside on the network and because of the process involved to import and update records, there has to be a way for the user to compact the database.

At this point I wish that this company I'm working for had upgraded to Access 2000!! Uggh!

Thanks,
Carie

 

[jiqjaq]

If you're the only one who has to compact this database then this is easy. Create a shortcut file for opening your database with Access 97 and use /compact switch at the end. (See "startup command line options" in help.)

The target line has a format of:
<full filepath of msaccess.exe> <space> <full filepath of database> /<switch> /<switch>...

The msaccess filepath needs to be enclosed in quotes since it usually contains names, like Program Files which contain spaces.

Then you can compact whenever you need to by double clicking. It's always worked for me.

 

[cariengon]

Unfortunately this is something the user needs to do, as I am just the developer and after this is done, I'm out of the scene...

I find it very strange that it also works if I run the compact against a db on my hard drive (so I know the code works...) Why doesn't it work on the server??