Connections String

  • Thread starter Thread starter Charles A. Lackman
  • Start date Start date
C

Charles A. Lackman

Hello,

I am using a Jet4.0 (Access) database as a datastore that 10 computers are
updating on a regular basis. I am using ExecuteNonQuerry to send the
updates with parameters from an unconnected state. The problem I am having
is when more than 1 person is sending an update the others attempt is not
accepted. I need all people to be able to concurrently update without the
database being locked.

Mode is set to Share Deny None

I have noticed that there is a Database Locking Mode that I have not set

Database Locking Mode=1

What does the Modes mean and will this fix the problem I am having. This
problem also happens when 2 people try to read the data from the datastore
at the same time.
Would this problem be solved if I used a different database like Dbase??

Thanks,

Chuck
 
"I need all people to be able to concurrently update without the database
being locked."

Then you really should not be using Access as your data store. Access is
not meant to be used like this. Use SQL Server instead.
 
What do you really expect to happen? Should the update from user A be
over-ridden by user B and every other user who comes afterward? This is
possible, (even in JET/Access) however, JET is much slower at this than SQL
Server. Yes, JET locks the data row, page or table while the update is
taking place and since using JET means doing physical IO over the new, it's
especially slow. SQL Server and other DBMS systems are more efficient at
handling larger loads. 10 users is not much, but if you're seeing locking,
you need to write exception handlers to deal with this. Most DBMS updates
require some sort of concurrency management scheme. Last change wins, data
owner wins, first change wins and many others. A brute-force approach is not
the most elegant, but it can work if you code it (and the exception
handlers) correctly.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
In addition to what Bill Vaughn said, you should also consider the
database schema and partition your Access tables so that users aren't
modifying the same rows at the same time. You may need to break tables
with many columns into multiple tables with 1-1 joins on the PK. For
example, if you store employee data in one huge table and the HR
department is updating an employee row at the same time as the Sales
department is, then you've got a problem. You should alway take into
account the table design when you are having concurrency problems, no
matter which RDBMS you are using. Designing to eliminate them makes
hassles like this one go away.

Mary
 
Back
Top