Book Review: Beginning Visual Basic 6 Databases
The syntax for opening an ADO recordset is like this:
adoRecordset.Open Source, ActiveConnection, CursorType, LockType, Options
The Source argument is Optional. This is a valid command object variable name, or it might be an SQL statement, a table name, or a stored procedure call.
The ActiveConnection is also optional. This is either a valid Connection object variable name, or a String that contains our ConnectionString
The CursorType is also Optional. This is simply one of the cursor constants that tells the provider the type of cursor to use when opening the Recordset.
There are several other options that we can provide the recordset as well. You may recall that when we build the connect string in our ADO data control, these options were provided in a drop down box. They may have looked a bit cryptic back then, but now we can see what they mean:
|The provider should take the source as a text description of a command, such as a SQL statement||adCmdText|
|ADO should generate an SQL statement to fetch all rows from the table in Source||adCmdTable|
|The provider should return all of the rows from the table named in Source.||adCmdTableDirect|
|The provider should treat the Source as a stored procedure||adCmdStoredProc|
|The type of command in Source is unknown. You should not use this! It is the slowest of all cursors.||AdCmdUnknown|
|A saved recordset should be restored from the file names in Source||adCommandFile|
|Source should be executed asynchronously||adFetchAsync|
After the initial quantity of records specified in CacheSize is fetched, any remaining rows should be fetched asynchronously: adFetchAsync
When might you use some of these options? Well, a good illustration would be when using a transaction.
Transactions and You
Transactions are useful or even necessary when you need to make several changes at once. Think of a transaction as a logical unit of work. And if any part of the transaction fails, the whole thing is rolled back (i.e. any changes made since the beginning of the transaction are undone). The example often used in computer books is one drawn from banking. For example, let's say that you go to the bank to pay a bill. The funds are drawn from your account and placed in the electric company's account. So two things happen. First the funds are debited from your account and second, the funds are credited to the electric company's account. If the funds are not drawn from your account but are credited to the electric company, you are happy but the bank is not. If the funds are drawn from your account but not placed in the electric company's account, you are mad and the electric company is not that happy either. So both parts of the transaction must occur properly (i.e., the transaction is committed) or both part of the transaction are not executed (i.e., the transaction is rolled back).
Computer scientists use the acronym ACID to define the characteristics of a transaction.
Atomicity - Although the changes may include several records, if anything fails the entire transaction fails. The system goes back to a pre-transaction state.
Consistency -Although A transaction never leaves the database in an inconsistent state. If a change is made, it can be undone if the transaction fails at a later point.
Isolation - Although A transaction behaves as if it were in complete isolation from other transactions in the system.
Durability - Once a transaction's changes are committed, they persist beyond any system failure. If the system crashes (or GPF's!) after the transaction is committed, the transaction's results are still maintained.
Page 21 of 23