Solved

Problem closing database

Posted on 2004-04-15
4
182 Views
Last Modified: 2010-05-02
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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
0
Comment
Question by:StephenSimpsonx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
mADOchist earned 250 total points
ID: 10833470
instead of doing a .Close on your Connection Objects set them to Nothing...
0
 
LVL 18

Assisted Solution

by:Sethi
Sethi earned 250 total points
ID: 10834712
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
 
LVL 1

Expert Comment

by:mADOchist
ID: 10839004
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
 

Author Comment

by:StephenSimpsonx
ID: 10909131
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question