I am using the following code to compact a database.
If the mdb file has not been opened. ie. This is e 1st thing done when opening the program. It works fine!!
---
Label1 = "Compacting Databases"
Label1.Refresh
Dim Fsys As New FileSystemObject
Dim OutFile As String
Dim Infile As String
Infile = App.Path & "\" & "awol.mdb"
OutFile = App.Path & "\" & "awolx.mdb"
If Len(Dir(OutFile)) > 0 Then
Kill OutFile
End If
DBEngine.Workspaces(0).Close
DBEngine.CompactDatabase Infile, OutFile, ";pwd=admin", , ";pwd=admin"
If Len(Dir(Infile)) > 0 Then
Kill Infile
End If
Fsys.CopyFile OutFile, Infile, overwrite
------------------------------------------------
BUT...!@!@!@!@ If i have had the mdb file open under ADO, and close the connection , and then try to run the above code, i get an error You attempted to open...allready exclusivery open etc... error 3356.
If i go totally out of the program, and then come back in, and run the compact, all ok.
How do you TOTALLY !@@!!@ close a database so i can compact it.
I am opening as follows.
Dim StrDBFileName As String
Dim StrCnn As String
StrDBFileName = Trim(App.Path) & "\Awol.mdb"
StrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=admin;Data Source=" & StrDBFileName
Set cnn = New ADODB.Connection
cnn.Open StrCnn
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs.Open "wol", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rs2.Open "wolid", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
----
rs.Close
rs2Close
cnn.Close
Set cnn = Nothing
DBEngine.Workspaces(0).Close
After all these close's, the compact still will not work, an i missing something???
===================================================
Appreciate any help.
maybe is a problem with timming, just a guess.