kennedymr
asked on
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 se
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 EDB.4.0;Je t 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).Clo se
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.
DBEngine.Workspaces(0).Clo se is DAO not ADO!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I agree an error.
I only included DBEngine.Workspaces(0).Clo se , at the last moment in a lapse of brainpower.!! After 2 hours of this thing not working.
I only included DBEngine.Workspaces(0).Clo
ASKER
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
ASKER
Appreciate you help.
Did not like to give a "C" grade !!!, even under these circumstances.
I use fsys. for both copying,deleting
Will give filecopy a go, as most of the fsys. is for copying files.
regards
Did not like to give a "C" grade !!!, even under these circumstances.
I use fsys. for both copying,deleting
Will give filecopy a go, as most of the fsys. is for copying files.
regards