Disconnect All Open Connections to MS Access Database

I need to perform a Compact and Repair operation on an Access database. In order to do that, all open connections to the database need to be closed. Unfortunately t开发者_如何学编程here are open connections that I can't always necessarily trace down and close properly. Is there a way to disconnect all open connections through code?

There is nothing particular about an access file say as compared to a text file, or a simple power point file sitting on the disk. We are talking about a plane jane windows file here.

So, your real question is can you disconnect a process that has a word file open or any old file that just happens to be sitting on the hard drive?

You might be able to kill the process and connection, but then again you would not be able to ensure that pending data writes to that word file (or access mdb file) would occur.

So, to my knowledge, even if you could determine what process has that power-point file open, I don't think you can reliable kill that process and close that connect to the file that is open and be sure that pending data to be written will in fact get written. You can't know what that code and process has pending in terms of updates waiting to be written to disk.

While failure write out pending data in a power point file might not be such a big deal, but in the case of data, you asking for real trouble.

The only real approach here is a graceful and proper shut down of the application/process that has that windows file open.

About all your code can attempt is to gain exclusive use of the file, and if you can't do that, then you have to inform the user that a compact is not possible until such time as exclusive use of the file can be obtained.

You can certainly open up the admin tools on the computer, and check the users who have the file open, but killing those user connections is done at your own risk.

This may be of interest: http://www.tek-tips.com/viewthread.cfm?qid=79802

I know this is a late answer, but here I go...

Although determining if the data has been saved is another concern altogether, if that is not an issue, there are several options available depending on your setup. Assuming the MS Access database and you are on a windows network, and you know the computer name(s) of the user's logged in, this may be one option:

Public Function StopProcess(PC As String) As Boolean

  On Error GoTo Errhandler

    Dim strComputer As String
    Dim objWMIService As Variant
    Dim colProcessList As Variant
    Dim objProcess As Variant

    ' Add the user domain
    strComputer = PC & ".yourdomain.com"
    Set objWMIService = GetObject(_
      "winmgmts:{impersonationLevel=impersonate}!\\" & _
      strComputer & "\root\cimv2")
    Set colProcessList = objWMIService.ExecQuery(_
      "SELECT * FROM Win32_Process WHERE Name = 'msaccess.exe'")

    ' Note: authority to do this must be provided prior for this function working
    ' Also, note this kills all open MS Access database the user has open
    For Each objProcess In colProcessList
        Call objProcess.Terminate
    Next

Errhandler:
    On Error Resume Next
End Function

And then to see which users are still logged in you could query the JET database by doing something like the following procedure outlined by Microsoft in a KB:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & Path

Set rs = cn.OpenSchema(adSchemaProviderSpecific, , _
                       "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
If not rs.EOF Then
     ' Note: Fields 0 = COMPUTER_NAME, 1 = LOGIN_NAME, _
     '              2 = CONNECTED, 3 = SUSPECT_STATE
     Debug.Print rs.Fields(0) & " - " & _
                 rs.Fields(1) & " - " & _
                 rs.Fields(2) & " - " & _
                 rs.Fields(3)
End If

I hope you find this helpful.