[RESOLVED] Access Compact & Repair + Some other ?

[BHCluster]

Firs of all thank you for taking interest, I am having a problem over the Compact & Repair in my app and the problem occurs when I hit the Compact & Repair button I get a error message saying: "7866 - Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user, or or it is not an ADP file." However during that process Access file is not missing, opened or used by someone else. This started happening when I have added the password on to the Access file.

Private Sub cmdC_R_Click()
On Error GoTo MyError
    Dim oApp As Access.Application
    Set oApp = New Access.Application
    oApp.CompactRepair "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Data\File.mdb;Persist " & _
"Security Info=False;Jet OLEDB:Database Password=pa00word", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Data\FileCR.mdb;Persist " & _
"Security Info=False;Jet OLEDB:Database Password=pa00word", False
    Kill App.Path & "\Data\File.mdb"
    Name App.Path & "\Data\FileCR.mdb" As App.Path & "\Data\File.mdb"
    MsgBox "Kompaktiranje i Popravka zavrseni!", vbOKOnly, MSGTITLE
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
    Exit Sub
MyError:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly
End Sub

Other Question refers to the delete of records in the access file, here everything works fine except when the last record in the access file is deleated it stays on screen until the application restarts next time.

If Not (ar.BOF = True Or ar.EOF = True) Then
ar.Delete 'delete the current record
If Not (ar.BOF = True Or ar.EOF = True) Then
ar.MoveNext 'move next
If ar.EOF Then ar.MoveLast
PopulateControls 'fill the controls
End If
End If

Any help is greatly appreciated!

[dee-u]

Using JRO I could back-up with the following snippet.

Private Sub Command1_Click()
    Dim objJet      As Object
    Dim strOrig     As String
    Dim strSource   As String
    Dim strDest     As String
        
    strOrig = App.Path & "\CheckUp.mdb"
    strSource = App.Path & "\CheckUpSource.mdb"
    strDest = App.Path & "\CheckUp.mdb"
    RidFile strSource 'delete
    'rename original file to source file name
    Name strOrig As strSource
    'USE LATEBINDING SO AS NOT TO TIE UP TO ONE VERSION
    Set objJet = CreateObject("JRO.JetEngine")
    'create the compacted database from the source database
    objJet.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & strSource & ";Jet OLEDB:Database Password=oiledor", _
                           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & strDest & ";Jet OLEDB:Database Password=oiledor"
    'if failed then rename source to dest (original file name)
    If FileExists(strDest) = False Then
        Name strSource As strDest
    Else 'if succeeded then delete source file
        RidFile strSource
    End If
    Set objJet = Nothing
End Sub

For your delete problem could you try out the following

If Not (ar.BOF = True Or ar.EOF = True) Then
    ar.Delete 'delete the current record
    ar.Requery
    PopulateControls 'fill the controls
End If

[BHCluster]

When I execute your above code I get this error "Sub or function not defined" for this

RidFile strSource

[dee-u]

Those are my custom functions, you could replace it with

Kill FileName

[BHCluster]

I get the same error for

FileExists

[dee-u]

You could replace it with

If LEN(DIR$(strDest))>0 THEN

[BHCluster]

Now I get the error "53 - File missing" but file is there! I mean Access file

On Error GoTo MyError
    Dim objJet      As Object
    Dim strOrig     As String
    Dim strSource   As String
    Dim strDest     As String
        
    strOrig = App.Path & "\Data\Iik.mdb"
    strSource = App.Path & "\Data\IikCR.mdb"
    strDest = App.Path & "\Data\Iik.mdb"
    Kill strSource 'delete
    'rename original file to source file name
    Name strOrig As strSource
    'USE LATEBINDING SO AS NOT TO TIE UP TO ONE VERSION
    Set objJet = CreateObject("JRO.JetEngine")
    'create the compacted database from the source database
    objJet.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & strSource & "Security Info=False;Jet OLEDB:Database Password=comma", _
                           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & strDest & "Security Info=False;Jet OLEDB:Database Password=comma"
    'if failed then rename source to dest (original file name)
    If Len(Dir$(strDest)) > 0 Then
        Name strSource As strDest
    Else 'if succeeded then delete source file
        Kill strSource
    End If
    Set objJet = Nothing
MyError:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly

[dee-u]

Could you comment the line 'On Error GoTo MyError' and specify which line raises the error?