Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Compacting an Access2000 Database Problem.
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).Clo
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.OL
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).Clo
After all these close's, the compact still will not work, an i missing something???
==========================
Appreciate any help.
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).Clo
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.OL
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).Clo
After all these close's, the compact still will not work, an i missing something???
==========================
Appreciate any help.
Who is Participating?
I agree an error.
I only included DBEngine.Workspaces(0).Clo
I only included DBEngine.Workspaces(0).Clo
Suddenly the whole thing is working.
The only thing i did was go into Access and open the database and then go back out.
Not sure if this has anything to do with it, BUT all is OK now....Hopefully forever!!!
The only thing i did was go into Access and open the database and then go back out.
Not sure if this has anything to do with it, BUT all is OK now....Hopefully forever!!!
Well, i would like to say a couple of things:
1)Thanks for "A" grade.
2)do you use filesystem only to use in
Fsys.CopyFile OutFile, Infile, overwrite
?
If so, you could use name function or FileCopy one
3)change this:
If Len(Dir$(OutFile)) > 0 Then
Kill OutFile
End If
to this
If Dir$(OutFile)<>"" Then
Kill OutFile
End If
I think, why to use one more function where it is not necessary?
;)
Cheers
1)Thanks for "A" grade.
2)do you use filesystem only to use in
Fsys.CopyFile OutFile, Infile, overwrite
?
If so, you could use name function or FileCopy one
3)change this:
If Len(Dir$(OutFile)) > 0 Then
Kill OutFile
End If
to this
If Dir$(OutFile)<>"" Then
Kill OutFile
End If
I think, why to use one more function where it is not necessary?
;)
Cheers
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
maybe is a problem with timming, just a guess.