Compacting an Access db with VB

[Ed Lampman]

Guys, I know there are tons of threads related to this issue, and I've chased several of them without getting an answer, so:

I'd like to compact an Access 2000 db from within my application, using the CompactDatabase method of the MS Jet & Replication Objects 2.x library.

When I execute the method, I'm getting this error:
"You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'COMPUTER'. Try again when the database is available."

I DON'T use bound controls. There are no open connections (I only open connections to create disconnected recordsets and then close the connection [all ADO]).

My questions are:

1. Is there any way to shut down ALL open connections that may be lurking so that the compact method can get Admin access,

2. Is the only way to handle this is by writing a separate DB Admin application that can be executed outside of my app?

Thanks in advance for any responses.

[oh1mie]

I never had any problem with this procedure

Public Sub subCombactDatabase(DbFile As String, Optional Versio As Integer = 5, Optional LocaleIdentifier As Integer = 1035)
 
    Screen.MousePointer = vbHourglass
    
    On Local Error Resume Next
        
    Dim strFile       As String
    Dim strFileBackup As String
    Dim JRO           As JRO.JetEngine
    
    strFile = DbFile
    strFileBackup = Left(strFile, Len(strFile) - 3) & "~db"
    
    Kill strFileBackup
    Name strFile As strFileBackup
    
    Set JRO = New JRO.JetEngine
    JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileBackup, _
                        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Jet OLEDB:Engine Type=" & Versio & ";Locale Identifier=" & LocaleIdentifier
    
    Kill strFileBackup
    Screen.MousePointer = vbDefault
    
End Sub

[honeybee]

I don't know any reliable way of ensuring all db connections have been closed, other than checking for all of them manually. You can call the Close method on all the Connection objects you have used, and set them to Nothing to ensure they are disconnected from the db.

.

[Ed Lampman]

Thanks for the responses.

oh1mie, that's the code I am trying to use when I get the error.