Calling ADO Experts (any MVPs about?) - Issues with disconnections

[BrianMH]

Hi everyone,

I don't use ADO much but I've inherited something that is using ADO and I've had a few problems.

Our network infrastructure isn't the greatest here and some times the connection to our shared drives momentarily drops. It doesn't unmap the drives or anything but we get an error in our access front ends sometimes of (disk error). Usually just waiting a second or restarting the front end sorts this so that's not a huge deal.

The problem I'm really having is with an excel form that is used for productivity tracking. It was taking quite some time to write to a MDB on our network drive. When looking at the code every time it was going to write to the backend it was creating a connection, opening the connection, creating a recordset, opening the recordset, writing the data, closing the recordset, closing the connection and then setting the rs and cn to null.

This seemed very inefficient to me. Having to open the connection each and every time seemed like a waste so I changed it to open the connection when the workbook first opened. Then I just opened the recordset and added records as needed. This did significantly improve the speed. However now multiple times a day the database is getting locked and no users can make changes until the person who has locked it closes excel.

What I really don't understand is I was under the impression that locks were on the record level. Since no one is editing or removing records, only adding them, how is it locking the whole **** database?

If anyone has any ideas on what I can do to improve performance but fix the locking issues I would be very appreciative.

Thanks

 

[AngelJ]

Unfortunately there is no way of checking if the connection is valid until you try to do something with it. The state stays open even if I disconnect from the network.

I just tried this and I see what you mean.

I was able to reproduce this behavior, except there seemed to be an easy fix, so maybe it's a little different.

I kept an open connection to a network db in one instance of VBA, then disabled my network connection. Then, I enabled the network connection and tried connecting to the same db in a new instance of VBA. As you described, even though the network connection is fine when I try connecting the second time, I get the error:

Your network access was interrupted. To continue, close the database, and then open it again.

But, if I close the connection and open it again in the new instance of VBA, then it works fine. Does that not work for you?