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).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.



   
kennedymrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Richie_SimonettiConnect With a Mentor IT OperationsCommented:
So, you are melting both technologies, uh?
maybe is a problem with timming, just a guess.
0
 
Richie_SimonettiIT OperationsCommented:
DBEngine.Workspaces(0).Close  is DAO not ADO!
0
 
kennedymrAuthor Commented:
I agree an error.
I only included DBEngine.Workspaces(0).Close  , at the last moment in a lapse of brainpower.!! After 2 hours of this thing not working.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
kennedymrAuthor Commented:
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!!!


0
 
Richie_SimonettiIT OperationsCommented:
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
0
 
kennedymrAuthor Commented:
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
0
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.