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?

Thanks

Stephen



'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'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
   cnRBT.Close
   Set catRBT = Nothing
   
   cnTemp.Close
   Set catTemp = Nothing
   
   cnCare.Close
   cnHospProf.Close
   cnMDS.Close             '2003/3/29 Jm
   
   On Error Resume Next    '2003/2/18 close facilities connection
   cnFacilities.Close
   
   Set cnFix = Nothing
   Set catFix = Nothing
   
   Exit Sub
ErrorH:
   ErrorMsg (PROC_ID)
   Resume      'in case one is not open, or won't close, others still will close
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
StephenSimpsonxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mADOchistCommented:
instead of doing a .Close on your Connection Objects set them to Nothing...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SethiCommented:
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
  DoEevnts
Next
0
mADOchistCommented:
public sub OpenIfClosed(ByRef con as adodb.connection)
dim bDone as boolean

on error goto err

bDone = false

do
      if con.state = -1 then
           bDone=True
     else
          con.close
          set con = nothing
          Do Events
     end if
loop while Done = false

exit sub

err:
    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
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.