VB.NET, Access and Transactions

[Stephen Roberts]

Hi,

We've got a multi-user system written in VB.net. It has an Access back
end. We've written it using Business objects, and it writes changes to
the database using saved queries eg
PARAMETERS P1 Integer, P2 Integer, pId Integer;
UPDATE Table1 Set Value1 = P1, Value 2 = P2 WHERE ID = pID;

All the writes are wrapped up in a transaction but we can have many
write operations within the same transaction (sometimes a couple of
hundred).

We're doing data access using ADO.Net.

Our problem is that we seem to be getting the situation where users are
encountering locked records, locked tables etc on many more occasions
than we would hope for considering the limited number of users on the
system (<10). This seems to be happening because of the transactions
locking portions of the database. This in itself is not unexpected,
it's just the number of times that it happens is surprising.

I've been asked to ask if anyone else is successfully using VB.NET with
an access back end like this, and if there are any suggestions for how
to fix this, or if access is just not up to the job, and we should be
using SQL Server.

I am aware of the obvious solutions: minimise the duration of the
transaction by ensuring that records are written as quickly as possible,
and that nothing keeps the transaction open for any longer than it needs
to be kept open.

Any comments would be appreciated.

Regards
Stephen Roberts

 

[Homer J Simpson]

I am aware of the obvious solutions: minimise the duration of the
transaction by ensuring that records are written as quickly as possible,
and that nothing keeps the transaction open for any longer than it needs
to be kept open.

You are aware that there are (should be?) different levels of locking? Is it
possible to do read only access where updates aren't needed?

VB .Net is supposed to copy records to itself, let you do updates, and then
update in one hit - haven't played with it enough yet though.