DAO/ADO Recordset Question

[Eddy]

Hope someone can clear my thought. Thanks in advance.

I have the following table keeping all available document numbers for my
application.

Document / Num
-------------------------
Invoice / 1230
Receipt / 451

Currently, I am using SQL SELECT query to obtain the number and then utilize
SQL UPDATE query to increase the number by one. I am always worried about
somewhere between the two queries, there is a slight possibility that
someone else would obtain the old number (before the UPDATE query is
executed). So I am thinking of using a recordset to take control of the
table (ideally at the record). This way, I can read the number and increase
it by one altogether. My question is how MSDE and JET 4.0 (because I am
using both ADO and DAO) will handle the recordset when there are two
recordsets (two PCs) trying to access the table. Will MSDE / JET 4.0 arrange
the later one to wait and process it after the first one is clear. Or will
they just return an error to the later recordset.

 

[Scott McDaniel]

MSDE is the Microsoft Database Engine ... basically, a stripped down version
of SQL Server. Jet is the file server type dbengine native to Access. While
you can certainly use both in your application, I don't believe they have
anything to do with your question (if I understand your question). DAO and
ADO are libraries which can be used to access data stored in an MSDE of Jet
database.

You can use a pessimistic cursor to "lock" the recordset when the user
begins editing, but you could run into trouble with this. A better solution
would be to write the number when the new record is written to the database
.... is you're using MSDE, you could do this via a StoredProc and/or trigger
of some sort. With Jet, you'll probably do it via code (or, use an
Autonumber). If you need to return this value to your frontend (using Jet),
I've always found it easier to open a recordset.

 

[Graham R Seach]

Eddy,

Why not just use an update query?
sSQL = "UPDATE myTable SET myNum = myNum+1 WHERE somefield = somevalue"
db.Execute sSQL

There's only one action.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia