OpenRecordset Method

See Also      Applies To

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

Syntax: CdbConnection, CdbDatabase

CdbRecordsetOpenRecordset(LPCTSTR pstrSource,

LONG lType = -1,

LONG lOptions = -1,

LONG lLockEdits = -1);

Syntax: CdbQueryDef

CdbRecordsetOpenRecordset(LONG lType = -1,

LONG lOptions = -1,

LONG lLockEdits = -1);

Syntax: CdbRecordset, CdbTableDef

CdbRecordsetOpenRecordset(LONG lType = -1,

LONG lOptions = -1);

Parameters

Settings

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

Note If you open a CdbRecordset in a Microsoft Jet workspace and you don't specify a type, OpenRecordset creates a table-type CdbRecordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type CdbRecordset. In an ODBCDirect workspace, the default setting is dbOpenForwardOnly.

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

Note The constants dbConsistent and dbInconsistent are mutually exclusive, and using both causes an error. Supplying an lLockEdits argument when lOptions uses the dbReadOnly constant also causes an error.

You can use the following constants for the ILockEdits argument.

Remarks

From a CdbQueryDef or a dynaset- or snapshot-type CdbRecordset object in a Microsoft Jet workspace, or if pstrSource refers to an SQL statement or a CdbTableDef that represents a linked table, you can't use dbOpenTable for the lType 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 CdbRecordset opened from a Microsoft Jet-connected ODBC data source, you can improve performance by using dbOpenForwardOnly for the lType argument.

From a dynaset- or snapshot-type CdbRecordset, the new CdbRecordset is of the same type. From a table-type CdbRecordset object, the type of the new object is a dynaset-type CdbRecordset. You can't open new CdbRecordset objects from forward-only–type or ODBCDirect CdbRecordset objects.

In an ODBCDirect workspace, you can open a CdbRecordset containing more than one select query in the pstrSource argument, such as:

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

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

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 CdbRecordset 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 lOptions argument. This allows your application to continue processing other statements while the query runs in the background. But, you cannot access the CdbRecordset data until the query has completed. To determine whether the query has finished executing, check the StillExecuting property of the new CdbRecordset. 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 CdbRecordset 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 CdbRecordset by using the MoveLast method as soon as the CdbRecordset is opened.

If you open a CdbConnection object with DefaultCursorDriver set to dbUseClientBatchCursor, you can open a CdbRecordset to cache changes to the data (known as batch updating) in an ODBCDirect workspace. Include dbOptimisticBatch in the ILockedits 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 CdbRecordset with the Close method automatically deletes it from the Recordsets collection.

Note If pstrSource 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 CdbRecordset. 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.

Usage: Database

#include <afxole.h>
#include <dbdao.h>

CdbDatabase      db;
CdbRecordset      rst;
CString         strSql = _T("SELECT * FROM PARTS");
...                              // Initialize db, etc.
rst = db.OpenRecordset((LPCTSTR)strSql, dbOpenDynaset,                               dbDenyWrite, dbPessimistic);

Usage: QueryDef

#include <afxole.h>
#include <dbdao.h>

CdbQueryDef      qd;
CdbRecordset      rst;
...               // Initialize qd, etc.
rst = qd.OpenRecordset( dbOpenDynaset, dbDenyWrite, dbPessimistic);

Usage: TableDef

#include <afxole.h>
#include <dbdao.h>

CdbDBEnghine      dben;
CdbDatabase      dbs;
CdbRecordset      rst;
CdbTableDef      td;
CdbField         fld;
   // Initialize the TableDef.
dbs = dben.OpenDatabase(...);
td = dbs.CreateTableDef(...);
fld = td.CreateField(...);
td.Fields.Append(fld);
dbs.TableDefs.Append(td);
   // Open a recordset based on the TableDef.
rst = td.OpenRecordset( dbOpenTable, dbAppendOnly );