Solved

Problem closing database

Posted on 2004-04-15
4
175 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now