Compact and Reapair Access db from Access?

[indydavid32]

I have the following code that compacts and repairs an access table from vb.

Public Sub MaintainDataFile(MDBfilename)
'init
Dim vJet As New JRO.JetEngine
Dim vBak As String
Dim vBackup As String
Dim vSource As String

'if bak exists, kill it
vBak = Left(MDBfilename, InStr(MDBfilename, ".")) & "bak"
If CBool(Len(Dir(vBak))) Then Kill vBak

'compact/repair & backup
vSource = "Data Source=" & MDBfilename
vBackup = "Data Source=" & vBak

vJet.CompactDatabase vSource, vBackup
Kill MDBfilename
Name vBak As MDBfilename
End Sub

When I try this code from within the Access program, I get the error message that says the database has been placed in a state by user 'Admin' that prevents it from being opened or locked.

Is there a way to use code to compact and repair an access database from within that Access program?

Thanks

[James Stanich]

Here's what I use :

'*******************************************************************************
' COMPACTJETDATABASE (SUB)
'
' DESCRIPTION:
' COMPACTS DATABASE TO TEMP FOLDER AND
' COPIES BACK TO ORIGINAL DESTINATION
'*******************************************************************************
 
Public Sub COMPACTJETDATABASE(LOCATION As String, _
    Optional BACKUPORIGINAL As Boolean = True)
    
    On Error GoTo COMPACTERR
    
    Dim STRTEMPFILE As String
    
    ' CHECK TO ENSURE DATABASE EXISTS
    
    If Len(Dir(LOCATION)) Then
        
        ' CREATE TEMPORARY FILE NAME
        
        STRTEMPFILE = "c:\temp.mdb"
        
        If Len(Dir(STRTEMPFILE)) Then Kill STRTEMPFILE
        
        ' PERFORM THE COMPRESSION
        
        DBEngine.CompactDatabase LOCATION, STRTEMPFILE
        
        ' REMOVE THE ORIGINAL DATABASE
        
        Kill LOCATION
        
        ' COPY THE TEMP COMPRESSES DATABASE
        ' BACK TO ORIGINAL LOCATION
        
        FileCopy STRTEMPFILE, LOCATION
        
        ' DELETE THE TEMP FILE
        
        Kill STRTEMPFILE
        
    Else
        
    End If
    
    Exit Sub
    
COMPACTERR:
    
    Exit Sub
    
End Sub

Don't know if it has any affect with the admin thing or not, but you are welcome to test it...

[indydavid32]

Thanks, but this is the same thing as I was doing. I got the same error.

Anyone else have any ideas? I think the problem is I can't copy the database back to it's self because I have the program open.

[cafeenman]

You're having this problem from within Access? You can't compact a database when it's open, so I don't think you can do it from a bas module within access because to run the code the database has to be open.

If you are using VB, then you have to close the database before compacting or repairing and then open it again.

[John McKernan]

Like cafeenman said, you can't use any of the code methods of compacting and repairing mdb's from within Access, since the mdb must be closed first. From within Access, you'll have to use the Access menu option

[cafeenman]

I wonder if he could write a macro that can access the menu option so he can still do it programically. I'm thinking it might fail because there will be code running and the database might not close, but it may work.

[John McKernan]

Originally posted by cafeenman
I wonder if he could write a macro that can access the menu option so he can still do it programically. I'm thinking it might fail because there will be code running and the database might not close, but it may work.

I had the same thoughts (both good and bad) but since the macro only has to access the menu option itself, it may work. Worth a try anyway.