• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Problem closing database

We have an application which has a monthly update function.  Before the processing we close the connection and do a backup of the MDB file.  We then re open the connection and do the processing.  We then close the connection again and do a database compact.  Then we open the connection again and that is the end of the process.

The procedure works most of the time, but  on some installations, we get a situation where after the connection is closed we cannot do the backup or compress.  It gives an error that the database is already opened exclusively someplace else.

The fact is it is not open someplace else.  What seems to be happening is that the connection is not really finished being closed before the backup tries to get exclusive access of the file.

This seems to happen when the database is on a network drive.

Below is our close database function.

Any Ideas as to how to make sure the database is free before we plow ahead?



'Close all connections and catalogs       2002/7/6 added

Public Sub CloseConnectionsAndCatalogs()
   Const PROC_ID As String = MODULE_NAME & ".CloseConnectionsAndCatalogs"
   On Error GoTo ErrorH
   On Error Resume Next '2003/8/7 JM
   Set catRBT = Nothing
   Set catTemp = Nothing
   cnMDS.Close             '2003/3/29 Jm
   On Error Resume Next    '2003/2/18 close facilities connection
   Set cnFix = Nothing
   Set catFix = Nothing
   Exit Sub
   ErrorMsg (PROC_ID)
   Resume      'in case one is not open, or won't close, others still will close
End Sub
  • 2
2 Solutions
instead of doing a .Close on your Connection Objects set them to Nothing...
I do the same thing and used to face this problem. Use DoEvents after closing the connection and reopening. For buying more time use DoEvents twice or thrice. Example:

For intLoop=0 to 2
public sub OpenIfClosed(ByRef con as adodb.connection)
dim bDone as boolean

on error goto err

bDone = false

      if con.state = -1 then
          set con = nothing
          Do Events
     end if
loop while Done = false

exit sub

    exit sub 'You will get this error if Object wasn't instanciated therefore it doesn't have a .State propert y because it doesn't exist !
end sub
StephenSimpsonxAuthor Commented:
I could not resolve this problem using only the suggestions given.  I ended up writing a little function which looped around and waited to see if I could open the file for lock read write access using the open statement.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now