Avatar of Charlie_Barnard

asked on 

How to Reconnect a DAO Data Control after connection has been lost

I have a VB6 front end that connects to an Access 2000 DB.
The project has a number of DAO Data Controls, some just for running SQL, some for powering Flexgrids.
As each form containing one or more of these data controls is loaded, we tell the control the DB file path and connection string, and all connect just fine and work well for a while.

sDB contains the path to the database, and gDBConnect contains the DB type and password.

dataContact.DatabaseName = sDB
dataContact.Connect = gDBConnect

They are never disconnected deliberately.

BUT, some of my clients have unstable networks, and the connection to the server is temporarily lost. This causes a "Disk or Network" error when we try to use the data control again.
So, I wrote a function to disconnect all the data controls, then reconnect them to the same DB as before.
BUT, it doesn’t work. When I try to reconnect them I get another Disk or Network error.
I can stop the program and restart it and it all works perfectly, but it is as though once the connection has been lost it cannot be reconnected without restarting the program, even if the DB server is back on line and the paths are all the same as before.

As an example, connect a DAO data control to an Access DB on a USB stick.
Check it works OK.
Pull the USB stick out of the PC, without stopping your VB program, and try to use the Data control, you get an error.
Put the stick back in, make sure you can see it all in windows explorer, same drive letter and path etc. and everything seems just fine.
Run the code below to reconnect the control. It doesn’t work.

So, what am I doing wrong?

Dim f As Form
Dim cont As Control
For Each f In Forms
    For Each cont In f.Controls
        If TypeOf cont Is Data Then
            frmError2.lblAdvice = "Reconnecting: " & cont.Name
            cont.DatabaseName = ""
            cont.Connect = ""
            Wait (1)
            cont.DatabaseName = sDB
            cont.Connect = gDBConnect
            cont.Refresh ‘<<<< This line gives a disk or network error
        End If
    Next cont
Next f
Visual Basic ClassicMicrosoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon