Solved

Ado Using MDB. Leaves  .ldb file  in directory.

Posted on 2002-04-29
4
188 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 50 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now