Solved

Problem closing database

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

726 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