Access Locking Problem.

  • Thread starter Thread starter Miky
  • Start date Start date
M

Miky

Hi,

I wrote a VB.NET data-entry application that has to be used by about 80
people. The controls on the form bind to the dataset. When we were making
the test on multiple machines, the application on some of the PCs locked
stating that the record (probably stored in the same memory page) that it
was trying to access was locked by another machine . The database is Access.

Wasn't .NET a (data) disconnected environment?

Do I have to move out from the databinding and manage the connection myself?

Hope you can help me out of this soon.

Thanks,
ME-LDV
 
80 concurrent users is a stretch for Access/JET. Even though 250 is
the absolute limit, 15-20 is generally agreed on as the practical
limit. You'll want to take a hard look at your table schema in the mdb
and partition it so that users are not updating the same rows in the
same tables at the same time.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
Look at the conneciton string at
http://www.kjmsolutions.com/flexibleconnectionstring.htm

If you do not have the following I need to see your connection string as you
have it now as that will cause a lock. Post it anyway just in case.

Extended Properties=;Mode=Share Deny None;

I wrote a VB.NET data-entry application that has to be used by about 80
people. The controls on the form bind to the dataset. When we were making
the test on multiple machines, the application on some of the PCs locked
stating that the record (probably stored in the same memory page) that it
was trying to access was locked by another machine .
Click to expand...


Try changing your connection string to the one posted at
http://www.kjmsolutions.com/
 
Thanks for Mary's response,

Hi Miky,

I think there's nothing wrong with databinding on the form. The problem
should be inside the connection. Please also check if you have opened the
database exclusively.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Yes, we meant the same. :)

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks to all for your answers.

Unfortunately, we need to deploy this application using
Access (at least for now), because the Server Group is
moving to a different area and then any new deployment
using server applications (SQL Server, Oracle, etc...) has
been frozen until the Summer.

This is the connection string:

Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry
Path=;Jet OLEDB:Database Locking Mode=0;Data
Source="G:\SAT\Test.mdb";Mode=Share Deny None;Jet
OLEDB:Engine Type=4;Provider="Microsoft.Jet.OLEDB.4.0";Jet
OLEDB:System database=;Jet OLEDB:SFP=False;persist
security info=False;Extended Properties=;Jet OLEDB:Compact
Without Replica Repair=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Create System Database=False;Jet
OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet
OLEDB:Global Bulk Transactions=1

Thanks,
LDV-ME.
 
¤ Hi,
¤
¤ I wrote a VB.NET data-entry application that has to be used by about 80
¤ people. The controls on the form bind to the dataset. When we were making
¤ the test on multiple machines, the application on some of the PCs locked
¤ stating that the record (probably stored in the same memory page) that it
¤ was trying to access was locked by another machine . The database is Access.
¤
¤ Wasn't .NET a (data) disconnected environment?
¤
¤ Do I have to move out from the databinding and manage the connection myself?
¤
¤ Hope you can help me out of this soon.

What version of Access are you using? If it's 2000 or higher (Jet 4.0) make certain you have the
database set up for record level locking (Tools...Options...Advanced tab in Access). Also set the
Jet OLEDB:Database Locking Mode argument in your connection string to a value of 1.

As Mary mentioned, 80 concurrent users is quite a stretch for Access and it isn't uncommon to
encounter database corruption and record or page level locking conflicts.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Miky,

Can you post exactly what the message was saying when the record was
"locked"? Your connection string as you have it is fine.

I suggest you post the code that is most commonly getting the "locked"
message as well. I suspect there is something else going on there.
 
Hi

Following is the detail of an error that I am getting

************** Exception Text *************
System.Data.OleDb.OleDbException: Could not lock file
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr
at System.Data.OleDb.OleDbConnection.InitializeProvider(
at System.Data.OleDb.OleDbConnection.Open(
at prjLogin.frmLogin.Login_Load(Object sender, EventArgs e
at System.Windows.Forms.Form.OnLoad(EventArgs e
at System.Windows.Forms.Form.OnCreateControl(
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible
at System.Windows.Forms.Control.CreateControl(
at System.Windows.Forms.Control.WmShowWindow(Message& m
at System.Windows.Forms.Control.WndProc(Message& m
at System.Windows.Forms.ScrollableControl.WndProc(Message& m
at System.Windows.Forms.ContainerControl.WndProc(Message& m
at System.Windows.Forms.Form.WmShowWindow(Message& m
at System.Windows.Forms.Form.WndProc(Message& m
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam

************** Loaded Assemblies *************
mscorli
Assembly Version: 1.0.5000.
Win32 Version: 1.1.4322.57
CodeBase: file:///c:/winnt/microsoft.net/framework/v1.1.4322/mscorlib.dl
---------------------------------------
prjLogi
Assembly Version: 1.0.1476.2044
Win32 Version: 1.0.1476.2044
CodeBase: file:///C:/Program%20Files/SIT/Loc%20Db/prjLogin.ex
---------------------------------------
System.Windows.Form
Assembly Version: 1.0.5000.
Win32 Version: 1.1.4322.57
CodeBase: file:///c:/winnt/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934e089/system.windows.forms.dl
---------------------------------------
Syste
Assembly Version: 1.0.5000.
Win32 Version: 1.1.4322.57
CodeBase: file:///c:/winnt/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dl
---------------------------------------
System.Drawin
Assembly Version: 1.0.5000.
Win32 Version: 1.1.4322.57
CodeBase: file:///c:/winnt/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/system.drawing.dl
---------------------------------------
System.Dat
Assembly Version: 1.0.5000.
Win32 Version: 1.1.4322.57
CodeBase: file:///c:/winnt/assembly/gac/system.data/1.0.5000.0__b77a5c561934e089/system.data.dl
---------------------------------------
System.Xm
Assembly Version: 1.0.5000.
Win32 Version: 1.1.4322.57
CodeBase: file:///c:/winnt/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/system.xml.dl
---------------------------------------
Microsoft.VisualBasi
Assembly Version: 7.0.5000.
Win32 Version: 7.10.3052.
CodeBase: file:///c:/winnt/assembly/gac/microsoft.visualbasic/7.0.5000.0__b03f5f7f11d50a3a/microsoft.visualbasic.dl
---------------------------------------

Following is the connection string

Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source="F:\SIT\Db\TestDb.mdb";Mode=Share Deny None;Jet OLEDB:Engine Type=4;Provider="Microsoft.Jet.OLEDB.4.0";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=

The database is in Access 2000

Why is it giving the error even if the mode is set to share

Thanks in advance
LDV-ME
 
¤ Hi,
¤
¤ Following is the detail of an error that I am getting:
¤
¤ ************** Exception Text **************
¤ System.Data.OleDb.OleDbException: Could not lock file.

Does this error happen on the same machines? Has everyone who is using the database been given
create, read, update and delete permissions for the folder where the database is located?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi Miky,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you're connection to an shared
Access 2000 database file, an error is generated which says "Could not lock
file.". If there's any misunderstanding, please feel free to let me know.

Based on my experience, if we share a .mdb database file, we have to limit
the number of users who open the database file simultaneously under 15. If
80 users are accessing the .mdb file at the same time. Some error on file
sharing might occur.

So let's try to isolate the problem. The "Could not lock file." error might
be generated, because some user opened the file exclusively and did not
close it properly. So the other users cannot open it. Please try the
following steps:

1. Copy the .mdb file to a local path. Change the data source in the
connection string to this local file, to see if we can connect to local
file. If this fails, it mean that this database file might be damaged.

2. Please check if you have the proper permission to the file. I think you
should have write permission to that folder.

3. Please check the folder to see if there's a .ldb file with the same name
as the .mdb file. When you open an Access database file (.mdb) in shared
mode, Microsoft Access also creates a locking information file (.ldb) with
the same file name (for example, Northwind.ldb) and in the same folder as
the database file. This locking information file stores the computer name
(such as mypc) and security name (such as Admin) of each shared user of the
database. Microsoft Access uses this information to control concurrency. In
most cases, Microsoft Access automatically deletes the locking information
file when the last user closes the database file. So if the file exists, it
means that there is still some user opening this file. Try to disconnect
them, or delete the .ldb file. Then can you open the database again?

4. Is the path "F:" in the connection string a mapped network drive? If
yes, please try to use a UNC (\\ServerName\FolderName) instead.

Hope this helps. If the problem still persists, please feel free to reply
to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi

Thanks to everybody

Both the problem sounds that have been resolved (we are still testing the application). For the first (record locking), we had to leave .NET managing the open connection rather than manually. It looks a little slower but at least doesn't lock user from using the application without problems. For the second (file-lock error), the server we were using as test box was the problem. One of the disks had to be replaced and now it works as expected

Thanks again for all your help
LDV-ME
 
Back
Top