Database - What do the parameters of the recordset.Open method mean?
If you look at the help for the Open method of the recordset, you will see this syntax listed:
Code:
recordset.Open <a href="#source">Source</a>, <a href="#conn">ActiveConnection</a>, <a href="#cursor">CursorType</a>, <a href="#lock">LockType</a>, <a href="#options">Options</a>
As the help uses many technical terms to describe what these all mean (and contains advanced options) many people get confused... so here is a simpler guide for "normal" usage, that will hopefully be easier to understand!
Note that each of these can be set using separate properties instead (eg: recordset.Source = "table1" ), but if you use those you should not specify values for them for the .Open method too, as that can cause errors!
Even tho you aren't forced to enter values for each parameter, you still should as you then know what behavior to expect, and reduce the chances of errrors.
<a name="source"></a>Source
This specifies what data you want in the recordset.
It can be a variety of different things (such as a Command or Stream object), but in simple terms it will contain either:
a) A table name (you should set the Options parameter to adCmdTable ), or:
b) An SQL statement (you should set the Options parameter to adCmdText ), or:
c) The name of an Access Query, or a Stored Procedure (you should set the Options parameter to adCmdStoredProc )
Note that if your SQL statement is an action query (such as Insert or Update), you should not be using a recordset - you should be using the .Execute method of a connection object instead, eg: objConn.Execute "INSERT INTO table1 (field1) Values (10)"
<a name="conn"></a>Active Connection
This specifies which database to get the data from.
You can use two different methods, either:
a) A Connection object (like cn in Dee-u's ADO tutorial), or:
b) A connection string (something like "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\DB1.mdb")
The first option is used most often, as it provides more features (such as being able to run action queries), and allows for better memory management.
<a name="cursor"></a>Cursor Type
The cursor type basically determines two things:
1) what methods you can use to move through the recordset (.MoveFirst/.MoveNext/..),
2) whether you see the changes to the data that other users have made since you opened the recordset.
Note that it can also effect if the recordset.RecordCount property works as you would expect. If that is important to you see the article Why does Recordcount sometimes equal -1?
The options are listed in order of efficiency, you should use the first one that is appropriate for you:
adOpenForwardOnly
This is the default. It is the fastest, and uses the least amount of memory and network traffic.
1) You can only use .MoveNext to move thru the records (not .MoveFirst etc).
2) You will not see changes to the data by other users since you opened the recordset.
Using this option means that the .RecordCount property is not available until you reach the last record (it will return -1 before that), so you need to use the .EOF property to read all the records, eg:
Code:
objRS.Open "SELECT field1 FROM table1", objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Do while Not objRS.EOF
'do something with the data here!
Msgbox objRS.Fields("field1").Value
objRS.MoveNext
Loop
adOpenStatic
1) You can use any of the .Move methods (.MoveFirst/.MovePrev/..).
2) You will not see changes to the data by other users since you opened the recordset.
adOpenKeyset
1) You can use any of the .Move methods (.MoveFirst/.MovePrev/..).
2) You will see Some changes to the data (edited/deleted records only) by other users since you opened the recordset.
adOpenDynamic
1) You can use any of the .Move methods (.MoveFirst/.MovePrev/..).
2) You will see All changes to the data (added/edited/deleted records) by other users since you opened the recordset.
<a name="lock"></a>Lock Type
The lock type also determines two things:
1) whether you can make changes to the data (add/edit/delete),
2) how other users will be stopped from editing the same record as you.
Once again these are listed in order of efficiency, you should use the first one that is appropriate for you.
adLockReadOnly
This is the default. It has the least amount of effect on other users, and is better in terms of speed/memory/network usage.
1) You cannot add/edit/delete data, you can only read it.
2) You do not block other users from editing records in the table.
adLockOptimistic
1) You can add/edit/delete data.
2) When you edit a record, another user can edit it at the same time - but if you both try to write the data, one of you will get an error when the .Update occurs.
adLockPessimistic
1) You can add/edit/delete data.
2) When you edit a record, it is immediately blocked from other users. An error will occur as soon as any values are altered in the recordset (eg: objRs.Fields("Field1").Value = 10 ).
Depending on the database you are using, this may block multiple records, rather than just the one you are editing!
<a name="options"></a>Options
This specifies extra behaviour that is not directly covered by the other parameters.
Generally you will only specify one thing, which is what the "Source" parameter contains:
a) If Source is a table name, use: adCmdTable
b) If Source is an SQL statement, use: adCmdText
c) If Source is the name of a Query/Stored Procedure, use: adCmdStoredProc
Note that if you don't set this, a "best guess" will be made, and you may get odd errors if the wrong assumption is made!
