VB and Access

[Monguse]

Can one you point me to some sample code that will alow me to repair/pack an Access 2000 db and then delete (or rename) the old db and rename the newely compacted/repaired db using ADO?

I thought I'd seen something about it once but have not been able to find it and did not see any FAQ's about it.

Thanks "The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.com

 

[CajunCenturion]

See sfunk's thread

thread222-321197 Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[zemp]

Do a keyword search for 'jro' on this forum and you will find many threads that answer your question. Thanks and Good Luck!

zemp

 

[Schroeder]

Reference the Microsoft Jet and Replication Objects 2.6 Library

Sub CompactDBase(DBPath As String)
    Dim oJetEngine As JRO.JetEngine
    Dim strSourceConn As String, strDestConn As String
    Set oJetEngine = New JRO.JetEngine
    strSourceConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
    strDestConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Left(DBPath, InStrRev(DBPath, "\")) & "Temp.mdb;Jet OLEDB:Engine Type=5;"
    On Error Resume Next
    Kill Left(DBPath, InStrRev(DBPath, "\")) & "Temp.mdb"
    On Error GoTo 0
    oJetEngine.CompactDatabase strSourceConn, strDestConn
    FileCopy Left(DBPath, InStrRev(DBPath, "\")) & "Temp.mdb", DBPath
    Set oJetEngine = Nothing
    Err.Clear
    Exit Sub
End Sub

This will leave the temporary copy intact which I like to do so that I've got a backup. If you don't want the backup copy add:

    Kill Left(DBPath, InStrRev(DBPath, "\")) & "Temp.mdb"

after you've done the FileCopy

 

[NJLDoc]

Must have reference to Microsoft Jet And Replication Objects x.x Library
Use Engine Type = 4 for Access 97, Engine Type = 5 for Access 2000
Using F1 on .compactDatabase will also give example