473,544 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DAO recordset append single record locks entire table (linked table)

I'm having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.

Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset

Set wkSpace = DBEngine.Worksp aces(0)
Set db = CurrentDb()

wkSpace.BeginTr ans

'some code...

Set rstTrans = db.OpenRecordse t("tblTrans", dbOpenDynaset,
dbAppendOnly, dbOptimistic)
With rstTrans
.AddNew
'set field values...
.Update
.Close
End With

'codes updating other tables

wkSpace.CommitT rans

The process is fine if the tables are local tables. But if the tables
are linked tables, it locks the entire tblTrans table once it execute
the Update method. No other user can update or append the tblTrans
table at all (with "Could not update; currently locked." error)

I'm sure that nothing else lock the table as I did run the code (just
the recordset append code) in the frontend and backend.

It seems the lock apply properly only to local tables?

May 10 '06 #1
22 18768
I imagine that the lock would be due to the transaction.

See if it makes any difference if you omit the line:
wkSpace.BeginTr ans

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RayPower" <ra********@yah oo.com.au> wrote in message
news:11******** ************@u7 2g2000cwu.googl egroups.com...
I'm having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.

Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset

Set wkSpace = DBEngine.Worksp aces(0)
Set db = CurrentDb()

wkSpace.BeginTr ans

'some code...

Set rstTrans = db.OpenRecordse t("tblTrans", dbOpenDynaset,
dbAppendOnly, dbOptimistic)
With rstTrans
.AddNew
'set field values...
.Update
.Close
End With

'codes updating other tables

wkSpace.CommitT rans

The process is fine if the tables are local tables. But if the tables
are linked tables, it locks the entire tblTrans table once it execute
the Update method. No other user can update or append the tblTrans
table at all (with "Could not update; currently locked." error)

I'm sure that nothing else lock the table as I did run the code (just
the recordset append code) in the frontend and backend.

It seems the lock apply properly only to local tables?

May 10 '06 #2
Hi Allen,

Thanks for your prompt reply.

Yes, I know the lock is due to the transaction. But I need to use
transaction as all updates should rollback if anything goes wrong.

The problem is whether the tables are link tables or local tables. With
local tables, the tblTrans table will not be locked. But with link
table, the entire tblTrans table just being locked even though I'm only
appending one record in that table.

Is that something wrong with JET or just Access 2000?

May 10 '06 #3
Did anyone encounter this problem before?

May 21 '06 #4
Have you tried opening just a single record instead of the entire
table?

strSQL = "SELECT TOP 1 * FROM tblTrans;"
Set rstTrans = db.OpenRecordse t(strSQL, dbOpenDynaset,
dbAppendOnly, dbOptimistic)

RayPower wrote:
Did anyone encounter this problem before?


Jun 15 '06 #5
I tried that but it still lock the entire table. I even tried to open
an empty recordset with "SELECT * FROM tblTrans WHERE TRUE=FALSE" but
with no luck.

The DAO append method locks the entire table if the target table is a
linked table (no matter the back end is located in the same PC or on
the Windows server).

Laurie wrote:
Have you tried opening just a single record instead of the entire
table?

strSQL = "SELECT TOP 1 * FROM tblTrans;"
Set rstTrans = db.OpenRecordse t(strSQL, dbOpenDynaset,
dbAppendOnly, dbOptimistic)

RayPower wrote:
Did anyone encounter this problem before?


Jun 26 '06 #6
RayPower wrote:
The DAO append method locks the entire table if the target table is a
linked table (no matter the back end is located in the same PC or on
the Windows server).


DAO's Recordset Locking seems very complex. I suppose one could wade
through the various switches and optional parameters until one got what
one wanted, or realized that one could not get what one wanted.

Are you sure the entire Table is locked as opposed to just the 2K page
where the record lives?

Have you considered using ADO?

Have you considered Updating the Record with an SQL UPDATE call (using
DAO or ADO) and not opening a Recordset at all, or opening it only long
enough to get values as the basis for your Edit? I believe this is
almost always the most efficient way tp Update Data.

IMO Recordsets should be opened only as a last resort; although I have
opened Recordsets [as in Declaring a Variable/Pointer and "Setting" it]
in the past, I have not done so for a very long time, and plan to
continue that practice.

Jun 26 '06 #7
"RayPower" <ra********@yah oo.com.au> wrote in
news:11******** **************@ c74g2000cwc.goo glegroups.com:
The DAO append method locks the entire table if the target table
is a linked table (no matter the back end is located in the same
PC or on the Windows server).


This was true with Jet 2.x, but is not true with Jet 3.5 and beyond.

Are you using dbAppendOnly?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 26 '06 #8
I know the transaction works find if I use append queries & the table
will not be locked. Unfortunately I need to use the recordset as I need
to process updates on various tables one record at a time (with
different conditions etc).

I'm using JET 4 & tried using different parameters in the OpenRecordset
method including dbAppendOnly.

In the testing of locking entire table, there is interesting thing. I
run the program from the front-end with the breakpoint set at the
AddNew method & step through the code. I also open the tblTrans table
in te back-end & test if the table is locked. Before hitting the Update
method, I can still update records in the tblTrans table in the
back-end datasheet. But once the Update method was executed, I cannot
update records except those I updated before the Updated method was
executed. I also tried not to update any record in the tblTrans table
in the back-end, the entire table was locked from update.

Jun 26 '06 #9
RayPower wrote:
Unfortunately I need to use the recordset as I need
to process updates on various tables one record at a time (with
different conditions etc).


And ... why can't you do this executing SQL statements?

Jun 26 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2057
by: Annette Massie | last post by:
I have a database with a linked table within it. I have a query form that opens and allows the user to select criteria and then the form is filled with data found from the linked table. At what point is the table linking? For example, if I open the form it takes forever. I would like the form to open immediately, allow the user to enter...
5
4458
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this error: Deleting data in a linked table is not supported by this ISAM. From what I understand, indexed sequential access method (ISAM) drivers are...
1
5328
by: robert demo via AccessMonster.com | last post by:
I'm trying to retrieve the connection string for a linked table in a backend that is password protected. I've modified the code shown below to temporarily check that the backend has been successfully opened by showing, via a messagebox, the last record in the table. The connection string should be provided in the last line shown, but I...
5
3315
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example I'm adding a customer. The Customer fields are mostly foreign keys that refer to primary keys in other tables, left join instead of junction tables...
3
2419
by: jbsfe | last post by:
I have "Spilt" my database and the "lookup" and "seek" methods that previously worked, no longer do. I have learnd from reviewing the posts that the "lookup" and "Seek" methods cannot be used on linked tables and that you cannot set an "index" for a linked table. What I don't know how to do, is revise my code to complete the same tasks as...
1
3874
by: news.sbcglobal.net | last post by:
Hi! I am able to add records to my SQL 2005 database using bound forms in my Access application, but if I try to edit any records (not only the new records I create) using the same bound forms, I receive a write conflict. For the record, I am currently the only user of this application and my SQL Server database has only one userID. I used...
1
4254
by: Gurdeep Toor via AccessMonster.com | last post by:
Hi Folks, I am building an application in VB.NET... It connects to 2 databases MS SQL SERVER 2005 and MS ACCESS 2003 .. It works all fine with SQL but It only reads the record from the ACCESS table which is linked with SAGE ACCOUNTING SOFTWARE V 11... Now When i Update or add record to the ACCESS linked table then i got the Error..... INVALID...
6
2664
by: jsacrey | last post by:
Hello everybody, I've got a bit of a situation that I could use some guidance with if possible. I work for an auditing firm where my users audit electronic shipping data for customers to see if they've been overcharged for shipments by truck and rail carriers. 99.9% of the time, one of our auditors needs to see all data sent by a...
2
3698
by: hapnendad | last post by:
The code below attempts to create a record in the tables listed and populating the PAR field (Primary Key) in each when a new project is added. The code adds rows to the tables but, the number of records does not increase. Each of the tables a record is added to is linked to a backend access database. Must I open the backend database and...
0
7362
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7602
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7764
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7704
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5291
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4911
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3404
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1837
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
657
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.