Working with Access databases on network drive with "shaky" connection issues

[AncientTiger]

Ok, first of all, I'm a self-taught programmer, so if I'm asking questions that make some of you go "DUH!", please forgive an old man. Evidently I know some, but just not enough to overcome some issues, and am hoping y'all can help.

Here's the situation. I've got a simple logging application built in VB6 that utilizes a shared network drive that's actually located at a remote site for data storage. Each office has several terminals, and folks can run the program and see the same thing on each others screens. When the network is working, the program works GREAT. But sometimes there are network connectivity issues, and that's what's tripping me up. I'm out of ideas, and hoping that there's a simple solution out there that I just don't know about.

Here's a sample of code that I use for refreshing a list. This code is set to execute about every 5-20 seconds, depending on user setting

Dim CNN As New ADODB.Connection
Dim RS As New ADODB.Recordset
CNN.ConnectionString = "//someremoveserverpath/mydatabase.mdb"
CNN.Mode = adModeRead

CNN.Open
RS.Open "SELECT * FROM LOGGINGITEMS WHERE ARCHIVED=FALSE",CNN,1,3
IF RS.RECORDCOUNT > 0 THEN
[indent]DO UNTIL RS.EOF = TRUE
'GET ITEMS FROM THE TABLE AND UPDATE THE LISTVIEW ON THE USER'S SCREEN
RS.MOVENEXT
LOOP[/indent]
END IF
RS.CLOSE
CNN.CLOSE

I know, probably not the way a lot would do it, but it's how I learned it... Anyways, the problem I'm running into is that if the network drops off while this script is executing, sometimes the screen locks up, sometimes it just throws up strange error messages (not always the same), and I know it has to do with connectivity. What would be a good way to compensate for this? I've tried putting in error handlers for errors that occur on connection, but again, my screen just freezes up:

Dim CNN As New ADODB.Connection
Dim RS As New ADODB.Recordset
CNN.ConnectionString = "//someremoveserverpath/mydatabase.mdb"
CNN.Mode = adModeRead

ON ERROR RESUME NEXT
GOODCONNECTION=FALSE
CNN.Open
IF ERR <> 0 THEN
[indent]
ET = TIMER + 20
DO UNTIL GOODCONNECTION=TRUE OR TIMER > ET
[indent]
ERR.CLEAR
DOEVENTS
CNN.OPEN
IF ERR = 0 THEN GOODCONNECTION=TRUE ELSE ERR.CLEAR
[/indent]
LOOP
[/indent]
ELSE
[indent]GOODCONNECTION=TRUE[/indent]
END IF
IF GOODCONNECTION=FALSE THEN X = MSGBOX("Connection to the database failed"):EXIT SUB

RS.Open "SELECT * FROM LOGGINGITEMS WHERE ARCHIVED=FALSE",CNN,1,3

Any suggestions would be MUCH appreciated!!!


------------------------------------
Over 35 years of programming, and still learning every day!

 

[dgillz]

This really isn't a programming question, it is a network connectivity issue IMO.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AncientTiger]

Oh, I agree dgillz..... however, I have NO control over our networking, so I'm trying to find a way for my software to compensate. I suspect there's traps and catches that I could be using in the code that would make my program a little more dependable in a bad environment.

------------------------------------
Over 35 years of programming, and still learning every day!

 

[strongm]

Here's what Microsoft have to say about (multiple) access to a shared remote mdb: some limitations ... reliability and availability. Access was never really designed for this ... and is very poor at handling network issues - as you have discovered.