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

 

[xenou]

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.

Just curious but how bad is this in actual time? With my databases on my network, this operation would be basically instantaneous. What kind of network are you talking about? LAN/WAN?

 

[BrianMH]

Just curious but how bad is this in actual time? With my databases on my network, this operation would be basically instantaneous. What kind of network are you talking about? LAN/WAN?

Corporate LAN. Yes it should be fast but it's not. Which is why I'm trying to add efficiency. These aren't dedicated sql servers but mdb backends stored on a network share. I can't really do anything about the infrastructure.

 

[AngelJ]

Sorry, not an MVP but...

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.

By 'multiple times a day' do you mean whenever the Excel form is opened, or is this locking behavior sporadic? If it happens whenever the Excel form opens, maybe there's something in the connection string causing the connection to open with exclusive access.

 

[BrianMH]

It is sporadic.

 

[AngelJ]

It is sporadic.

In that case, maybe you could have something running on the Excel workbook on a timer. It could check the status of the connection, and kill it and create a new one whenever there's a problem.

 

[xenou]

I think I would prefer to just do it the old way (open/close) - unless by slow you mean minutes instead of seconds. Access is always a very bad database to be using if you have network issues and if you are dropping connections it's a problem for Access. Getting your IT guys to fix the network problems would be a good solution (although they probably don't want to hear about this). I suppose it's a matter of preference but I don't like to have permanent connections kept open when using ADO - I would only do that as a necessity.

ADO does have batch update option but I don't have any experience with it. In principle, you save your edits locally and batch update to the database all at once. This could be hairy depending on the number of users and -- more precisely -- the possibility of conflicts. However, you could explore that option if you were committed to using ADO. Beginning Access 2007 VBA by Denise Gosnell explores an in depth case study using batch updates (I may have had the 2003 edition but I assume they are basically the same).

You could also explore using a different DB for the backend - a real server based DB. Or perhaps other forms of storage ... depending on how complex the data is.

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?

Locks are in practice usually in "blocks" called pages. You might have the option to have more precise locking - look into the advanced options. But the problem might be related to the lost connections leaving the lock file broken. Perhaps Access responds to this unknown state by "locking down" the database until the problem is fixed (not sure though).

 

[BrianMH]

You could also explore using a different DB for the backend - a real server based DB. Or perhaps other forms of storage ... depending on how complex the data is.

Yeah I'm working on that. Trying to get the right people to understand the infrastructure we need is frustrating.

Locks are in practice usually in "blocks" called pages. You might have the option to have more precise locking - look into the advanced options. But the problem might be related to the lost connections leaving the lock file broken. Perhaps Access responds to this unknown state by "locking down" the database until the problem is fixed (not sure though).

As I typed this I found something else. I knew I could control locking on recordset with optimistic locking but didn't realise on the connection creation I needed to set the locking mode to 1 for row level locking. I'm going to try this later and see how it works out.

 

[Kyle123]

I had this problem and when I used an Access db once in a similar scenario, our network was fine though. I was running into full table locks on updates and inserts, I tried both only opening the connection when required and leaving it open and all the interactions were through sql rather than recordsets. The only way I solved it was by switching to SQL server - I think I honestly just had too many people using the database and access didn't properly handle the concurrency.

 

[BrianMH]

In that case, maybe you could have something running on the Excel workbook on a timer. It could check the status of the connection, and kill it and create a new one whenever there's a problem.

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.