asked on

Ado Using MDB. Leaves .ldb file in directory.

Access2000.........

I am using ADO and need to compact the database after deleting a large number of records.

x = rs.RecordCount

If x > 0 Then
cnn.Execute "Delete From Table1"
End If

etc......

cnn.close
set cnn=nothing

Then

DBEngine.CompactDatabase Infile, OutFile, ";pwd=admin", , ";pwd=admin"

-----------
On quite a large number of occassions i am ending up the the infiles's .ldb file still on the disk, with a zero block size.
-----------

I am therefore getting an access denied error., when trying to compact.

I then have to open the infile in Access, close it. This gets rid of the .ldb file.

Would appreciate any help

n_narayanan

Use this code,

'**********************************************************
'Passing values compactDB module
'Sour_path = Source path of existing database.
'Dest_Path = Target path
'
'Note -
'Add Microsoft Jet and Replication Objects X.X library,
'where (X.X is greater than or equal to 2.1).
'***********************************************************
'Jet OLEDB:Engine Type Jet x.x Format MDB Files
'********************* ************************
' 1 JET10
' 2 JET11
' 3 JET2X
' 4 JET3X
' 5 JET4X
'**********************************************************

Option Explicit

Public Function compactDB(ByVal SOUR_path As String, _
ByVal DEST_path As String) As Boolean

On Error GoTo Err_compact
Private JRO As New JRO.JetEngine

' Source and Destination connection path
Private DB_sour As String, DB_dest As String

DoEvents
DB_sour = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& SOUR_path
DB_dest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& DEST_path & " ;Jet OLEDB:Engine Type=5"

JRO.CompactDatabase DB_sour, DB_dest

compactDB = True
Exit Function

Err_compact:
compactDB = False
MsgBox Err.Description, vbExclamation
End Function

'*************************************************
' Usage Module level or form level.
'*************************************************

' Dim source_path,Target_path as string

' source_path=App.Path & "\Nwind.MDB"
' Target_path=App.Path & "\CompactNwind.MDB"

' If not compactDB(source_path,Target_path) Then
' MsgBox "An error occurred while attempt to rename database " _
' & vbCrLf & vbCrLf & DBCP_Name, vbExclamation
' End If

Reference

http://www.freevbcode.com/ShowCode.Asp?ID=1162

Cheers

Narayanan

ASKER CERTIFIED SOLUTION

n_narayanan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

turnerrob

ASKER

Thanks for your quick comments.
I will have a look tomorrow morning, and try the code.

regards

turnerrob

ASKER

Thankyou for your advice.
The code is more complicated, but, does work better.

regards turnerrob