|
I am working with Microsoft Access MDBs (2003 Format) on a project and have a situation where two threads can be inserting records in the same table at the same time. I am using OleDB to open the Access MDB, create commands and transactions, and insert the records.
The connection string to open the MDB is essentially the following:
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Data Source = SOMENAME.MDB"
Even though I am trying to use record locking with this connection string, and the MDB is certainly shared, we consistently get "Could not update. Currently locked." errors when the two threads are writing to the same table at the same time (and the two threads are using separate OleDBConnections). I have tried using and not using transactions, and the error is the same.
Can anyone tell me what else I may need to do to avoid the "Could not update" problem I am experiencing?
Thank you...
modified on Wednesday, April 8, 2009 4:38 PM
|
|
|
|
|
AFAIK Access has PAGE locking, not record locking. I don't know if it still applies but MS used to state that Access is NOT a multiuser database and therefore record locking is not supported. Obviously the page is bigger than 1 record and your multithreaded app is screwing it up.
I used earlier versions with multiple users connected to the same data database with no real locking problems, but that is very different to a threaded application (which was not available on the desktop in those days).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply. FWIW, the earliest versions of Access only supported page locking, but the Access 2003 format, at least in theory, is supposed to support record-level locking. If you happen to have such a version of an Access MDB, under its Tools/Options/Advanced tab you can see that there is an option to open databases using record-level locking. And, in the connection string I am using the parameter "Jet OLEDB:Database Locking Mode=1" is supposed to be for record level locking (Mode=0 would be page locking).
I agree that the multi-threading seems to be using page locking, so I am curious if anyone has had any success with Access MDBs under a similar, but certainly not recommended, scenario. Unfortunately using SQL Express is not currently an option on this project; users may have to suffer with slower non-threaded performance if I can't figure something out.
|
|
|
|
|
I presume you have jumped MS directly with the problem and got no respose?
It's been has 15 yrs since I worked with Access. 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I presume you have jumped MS directly with the problem and got no respose?
We don't have direct MS support; and their knowledge base (and other groups) has not yet shed any light on what I am seeing. However, I continue to hold out hope that I am missing something painfully obvious that will soon become clear. OTOH, I may just have to travel down another road.
Mycroft Holmes wrote: It's been has 15 yrs since I worked with Access.
Lucky you. 
|
|
|
|
|
Assuming the two update threads belong to a single process, I would delegate all DB modifications to one specialized thread, and probably give it an input queue. Or choose another database (SQL Server Express).
The alternative, assuming DB load is low, is providing retry loops for every modification, with an appropriate sleep value of course.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Luc Pattyn wrote: I would delegate all DB modifications to one specialized thread, and probably give it an input queue
Thanks for the reply. Delegating all DB modifications to one thread with an input queue is an interesting thought. I'll probably give that a try if I can not figure out why this version of an Access MDB, which is supposed to support record-level locking, does not seem to at least in this multi-threaded app. Unfortunately SQL Server Express is not an option, and a retry loop could get real ugly in this app.
|
|
|
|
|
Hi,
How can I run (or call) more than one store procedure at the same time?
For example I have a 3 store procedure;
First is sp_GetResult
Second is sp_Allotment
Third is sp_Price
How can I run sp_Allotment and sp_Price stored procedures at the same time (like thread) in sp_GetResult stored procedure.
Note: I use MS SQL Server 2005
Best Regards...
|
|
|
|
|
Don't think you can. Sql Server determines its concurrency automatically and steps through stored procs iteratively.
Regards,
Rob Philpott.
|
|
|
|
|
hi friend
How to hide trigger so that othe user can not modify or view
plz help me
Thank and Regards
Azad yadav
|
|
|
|
|
u have to set the permission for object by using the managaement option and set the permission for other user.
let me know if need any things
|
|
|
|
|
sorry for the above go the database and then security and there u can set the deny or grant for alter or view
|
|
|
|
|
hi experts.
i have a several special characters in my column
like example
goes here
empno emp name
1 ve?u.r
2 raj
3 venki
4 Stáció
5 gái
6 Vecsés
7 Mosonmagyaróvár
8 sachin
i want to list out the names with only special characters plz help me out
regards
venu
|
|
|
|
|
|
use patindex function to find out the column with special character .
|
|
|
|
|
i have 10000 rows in that different different special characters r there how can i list with patindex plz specify
|
|
|
|
|
ex a-zA-Z0-9
the above pateern will accecpt only value in betwwn them for other they will return for presence of special character .
plz check it
|
|
|
|
|
hi friend
How to view trigger so that we can modify, we donnot name of trigger.
plz help me
Thank and Regards
Azad Yadav
|
|
|
|
|
The management studio lets you view and modify triggers easily.
If you want to scripts, use exec sp_helptext "Your_Trigger_Name" -- actually, any database object to view the trigger. And use Alter to modify the trigger.
Eslam Afifi
|
|
|
|
|
use this statement
select * from sys.triggers
which will return all the trggername list
|
|
|
|
|
how to draw my own control from scratch
|
|
|
|
|
in a general database?
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Hello Friends,
I've a table as given below
ID LChild RChild
1 2 3
2 4 6
3 5 7
4 8 9
5 10 11
6 12 13
7 0 14
Now i've to find out the LChild of 1, 1 has 2 in it's left and 2 has 4,6 as it's child and 4 and 6 has also two-two child each......At final 1 has 7 child on it's left and on it's Right it's 6
And also i've find out for each ID i.e 1,2,3,4,5,6,7
|
|
|
|
|
Yes I have same table and want sql query for counting left and right node
|
|
|
|
|
Hi all,
Someone once posted here an excellent self join code on aggregates but just couldn't find it. The problem is similar as follows:
PriKey Field1 Field2 Field3 Field4
1 54 Ninna 42 1
2 2 Sybil 53 2
3 6 Michelle 44 3
4 12 Abigail 47 4
5 67 Geneana 55 5
6 23 Abigail 40 4
7 21 Evelyn 41 6
8 6 Sybil 57 2
9 85 Ninna 57 1
10 3 Sybil 49 2
11 78 Evelyn 50 6
12 4 Michelle 51 3
I want to group by on Field4 and for each, I want the maximum in Field1 such that the output is as follows:
PriKey Field1 Field2 Field3 Field4
8 6 Sybil 57 2
3 6 Michelle 44 3
9 85 Ninna 57 1
6 23 Abigail 40 4
11 78 Evelyn 50 6
5 67 Geneana 55 5 I believe the solution involves a self join and the max aggregate function. Thanks in advance.
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
