Close method not working on OleDB Connection object

[silverblatt]

I have a Windows Forms application using a Jet 4.0 local database via OleDb. When the application exits, it attempts to compact & repair the database before performing automatic backups. I'm explicitly closing the OleDb connection before attempting the compact and repair, and sometimes it works exactly as intended. But in some cases (apparently after new columns and tables have been added to the database by the application itself) the compact & repair fails with the error message "You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'ALANM720T'" (curiously, my user name is Alan, not Admin). There are no other connections or processes accessing the database in any way.

After stepping through the code, it appears that the closing of the connection is failing silently in those cases, and the database's .ldb file (which normally goes away as soon as the last connection is closed) persists. For example, the following code produces an infinite loop when executed immediately after the close:

Do While File.Exists([ldb_filespec])
Loop

Is there something I'm missing here, or some other step I need to take to ensure that the close will be effective in all cases?

            'JRO is a Jet replication object
            CloseConnection()
            JRO.CompactDatabase([existing_database_filespec], [new_filespec_for_compacted_db]) 'operation fails here after new columns/tables have been added to the database

    Public Sub CloseConnection()
        Try
            If cn IsNot Nothing Then
                If cn.State <> ConnectionState.Closed Then
                    cn.Close()
                End If
                cn.Dispose()
            End If
            cn = Nothing
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub

[Shaggy Hiker]

The first thing to do would be to see whether or not your assumption is correct. If you put a breakpoint on the first If statement in your CloseConnection method, is it hit? If so, what happens when you step forwards through that code? You should be able to see whether or not it is reaching the cn.Close line, and whether or not that works or has left you out to dry.

Nothing else is worth looking into until you know the answer to that. After all, if the line isn't reached, then the problem has nothing to do with the connection not really closing, it has to do with why the closing isn't even being attempted.

[silverblatt]

I simplified the CloseConnection Sub to remove an unneeded If statement, and then added Debug.Print() calls to track what it's doing (see below). Long story short is that the remaining If statement is being satisfied every time, but the error keeps happening. Also, I've noticed that when I move the compact & repair operation to the opening of the application's main form the error doesn't happen, so I may end up working around the problem that way. But I think the compact & repair should be done when the application is closing, and in any case I'd like to understand what is causing this so I can avoid it in the future.

    Public Sub CloseConnection()
        Try
            Debug.Print(Now)
            If cn IsNot Nothing Then
                Debug.Print("If condition satisfied.")
                cn.Close()
                cn.Dispose()
            End If
            cn = Nothing
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub

[Zvoni]

Maybe your call to close causes some internal cleanup, which isn’t done when your code reaches the c&r code.
have you thought about using the StateChange-event to check when the state has really changed to „closed“?

[Shaggy Hiker]

Don't use debug statements in place of proper debugging. Yes, it can work, but no, it is not an adequate alternative. That type of debugging is like taking snapshots of the process and trying to figure out how the process works by how the snapshots change over time. Even worse, those snapshots are not even real snapshots, as it's just some text that you put in.

To do proper debugging, set a breakpoint. When execution stops at the breakpoint, you can see everything there is to see, then step through the execution along with the program.

[jmcilhinney]

You shouldn't even have that method to begin with. If you create a connection where and when you need it with a using statement then that connection will be closed and discarded implicitly, without fail.

Using connection As New OleDbConnection(connectionString)
    'Use connection here.
End Using

If you do that each time you need a connection then you will never have an open connection hanging around. If your data access code is naive then you might have code like that in a form but you can also design a proper data access layer that way.