compact repair fails after creating db1.mdb and deleting original mdb

[beckerttech]

Compact and repair (called by code - see below) occasionally fails
after creating the db1.mdb copy and deleting the original MDB. The
error message is: Microsoft Access can't delete myDatabase.mdb after
compacting it. The compacted database has been named db1.mdb.

The original database has indeed been deleted. Fortunately db1.mdb is
there can be renamed and runs fine. It can be compacted with out
issue.

On average the database can run for several days before having this
error. Sometime the error will occur with in a few times of
restarting the application.

The major components of the database process is to every 30 minutes
24x7 download a file from SAP, write out multiple text files and then
run an external FTP program to upload all of these files.

I wrote a small database that does nothing but start up and compact
and repair itself with the same code below and it compacted itself
several thousand times over the course of a few days with out having
this error.

Thoughts?

---------------------------------------
Compact code
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

 

[Allen Browne]

What version of Access is this? If 2007, this is quite a dangerous bug:
http://allenbrowne.com/Access2007.html#Bugs
(It's the last one before the Configuration section.)
MS released a hotfix on April 16:
http://support.microsoft.com/?kbid=950812

In any version, make sure the database is not in a Windows off-line folder.

Otherwise, the issue could suggest that some process has not finished/db is
still in use by somthing.

 

[beckerttech]

Sorry - forgot to include that - thanks for the reply

Access 2003 SP2 (11.6566.8132) running on Windows XP SP2
Not in an offline folder but running on a VMWARE virtual PC - testing
compact and close independent of this process leads me to believe
VMWARE not the problem

Any suggestions for tests to make sure that all processes spawned by
the database are done - using SHELLWAIT which is supposed to make this
happen

Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret As Long
' Initialize the STARTUPINFO structure:
With start
.cb = Len(start)
If Not IsMissing(WindowStyle) Then
.dwFlags = STARTF_USESHOWWINDOW
.wShowWindow = WindowStyle
End If
End With
' Start the shelled application:
ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
' Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
ret& = CloseHandle(proc.hProcess)
End Sub