Compact Secured Database

[ria_arora]

Dear All,

I'm trying to compact the secured database and wrote below code but I'm getting below error

"Cannot Start Your Application" Error When You Open a Database Project That Has an Access Data Connection

We have two different databases. One database contains only code and another database contains data and this second database is password protected. And I want to compact this second database which is protected with password. Below is the code:

Public Function CompactAndRepairDB(sSource As String, _
    sDestination As String, _
    Optional sSecurity As String, _
    Optional sUser As String, _
    Optional sPassword As String, _
    Optional lDestinationVersion As Long) As Boolean
    Dim sCompactPart1   As String
    Dim sCompactPart2   As String
    Dim oJet            As JRO.JetEngine
    
    Call ConnectCnpDatabase.DisconnectCnPDatabase
    ' Put together the provider string for the source database
    'Data Source = .mdb filename
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Mode=Share Exclusive;User Id=admin;Password=;
    sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
        ";Data Source=" & sSource & _
        ";Mode=Share Exclusive" & _
        ";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[\code]
 
Thanks
Ria

 

[JamesMcS]

Is it stopping on a particular line?

 

[ria_arora]

Hi James,

Error is at below line

oJet.CompactDatabase sCompactPart1, sCompactPart2

Error: "Cannot Start Your Application" Error When You Open a Database Project That Has an Access Data Connection

Regards
Ria

 

[JamesMcS]

Just a thought, have you thought of using Shell to compact the DB from the command line? Much easier than faffing about with all that code:

http://support.microsoft.com/kb/209207

 

[ria_arora]

I have managed to compact secured and unsecured both. Here is the code:

Public Sub CompactRepairAccessDB(ByVal sDBFILE As String, _
            Optional sPASSWORD As String = "")
    Dim sDBPATH As String, sDBNAME As String, sDB As String, sDBtmp As String
    sDBNAME = sDBFILE 'extrapulate the file name
    Do While InStr(1, sDBNAME, "\") <> 0
        sDBNAME = Right(sDBNAME, Len(sDBNAME) - InStr(1, sDBNAME, "\"))
    Loop
    'get the path name only
    sDBPATH = Left(sDBFILE, Len(sDBFILE) - Len(sDBNAME))
    sDB = sDBPATH & sDBNAME
    sDBtmp = sDBPATH & "tmp_" & sDBNAME
    'Call the statement to execute compact and repair...
    If sPASSWORD <> "" Then
        Call ConnectCnpDatabase.DisconnectCnPDatabase
        Call DBEngine.CompactDatabase(sDB, sDBtmp, dbLangGeneral, , ";pwd=" & sPASSWORD)
    Else
        Call ConnectCnpDatabase.DisconnectCnPDatabase
        Call DBEngine.CompactDatabase(sDB, sDBtmp)
    End If
    DoEvents 'wait for the app to finish
    'remove the uncompressed original
    Kill sDB
    'rename the compressed file to the original to restore for other functions
    Name sDBtmp As sDB
End Sub

 

[JamesMcS]

Nice one! Glad it works now!