Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ado Using MDB. Leaves  .ldb file  in directory.

Posted on 2002-04-29
4
Medium Priority
?
201 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:turnerrob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:n_narayanan
ID: 6979584
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
0
 
LVL 3

Accepted Solution

by:
n_narayanan earned 200 total points
ID: 6979588
This code does much better

'***************************************************************
' CompactAndRepairDB (FUNCTION)
'
' PARAMETERS:
' sSource             - Path to source database
' sDestination        - Path for destination database
' sSecurity           - Path to Jet system database if there is
'                       one
' sUser               - The username if user level security is
'                       turned on
' sPassword           - The password if user level security is
'                       turned on
' lDestinationVersion - If the destination database should
'                       not be in the latest version of Jet
'                       then use this variable to specify
'                       the version required; 1 = Jet 1.0,
'                       2 = Jet 1.1, 3 = Jet 2.x,
'                       4 = Jet 3.x, 5 = Jet 4.x etc
'
' RETURN VALUE:
' Boolean - Success indicator
'
' DESCRIPTION:
' This function uses the Microsoft Jet and Replication Objects
' Library (part of ADO) to compact and repair a Microsoft Access
' database. A project reference is required to the Microsoft Jet
' and Replication Objects Library.
'
' You may want to add error handling to this procedure, or to
' the function that calls it as the compact and repair may fail,
' for instance, if exclusive access cannot be gained to the
' source database.
'***************************************************************
Public Function CompactAndRepairDB(sSource As String, _
    sDestination As String, _
    Optional sSecurity As String, _
    Optional sUser As String = "Admin", _
    Optional sPassword As String, _
    Optional lDestinationVersion As Long) As Boolean

    Dim sCompactPart1   As String
    Dim sCompactPart2   As String
    Dim oJet            As JRO.JetEngine
   
    ' Put together the provider string for the source database
    sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
        ";Data Source=" & sSource & _
        ";User Id=" & sUser & _
        ";Password=" & sPassword
   
    ' If the database has a user-level security file, add the
    ' details
    If sSecurity <> "" Then
        sCompactPart1 = sCompactPart1 & _
            ";Jet OLEDB:System database=" & sSecurity & ";"
    End If
   
    ' Put together the provider string for the destination
    ' database
    sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
        ";Data Source=" & sDestination
   
    ' The destination database will end up in the latest version
    ' of jet, unless a specific version has been requested;
    ' 1 = Jet 1.0, 2 = Jet 1.1, 3 = Jet 2.x, 4 = Jet 3.x,
    ' 5 = Jet 4.x etc
    If lDestinationVersion <> 0 Then
        sCompactPart2 = sCompactPart2 & _
            ";Jet OLEDB:Engine Type=" & lDestinationVersion
    End If
   
    ' Compact and repair the database
    Set oJet = New JRO.JetEngine
    oJet.CompactDatabase sCompactPart1, sCompactPart2
    Set oJet = Nothing
   
    CompactAndRepairDB = True
End Function

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

0
 

Author Comment

by:turnerrob
ID: 6979767
Thanks for your quick comments.
I will have a look tomorrow morning, and try the code.

regards
0
 

Author Comment

by:turnerrob
ID: 6981942
Thankyou for your advice.
The code is more complicated, but, does work better.

regards  turnerrob
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question