Creating a Recordset

You can create a recordset by using the OpenRecordset method of the Database, TableDef, or QueryDef objects.

Set rst = dbs.OpenRecordset( Source, Type, Options, LockEdits) Set rst = object.OpenRecordset( Type, Options, LockEdits)

The Source argument specifies the name of a table or query, or a string expression that equates to an SQL query. For dbOpenTable recordsets, the Source argument can only be the name of a table.

The default recordset type that is opened if you omit the Type argument, depends on the type of table you're trying to open. If you open a Microsoft Jet recordset on a local table, the default is a Table type. If you open a Microsoft Jet recordset against a linked table or query, the default type is Dynaset. If you open an ODBCDirect recordset, the default type is ForwardOnly.

The Type argument values are specified by a number of constants. These constants and their values can be found in Appendix C. The following code examples demonstrate how to open different types of recordsets.

Opening a Recordset Based on a Table or Query

Dim dbs As Database

Dim rsTable As DAO.Recordset

Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

'Open a table-type recordset

Set rsTable = dbs.OpenRecordset("Table1", dbOpenTable)

'Open a dynaset-type recordset using a saved query

Set rsQuery = dbs.OpenRecordset("qryMyQuery", dbOpenDynaset)

Opening a Recordset Based on an SQL Statement

Dim dbs As Database

Dim rsSQL As DAO.Recordset

Dim strSQL As String

Set dbs = CurrentDb

'Open a snapshot-type recordset based on an SQL statement strSQL = "SELECT * FROM Table1 WHERE Field2 =33" Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Opening a Recordset That Locks Out All Other Users

Dim dbs As Database

Dim rsSQL As DAO.Recordset

Set dbs = CurrentDb

'Open a dynaset-type recordset based on a saved query Set rsSQL = dbs.OpenRecordset("qryMyQuery", _ dbOpenDynaset, dbDenyRead)

Opening a Recordset That Contains More Than One SELECT Query

In ODBCDirect workspaces, you can open one of the following recordset types that contain more than one SELECT query: Dynamic, Dynaset, ForwardOnly, Snapshot.

The following example demonstrates how to use such a recordset. We start by declaring all the variables and opening an ODBCDirect connection.

Dim wsODBC As Workspace Dim cn As DAO.Connection Dim rst As DAO.Recordset Dim intCount As Integer Dim booNext As Boolean

'Create ODBCDirect Workspace

Set wsODBC = CreateWorkspace("", "sa", "somepassword", dbUseODBC)

'The DefaultCursorDriver setting is required when using 'compound SQL statements.

wsODBC.DefaultCursorDriver = dbUseODBCCursor

'Open the connection (in this case, using a DSN) Set cn = wsODBC.OpenConnection("myConnection", , , _ "ODBC;DATABASE=myDB;DSN=myDSN")

Next, we open the ODBCDirect recordset, specifying two or more different SELECT queries, each separated by a semicolon (now you know what the semicolon is for).

'Open the recordset

Set rst = cn.OpenRecordset("SELECT * FROM Table1; " & _

"SELECT * FROM Table2", dbOpenDynamic)

As you would expect, the first dataset to be loaded into the recordset is the one that is specified first in the method call or SQL property, and you can cycle through each recordset in the same way you would with other recordsets.

If when you finish with one recordset, and there are more records that are pending (including an empty recordset), the next dataset is loaded into the recordset and the NextRecordset property returns True. When the last record of the last recordset has been read, this property returns False.

You can terminate a recordset, and move on to the next one, by using the NextRecordset method. In the example, this is done by prematurely exiting the Do...Loop construct after the first record is returned.

'Cycle through the recordset queries

booNext = True

intCount = 1

With rst

Do While booNext

Debug.Print "Contents of recordset

" & intCount & "..."

Do While Not .EOF

Debug.Print , !Field1, !Field2

'Terminate the first recordset

early

If intCount = 1 Then Exit Do

.MoveNext

Loop

'Load the next query

booNext = .NextRecordset

intCount = intCount + 1

Loop

End With

rst.Close

cn.Close

wsODBC.Close

As with other recordsets, you can flush the recordset with the recordset's Cancel method, but you should remember that this cancels the entire recordset, not just the current dataset.

0 0

Post a comment