access 2007 compact creates new database

[Michel Peeters]

I am using following code to compact my database.
this worked ok in Access 2000 but in 2007 after compacting I find a new
(compacted) database; the original database did not change.
I want my original database to compact and I do not need a new one.
How can I fix this?


Declare Function TSB_API_GetExitCodeProcess Lib "kernel32" Alias
"GetExitCodeProcess" (ByVal hProcess As Long, lpExitCode As Long) As Long
Declare Function TSB_API_OpenProcess Lib "kernel32" Alias "OpenProcess"
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal
dwProcessId As Long) As Long

Sub CompactMijnDB(theDBName As String)

Dim myString As String
Dim IsComplete As Double
Dim strDC As String
strDC = """"
Debug.Print strDC
Debug.Print myString
myString = "C:\Program Files\Microsoft Office\Office12\MSAccess.exe " &
strDC & theDBName & strDC & " /compact" & strDC
Debug.Print myString
If SysInfoWaitForApp_TSB(myString, vbNormalFocus) = True Then
'MsgBox "target compacted"
Else
Stop 'do whatever you want here..
End If
Exit Sub

End Sub

Function SysInfoWaitForApp_TSB(strCommandLine As String, intMode As Integer)
As Boolean
' Comments : runs the specified app and waits for its termination
' Parameters: strCommandLine - command line for program to execute
' intMode - mode for Shell command (search Access Help for
"Shell")
' Returns : True if successful, False otherwise
'
Dim hInstance As Long
Dim hProcess As Long
Dim lngRet As Long
Dim lngExit As Long

Const ProcessQueryInformation = &H400
Const StillActive = &H103

On Error GoTo PROC_ERR

' Launch the application
hInstance = Shell(strCommandLine, intMode)

' Get a process handle
hProcess = TSB_API_OpenProcess(ProcessQueryInformation, True, hInstance)

' Wait until the process ends
Do
lngRet = TSB_API_GetExitCodeProcess(hProcess, lngExit)

' Yield to the operating system
DoEvents
Loop Until lngExit <> StillActive

SysInfoWaitForApp_TSB = True

PROC_EXIT:
Exit Function

PROC_ERR:
SysInfoWaitForApp_TSB = False
Resume PROC_EXIT

End Function



tks
Michel

 

[Arvin Meyer [MVP]]

The process of compaction always creates a new database, writes the data and
objects to it, leaving behind any corrupted or deleted data or objects. Once
created, it deletes the current database and renames the new one. This has
been the process since the first compaction capability.

What you are seeing is a failure to delete and rename the database. That
could be because of delete permissions on the file or folder not being
allowed, or some other failure caused by your file system. If you are also
operating on Microsoft Vista, that is the most probable cause.