[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

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
0
Charlie_Barnard
Asked:
Charlie_Barnard
  • 4
  • 4
  • 3
1 Solution
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now