Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Ado Using MDB. Leaves  .ldb file  in directory.

Posted on 2002-04-29
4
Medium Priority
?
209 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
  • 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

Technology Partners: 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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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

580 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