[RESOLVED] Access database compact/repair

This should be a simple problem to answer. I wish to carry out a compact and repair process on several Access databases being used in a program.

From the Forum FAQs I obtained the following straightforward code, from a tutorial written by RobDog888.

However when I try to get it to run, I receive an error message that the method is not found. The highlighted error is the oApp.CompactRepair instruction at the start of that code line.

I have called up reference to Microsoft Access 9.0 Object Library.

Any suggestions would be appreciated please.

camoore

Wales, UK

Code:

---

'Add a reference to MS Access xx.0 Object Library
Private Sub Command1_Click()

    On Error GoTo MyError

    Dim oApp As Access.Application

    Set oApp = New Access.Application
    oApp.CompactRepair "C:\RobDog888.mdb", "C:\RobDog888CRd.mdb", False
    Kill "C:\RobDog888.mdb"
    Name "C:\RobDog888CRd.mdb" As "C:\RobDog888.mdb"
    MsgBox "Compact & Repair Complete!", vbOKOnly
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
    Exit Sub

MyError:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly
End Sub

---

That method might be specific to particular versions, but I don't know what method the other versions provide instead.

An alternative way to do it (using JRO) is here:
http://www.vbforums.com/showthread.php?t=324348

That jro method looks complicated, and seems to introduce new references which in turn may introduce a need for more dep. files. This will complicate distribution of the program, probably requiring a full re-install.

I would like to get the RobDog888 method working if at all possible.

Thank you for correcting my code tags. Could you please remind me about the correct format of code tags in forum posts? Regret I have forgotten this.

camoore

Wales, UK

It isn't complicated, because you can simply copy and paste the function into your project, then use a single line to call it. It does use two references, but I would expect that you already have ADO, and JRO is something that is free to distribute (and I think pre-installed), unlike Access.

For code tags...You can either use the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by put them in manually like this: [code] code here [/code]

Si, Thank you for your reply.

I wanted to try to avoid the JRO method, and in any case when I tried it I got numerous complex run time errors from it.

Having spent many hours on the problem, and having researched several sources, I have come up with the following code, which is at last working for me. I have inserted several comments, which I hope will make it understandable to others.

This code is designed to compact an Access file C:\RP\ABCDE.mdb and to place the compacted version back into the same place.

Note that I have found that the compacting process can alter an Access file, especially with regard to the order in which its entries appear. I would advise that PRIOR to compacting, the user should make a copy of the source file somewhere else, in case the compacting proces should corrupt it.

I have written error traps whereby the program fails safe if errors in process are encountered.

camoore

Wales, UK

Code:

---

Private Sub Command1_Click()          'COMPACT ACCESS DATA


    'This routine compacts a .mdb Access file C:\RP\ABCDE\db1.mdb and places
    'the compacted result back into the same location

    'requires a project reference to Microsoft Scripting runtime
   
    'requires a project reference to MS Access 9.0 Object Library

    Dim FSys As New FileSystemObject
   
    If Dir("C:\RP\Temp1.mdb") <> "" Then Kill ("C:\RP\Temp1.mdb")
   
    'This will be a temporary file. Delete it if it already exists

    If Dir("C:\RP\Temp2.mdb") <> "" Then Kill ("C:\RP\Temp2.mdb")
   
    'This will be a temporary file. Delete it if it already exists

    On Error GoTo Line100

    FSys.CopyFile "C:\RP\ABCDE\db1.mdb", "C:\RP\Temp1.mdb"
   
    'We make a copy of the source .mdb file into C:\RP\Temp1.mdb

    Dim oApp As ACCESS.Application

    Set oApp = New ACCESS.Application

    On Error GoTo Line100

    Call DBEngine.CompactDatabase("C:\RP\Temp1.mdb", "C:\RP\Temp2.mdb", dbLangGeneral)
   
    'The .mdb file now in C:\RP\Temp1.mdb is compacted and the result
    'is placed into C:\RP\Temp2.mdb

    Set oApp = Nothing

    Kill ("C:\RP\ABCDE\db1.mdb") 'we delete the original source .mdb file

    FSys.CopyFile "C:\RP\Temp2.mdb", "C:\RP\ABCDE\db1.mdb"
   
            'and copy into its previous path the compacted file from C:\RP\Temp2.mdb
           
    If Dir("C:\RP\Temp1.mdb") <> "" Then Kill ("C:\RP\Temp1.mdb")
   
    'We delete this temporary file

    If Dir("C:\RP\Temp2.mdb") <> "" Then Kill ("C:\RP\Temp2.mdb")
   
    'We delete this second temporary file
           
Line100:


End Sub

---

This post by way of closing the thread, at least from my standpoint.

The essential code which I have found necessary to implement the RobDog888 approach was :

Dim oApp As ACCESS.Application

Set oApp = New ACCESS.Application

Call DBEngine.CompactDatabase("C:\RP\Temp1.mdb", "C:\RP\Temp2.mdb", dbLangGeneral)

That Call DBEngine statement did the trick. the Temp1.mdb database is compacted into Temp2.mdb. It is then a matter of using CopyFile (under the FSO) or similar to put the compacted database into the original path. The statement :-

oApp.CompactRepair .....................

for some reason would not work for me. I got error messages about method not recognised.

One last point, which I hope might be of use to others. At the time of compacting, the database should not be linked to any ADODC or ADODB connection. If it is then it is likely that windows will not permit its replacement by a compacted version (gives run time error 70 or 75).

I will mark this thread resolved, and thanks to si the Geek for replying.

camoore

Wales, UK

A supplementary question please, Si et al :

I have found that if in a program an Access.mdb database has been "hooked on to" by ADODC or ABODB routines then it gets somehow assigned an "in-use" property and can not be deleted in the VB software, despite nulling any ADODC/ADODB connectionstring or recordsource property involving it. I get run time error 70 at every attempt (access denied). I have tried setting the Access.mdb file attributes to 0. This seems to make no difference.

Is there please some way to bring up all the properties of a .mdb database and allow VB6 code to totally un-link it from any former code association in the program - so that it can be deleted?

Any further suggestion welcomed please. I will issue this as a new thread if so advised.


camoore

Wales, UK

In order to completely disconnect from a database you need to close all of the items (connections, recordsets, etc) that refer to it, eg:

Code:

---

objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing

---

I don't use data controls, so I'm not sure what things you need to do there.

To describe the problem (simplified) I used code to link an ADODC control to an ACCESS .mdb database. In the code I specified the ConnectionString and the RecordSource. That worked fine. Subsequently I wanted to delete (Kill) that database and replace it by a compacted version. VB6 would not let me do that. I tried every (to me) conceivable way of disconnecting that ADODC from the database, including setting its ConnectionString and RecordSource properties to "". Still I could not kill that database once the ADODC had "had a go at it". Windows gave me errors every time I tried it (mostly run time 70 or 75).

A solution I have found today which works, but which is not very elegant, is to have the program generate a "dummy .mdb database" in another folder and then program the ADODC ConnectionString and RecordSource to link to that instead. If I hook the ADODC onto that gash .mdb, it appears to detach itself completely from the original database, and I am then able to Kill that first database as desired.

Thanks again for your help Si

camoore

Wales, UK