RS.Edit and RS.Update...definitions please...

[gusbrunston]

Hi:

In a manual on VB and two manuals on Access2000, and in Help, I'm having trouble finding a reference to "RS.Edit" and "RS.Update".

Below is a snippet of code from a function that works great...but I need to lock the records while they're being edited...

Does "RS.Edit" lock the record being edited? and does "RS.Update" unlock it?

If I didn't use RS.Edit and RS.Update, would the record be edited and updated anyway (through the SQL statement not shown below)?

I've put this code in so that you can more easily see what I'm about:

[tt]Function....

Set db = CurrentDb()
Set RS = db.OpenRecordset(SQL, dbOpenDynaset)

If RS.RecordCount = 0 Then
‘if no records, close and exit
RS.Close
GoTo Exit1
End If

If RS!BeginBalance > 0 Then
‘accounts in existence before implementation
‘have a balance from the old system
CurBB = RS!BeginBalance
Else
‘new accounts have no beginning balance
CurBB = 0
End If

‘Does this lock the first record?
RS.Edit
RS!Balance = CurBB - RS!PayAmount + RS!DepAmount
‘Does this unlock the first record?
RS.Update
CurDB = RS!Balance
RS.MoveNext
Do Until RS.EOF
RS.Edit ‘again: will this lock the record?
RS!Balance = CurDB - RS!PayAmount + RS!DepAmount
RS.Update ‘and again, unlock the record?
CurDB = RS!Balance
RS.MoveNext
Loop

RS.Close
db.Close
....
End Function
[/tt]
Thanks for your help, and/or pointing me in the right direction.
Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[danvlas]

Access 2000 is by default based on ADO.
In ADO you don't need the .Edit and .Update methods.
That's why you have trouble finding help info.

Your code is DAO.
In DAO you need .Edit which merely tells DBEngine that the record is going to be changed.
If you want to save changes in the record made after using .Edit, you must use .Update method. Otherwise the changes are not saved.
As for locking:
Here is what the Access 97 Help says about OpenRecordset method:

Creates a new Recordset object and appends it to the Recordsets collection.

Syntax

For Connection and Database objects:

Set recordset = object.OpenRecordset (source, type, options, lockedits)

For QueryDef, Recordset, and TableDef objects:

Set recordset = object.OpenRecordset (type, options, lockedits)

The OpenRecordset method syntax has these parts.

Part	Description
recordset	An object variable that represents the Recordset object you want to open.
object	An object variable that represents an existing object from which you want to create the new Recordset.
source	A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-type Recordset objects in Microsoft Jet databases, the source can only be a table name.
type	Optional. A constant that indicates the type of Recordset to open, as specified in Settings. 
options	Optional. A combination of constants that specify characteristics of the new Recordset, as listed in Settings.
lockedits	Optional. A constant that determines the locking for the Recordset, as specified in Settings. 
Settings 

You can use one of the following constants for the type argument.

Constant	Description
 
 
dbOpenTable	Opens a table-type Recordset object (Microsoft Jet workspaces only).
dbOpenDynamic	Opens a dynamic-type Recordset object, which is similar to an ODBC dynamic cursor. (ODBCDirect workspaces only)
dbOpenDynaset	Opens a dynaset-type Recordset object, which is similar to an ODBC keyset cursor.
dbOpenSnapshot	Opens a snapshot-type Recordset object, which is similar to an ODBC static cursor.
dbOpenForwardOnly 	Opens a forward-only-type Recordset object. 
Note   If you open a Recordset in a Microsoft Jet workspace and you don't specify a type, OpenRecordset creates a table-type Recordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset. In an ODBCDirect workspace, the default setting is dbOpenForwardOnly.

You can use a combination of the following constants for the options argument.

Constant	Description
dbAppendOnly 	Allows users to append new records to the Recordset, but prevents them from editing or deleting existing records (Microsoft Jet dynaset-type Recordset only).
dbSQLPassThrough 	Passes an SQL statement to a Microsoft Jet-connected ODBC data source for processing (Microsoft Jet snapshot-type Recordset only).
dbSeeChanges	Generates a run-time error if one user is changing data that another user is editing (Microsoft Jet dynaset-type Recordset only). This is useful in applications where multiple users have simultaneous read/write access to the same data. 
dbDenyWrite 	Prevents other users from modifying or adding records (Microsoft Jet Recordset objects only).
dbDenyRead 	Prevents other users from reading data in a table (Microsoft Jet table-type Recordset only).
dbForwardOnly 	Creates a forward-only Recordset (Microsoft Jet snapshot-type Recordset only). It is provided only for backward compatibility, and you should use the dbOpenForwardOnly constant in the type argument instead of using this option. 
dbReadOnly 	Prevents users from making changes to the Recordset (Microsoft Jet only). The dbReadOnly constant in the lockedits argument replaces this option, which is provided only for backward compatibility.
dbRunAsync	Runs an asynchronous query (ODBCDirect workspaces only). 
dbExecDirect 	Runs a query by skipping SQLPrepare and directly calling SQLExecDirect (ODBCDirect workspaces only). Use this option only when you’re not opening a Recordset based on a parameter query. For more information, see the "Microsoft ODBC 3.0 Programmer’s Reference."
dbInconsistent 	Allows inconsistent updates (Microsoft Jet dynaset-type and snapshot-type Recordset objects only).
dbConsistent 	Allows only consistent updates (Microsoft Jet dynaset-type and snapshot-type Recordset objects only).
Note   The constants dbConsistent and dbInconsistent are mutually exclusive, and using both causes an error. Supplying a lockedits argument when options uses the dbReadOnly constant also causes an error.

You can use the following constants for the lockedits argument.

Constant	Description
dbReadOnly	Prevents users from making changes to the Recordset (default for ODBCDirect workspaces). You can use dbReadOnly in either the options argument or the lockedits argument, but not both. If you use it for both arguments, a run-time error occurs.
dbPessimistic 	Uses pessimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record you're editing is locked as soon as you use the Edit method (default for Microsoft Jet workspaces).
dbOptimistic 	Uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record is not locked until the Update method is executed.
dbOptimisticValue 	Uses optimistic concurrency based on row values (ODBCDirect workspaces only).
dbOptimisticBatch 	Enables batch optimistic updating (ODBCDirect workspaces only).
Remarks

In a Microsoft Jet workspace, if object refers to a QueryDef object, or a dynaset- or snapshot-type Recordset, or if source refers to an SQL statement or a TableDef that represents a linked table, you can't use dbOpenTable for the type argument; if you do, a run-time error occurs. If you want to use an SQL pass-through query on a linked table in a Microsoft Jet-connected ODBC data source, you must first set the Connect property of the linked table's database to a valid ODBC connection string. If you only need to make a single pass through a Recordset opened from a Microsoft Jet-connected ODBC data source, you can improve performance by using dbOpenForwardOnly for the type argument. 

If object refers to a dynaset- or snapshot-type Recordset, the new Recordset is of the same type object. If object
 refers to a table-type Recordset object, the type of the new object is a dynaset-type Recordset. You can't open new Recordset objects from forward-only–type or ODBCDirect Recordset objects.
In an ODBCDirect workspace, you can open a Recordset containing more than one select query in the source argument, such as

"SELECT LastName, FirstName FROM Authors 
WHERE LastName = 'Smith';
SELECT Title, ISBN FROM Titles 
WHERE ISBN Like '1-55615-*'"

The returned Recordset will open with the results of the first query. To obtain the result sets of records from subsequent queries, use the NextRecordset method.

Note   You can send DAO queries to a variety of different database servers with ODBCDirect, and different servers will recognize slightly different dialects of SQL. Therefore, context-sensitive Help is no longer provided for Microsoft Jet SQL, although online Help for Microsoft Jet SQL is still included through the Help menu. Be sure to check the appropriate reference documentation for the SQL dialect of your database server when using either ODBCDirect connections or pass-through queries in Microsoft Jet-connected client/server applications.

Use the dbSeeChanges constant in a Microsoft Jet workspace if you want to trap changes while two or more users are editing or deleting the same record. For example, if two users start editing the same record, the first user to execute the Update method succeeds. When the second user invokes the Update method, a run-time error occurs. Similarly, if the second user tries to use the Delete method to delete the record, and the first user has already changed it, a run-time error occurs.

Typically, if the user gets this error while updating a record, your code should refresh the contents of the fields and retrieve the newly modified values. If the error occurs while deleting a record, your code could display the new record data to the user and a message indicating that the data has recently changed. At this point, your code can request a confirmation that the user still wants to delete the record.

You should also use the dbSeeChanges constant if you open a Recordset in a Microsoft Jet-connected ODBC workspace against a Microsoft SQL Server 6.0 (or later) table that has an IDENTITY column, otherwise an error may result.

In an ODBCDirect workspace, you can execute asynchronous queries by setting the dbRunAsync constant in the options argument. This allows your application to continue processing other statements while the query runs in the background. But, you cannot access the Recordset data until the query has completed. To determine whether the query has finished executing, check the StillExecuting property of the new Recordset. If the query takes longer to complete than you anticipated, you can terminate execution of the query with the Cancel method.

Opening more than one Recordset on an ODBC data source may fail because the connection is busy with a prior OpenRecordset call. One way around this is to use a server-side cursor and ODBCDirect, if the server supports this. Another solution is to fully populate the Recordset by using the MoveLast method as soon as the Recordset is opened.

If you open a Connection object with DefaultCursorDriver set to dbUseClientBatchCursor, you can open a Recordset to cache changes to the data (known as batch updating) in an ODBCDirect workspace. Include dbOptimisticBatch in the lockedits argument to enable update caching. See the Update method topic for details about how to write changes to disk immediately, or to cache changes and write them to disk as a batch.

Closing a Recordset with the Close method automatically deletes it from the Recordsets collection.

Note   If source refers to an SQL statement composed of a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to open the Recordset. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and SQL only accepts U.S. decimal characters.

So, if your code is 'mission-critical', use the pessimistic lock.

HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[RickSpr]

You have the right idea, but the details depend on something you've left out.

First some definitions of locking strategies. The strategy is chosen by the Record Locks property of a form or query, or by a combination of the 'options' and 'lockedits' arguments of the OpenRecordset method in code.

Table locking: The entire table is locked when you open the recordset.
Forms and Queries: "All records" setting
Code: dbDenyWrite setting of the 'options' argument (I think)

Pessimistic locking: The page containing the record is locked before the first change is made to one of its fields.
Forms and Queries: "Edited Record" setting
Code: dbPessimistic setting of the 'lockedits' argument

Optimistic locking: The page containing the record is locked just before it is written to the database.
Forms and Queries: "No Locks" setting
Code: dbOptimistic setting of the 'lockedits' argument

If you're using pessimistic locking, the Edit method locks the current record (and other records on the same page), and the Update method unlocks it (unless the update is occurring within a transaction). This corresponds to "Yes" answers to your questions in the code.

If you're using optimistic locking, the Edit method saves a copy of the record. The Update method locks the record (and page), then reads the record and compares it to the saved copy. If they're the same, indicating that no other user has changed the record since the Edit method was called, Update writes the updated record and then unlocks it (again, unless it is occurring within a transaction). If the record has been changed by another user, Update raises a runtime error. (In forms and queries, this error results in Access asking the user whether to overwrite the changes, or to cancel the update.)

When a transaction is in effect (BeginTrans and Commit method of the Workspace object), all locks are held until the Commit or Rollback method is called.

Access 2000 allows row-level locking. I don't think that changes the locking strategy, other than locking only a record instead of a page during optimistic and pessimistic locking. I'm not sure of the details, however. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[gusbrunston]

[tt]
Hi:

Thanks to both of you, Danvlas and RickSpr. I guess I can't postpone any longer delving into locking strategies. I've been putting it off, along with database security. Oh well, nothing else important to do for six months or so!

By the way, I thought A2K was by default DAO. Not that I know too much about that either, except that if I was a lot younger I'd better learn about ADO and all those ActiveX controls which it seems are going to rule the universe(s) created by all those programmers.

Thanks again. Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[LittleSmudge]

Gus,

DAO is the standard for Access version 2 up to Access 97

ADO is the 'default' for A2k and beyond.

Those heavily into A97 and forced to use A2k have been known to import the DAO reference libraries into A2k to get their old A97 applications running without too much intervention. It is probobly those that you've come across.

As for learning ADO - just as you get up to speed the world will move to the .NET framework

Life is like walking up the down esculator - when you think you've stopped you're actually going backwards.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[gusbrunston]

[tt]
Dear GLS:

Thanks for setting me straight about the default for A2K.

Early on I had some great help in writing a Module, and the author told me to move a reference for the DAO library to the 3rd or 4th position in the "references" list, "so that it will compile." Never thought about it after that.

The code that I have written in many forms and some modules, is that all now in DAO, and should it be revised to be in "ADO"?

In other words, is DAO now DOA?

Are the tables, forms, reports, queries that I've designed, are they messed up in some way? (They work, but maybe this is a case where they should be fixed, "even if they're not broken.&quot

Interested in any comments you might have.

Thanks,[/tt] Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[LittleSmudge]

Well, now there could be the start of some very long conversations ( preferably in from of log fires with pints in hand .. .. ) but anyway, the answer actually comes down to some personal preferences and local conditions.

Guiding thoughts
DAO is the 'old technology'. There seems little point, to me, to learn DAO if you don't know it. ( unless you are only working on A97, or earlier, systems ).

ADO is 'newer technology'.

If your database is working on A2k at the moment and it is running DAO because you have the library referenced then does it make sense to go through and change everything to ADO just so that you can unlink the old DAO library ?
Well I'd guess NOT. Unless the database is small and you want the learning exercise .. .., otherwise I'd say leave well along.
In a commercial setting it certainly is not worth the time and effort unless there are some other compelling reasons why the DAO library is not to be made available.



Personally I try very hard to do everything in ADO if I'm working on an A2k or A2k2 job. This is sometimes harder than it might seem because even in A2k here are a lot of old DAO examples in the help files, and whilst you can do everything in ADO - finding out how is not always that easy.

However, reliving my life to go through all my old A97 apps to convert the DAO to ADO ? No thanks. - I'll have the life I've got !


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[danvlas]

"and whilst you can do everything in ADO"

LS, I'm trying the same thing here, and I havent't yet found the .CreateProperty method in ADO yet. Even though the .properties collection does exist and can be inspected, how do you create a new property?

Meanwhile, I'd change the statement above to:

"and whilst you can do almost everything in ADO"




Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[LittleSmudge]

You don't CREATE anymore

You APPEND

This is because a property is a member of the Properties collection.

So you are APPENDING a new property to the Properies collection.


'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[danvlas]

a-ha...gotta give it a try!


(sigh) I loved to think of me as CREATIVE...well, not anymore!



Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[danvlas]

I've tried...no success.
What is the ADO version for this wonderful piece of DAO code, which works perfectly (that's why it's wonderful):

Dim prp As DAO.Property
Set prp = CurrentDb.CreateProperty("ThisPrp", dbText, "Hello&quot
CurrentDb.Properties.Append prp


The Properties collection of a connection object does not support ... Append!

Or, if I'm trying it on the wrong object, what is the right one???

Stumped and hungry for this info...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[LittleSmudge]

Okay Daniel - I thinks its a bit of Humble Pie eating time .. .. Append is also a DAO construct.


I want back and had a look at the code I use in a database that I did convert from DAO to ADO

I think the following will be of use :-

Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
    Dim dbs As Object, prp As Variant
    Const conPropNotFoundError = 3270

    Set dbs = CurrentProject.Connection

    On Error GoTo Change_Err
    dbs.Properties(strPropName) = varPropValue
    ChangeProperty = True

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then    ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, _
            varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        ChangeProperty = False
        Resume Change_Bye
    End If
End Function

I remember now that to convert this from DAO to ADO all I did was change
Dim dbs As Database
Set dbs = currentDb
to
Dim dbs As Object
Set dbs = CurrentProject.Connection



'ope-that-'elps. G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[danvlas]

Nope...it doesn't help.
I made several applications that retained certain settings in custom properties. That was in DAO. I had a similar function that looked for existing properties and created them on the fly if they were missing at the moment of request.

If the reference to DAO is set, everything works fine. If not, the function fails and halts (my version) or jumps to the error handler and then exits (your version)

Now I have to find something similar for ADO, and I have been looking and looking and looking after this piece of info, but nothing showed up.

I can set and remove the reference to DAO on the fly, but looks like scratching the right ear with the left index...

Thanks, anyway!


Daniel Vlas
Systems Consultant
danvlas@yahoo.com