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
dataContact.Refresh

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
            DoEvents
            cont.DatabaseName = ""
            cont.Connect = ""
            cont.Refresh
            Wait (1)
            cont.DatabaseName = sDB
            cont.Connect = gDBConnect
            cont.Refresh ‘<<<< This line gives a disk or network error
        End If
    Next cont
Next f
Charlie_BarnardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GeoffHarperCommented:
What is the code you use to open the database for the first time?
0
Charlie_BarnardAuthor Commented:
gDBConnect = "Access 2000;" & "pwd=" & gDatabasePassword
sDB is fed whatever the path for the user is, eg: "G:\EAC_DB_V6.mdb" or "\\Server\C\EAC_DB_V6.mdb"

then

dataContact.DatabaseName = sDB
dataContact.Connect = gDBConnect
dataContact.Refresh

I don't think there's any other opening of the database other than connecting each data control like this.
0
GeoffHarperCommented:
I don't trust what "Access 2000" does as a provider; this probably won't make a difference, but I would try


gDBConnect = "Microsoft.Jet.OLEDB.4.0;" & "pwd=" & gDatabasePassword

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Charlie_BarnardAuthor Commented:
Hmm, that doesn't seem to work at all. It can't even open the DB in the first case now, it gives a "Could not find installable ISAM." error.
Does it matter that although it's an mdb file it's been worked on using Access 2007?
0
GeoffHarperCommented:
Well, I doubt you want to install MDAC just to get it working with that provider and then maybe find out it's not the problem (?).

If you could close and re-open the form which holds the data controls, I think that would release them, on the theory that the data control is just not letting go of the bad connection.  Is that not an option because you still have data on the form (or want to preserve its state for some reason)?

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbireckiCommented:
Have you tried manually stepping through the code slowly one line at a time?  Does out work if you go slow and give the contends time to act, particularly the connection process?    If that works, can you try adding a DoEvents after "cont.Connect = gDBConnect" and before "cont.Refresh".  Maybe even another Wait() (only if it helps) to give the Connect stmt time to complete.
0
GeoffHarperCommented:
Sorry to confuse.  I didn't mean to post that code with my last post!  Originally I was going to modify it to close and re-open the forms but I changed my mind.
0
kbireckiCommented:
Wouldn't editing our own posts be helpful?  Maybe a 2-24 hr grace period.  I see I made another typo.  I submitted using my phone and typed "Does out work if you go slow and give the contends time to act..."  and meant "Does it work if you go slow and give the commands time to act,..."
0
Charlie_BarnardAuthor Commented:
Timing doesn’t seem to be an issue.

Based on Geoff’s suggestions I did some more tests and found:

a) You can unload the form, reload it, check it doesn’t remember what it was connected to and then try to reconnect it and it fails.
b) You can try and connect a dataControl that was never connected before, and it fails.
c) You can reconnect the old dataControl to a different DB, but not back to the original.
After further experimentation, what I think is happening is that if ANY data control on any form was connected to the DB, it’s somehow preventing any other dataControl on any form from reconnecting.
If you unload each form that had a data control connected to the DB, then set it to Nothing, then reload it and reconnect it it seems to work OK.
Thank you!
0
Charlie_BarnardAuthor Commented:
It looks like you need to unload all forms with datacontrols linked to the lost DB before you can reload them and re-establish connections.
0
kbireckiCommented:
So I think that the controls seem to be classes that retain info until all instances are removed.  Not what I would have expected.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.