Discussion Locked

Open ADO Connection to new DB fails.

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi there,

I have a problem that is really causing me grief. I have a VB application that opens an ADO connection to a database. When the application is first installed, the database does not exist. Normally it will. For that reason, the code to create the database file (by copying it from a template stored as an application resource) is executed in response to an error opening the original connection.

My problem is that once the new DB file has been created, ADO fails to open the file. The problem is obviously timing related as stepping through the code in the IDE works 100% of the time! Can anyone suggest a mechanism to fix this without resorting to an arbitrary delay factor or similar?

Here is the current code (with some parts snipped) m_ConData is a module level ADO Connection object:

Public Property Let DBItem(NewItem as String)
Dim sConnect As String
Dim fDBCreated As Boolean

On Error Goto ErrorHandler

'// Make sure fDBCreated is initialized.
fDBCreated = False

'// Open the connection (if it isn't)
If m_conData Is Nothing Then
sConnect = CONNECTION_STRING
Set m_conData = New Connection
With m_conData
.ConnectionString = sConnect
.Open
End With
End If

'// Functionality removed here....

ExitPoint:
Exit Property

ErrorHandler:
Select Case Err
Case &H80004005 ' ADO Error - Probably because file does not exist.
If Not fDBCreated Then
If CopyResourceToFile Then
fDBCreated = True
Resume
Else
Err.Raise vbObjectError, Description:="Unable to createor access DB."
End If
End If
End Select

Resume ExitPoint
End Property

7 replies

by hmertens

The connection you've established is open, so the error that you get is not a connection error to the db server layer, so much as a failure to find the default database to be present on the server, whereupon you create a database (.MDB/Access or .MDF/SQL?).

This connection has not been authenticated for access to the database (since it is newly created) since the connection has been opened; If you are doin' SQL here, you are creating the database from within the master/msdb.

Try closing your connection and re-opening it to insure that connection is established to the appropiate database (once is exists).

by rfoster

Good thinking! Unfortunately, I have already been down that path.

Actually, since the file (.MDB/Access) doesn't exist, the connection is never opened. (Attempting to close it generates error 3704 - Operation is not allowed when the object is closed). I tried creating a completely new connection object, however that is also unable to access the freshly created database for approximately half a second (on my machine) after the file's creation.

In addition, I made sure that write behind caching is turned off on my development machine. (I am sure that could cause problems....) Unfortunately I am equally sure that most of our users will have write behind caching turned on, which may make the problem worse.

by bill_brower

I've developed similar systems in the past where I told the system to copy a file and got a response that the file was copied before it was really finished (directory entry made, but file not completely copied). Therefore, what I do now is copy thefile and then check the existence and size of the new file to ensure that it really has been copied.

by rfoster

Bill,

Also good thinkingn however I'm afraid that's not the answer either. The function which creates the file already does exactly what you suggest. If the file does not exist, or is the wrong size, an error will be generated. No such error is occuring.

by sankulpune

Hello rfoster,

Try replacing the if clause with a while clause.

Replace:

If m_conData Is Nothing Then
sConnect = CONNECTION_STRING
Set m_conData = New Connection
With m_conData
.ConnectionString = sConnect
.Open
End With
End If

With

While m_conData Is Nothing
sConnect = CONNECTION_STRING
Set m_conData = New Connection
With m_conData
.ConnectionString = sConnect
.Open
End With
Wend

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

by shanghai sam

Hi SanganakSakha,

In the words of Homer Simpson... D'oh! Yes, since the operation seems to (eventually) succeed, trying it in a while loop would actually work, and accomplishes the desired result without using a delay. Since that solves the problem I posed, it is only fair you get the points.

A couple of comments though (these all relate to your code as written)....

1) m_ConData will not be nothing after the first iteration, even if the connection did not open. I assume you ment to check on the connection's state?

2) The code does not handle situations where there really is a problem. I would suggest that you should only try to establish the connection a certain maximum number of times before failing. As written, you would get into an infinate loop.

3) This is really only a work-around. Neither you, nor any of the others who responded were able to indicate *why* the problem is occuring. Without that knowledge, there is always the danger that the work around will fail unexpectedly in the future.

Thanks to everyone for their suggestions,

Regards,
Richard

This discussion is closed to new replies.