NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
|
Sets or returns the type of cursor driver used on the connection created by the
OpenConnection or
OpenDatabase methods (ODBCDirect workspaces only).
Syntax
expression .DefaultCursorDriver
expression A variable that represents a Workspace object.
Remarks
The setting or return value can be set to one of the
CursorDriverEnum constants.
This property setting only affects connections established after the property has been set. Changing the DefaultCursorDriver property has no effect on existing connections.
Example
This example uses the NextRecordset method to view the data from a compound SELECT query. The DefaultCursorDriver property must be set to dbUseODBCCursor when executing such queries. The NextRecordset method will return True even if some or all of the SELECT statements return zero records; it will return False only after all the individual SQL clauses have been checked.
Sub NextRecordsetX()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rstTemp As Recordset
Dim intCount As Integer
Dim booNext As Boolean
' Create ODBCDirect Workspace object and open Connection
' object. The DefaultCursorDriver setting is required
' when using compound SQL statements.
Set wrkODBC = CreateWorkspace("", _
"admin", "", dbUseODBC)
wrkODBC.DefaultCursorDriver = dbUseODBCCursor
' Note: The DSN referenced below must be set to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
"ODBC;DATABASE=pubs;DSN=Publishers")
' Construct compound SELECT statement.
Set rstTemp = conPubs.OpenRecordset("SELECT * " & _
"FROM authors; " & _
"SELECT * FROM stores; " & _
"SELECT * FROM jobs")
' Try printing results from each of the three SELECT
' statements.
booNext = True
intCount = 1
With rstTemp
Do While booNext
Debug.Print "Contents of recordset #" & intCount
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop
booNext = .NextRecordset
Debug.Print " rstTemp.NextRecordset = " & _
booNext
intCount = intCount + 1
Loop
End With
rstTemp.Close
conPubs.Close
wrkODBC.Close
End Sub
Another way to accomplish the same task would be to create a prepared statement containing the compound SQL statement. The CacheSize property of the QueryDef object must be set to 1, and the Recordset object must be forward-only and read-only.
Sub NextRecordsetX2()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim qdfTemp As QueryDef
Dim rstTemp As Recordset
Dim intCount As Integer
Dim booNext As Boolean
' Create ODBCDirect Workspace object and open Connection
' object. The DefaultCursorDriver setting is required
' when using compound SQL statements.
Set wrkODBC = CreateWorkspace("", _
"admin", "", dbUseODBC)
wrkODBC.DefaultCursorDriver = dbUseODBCCursor
' Note: The DSN referenced below must be set to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
"ODBC;DATABASE=pubs;DSN=Publishers")
' Create a temporary stored procedure with a compound
' SELECT statement.
Set qdfTemp = conPubs.CreateQueryDef("", _
"SELECT * FROM authors; " & _
"SELECT * FROM stores; " & _
"SELECT * FROM jobs")
' Set CacheSize and open Recordset object with arguments
' that will allow access to multiple recordsets.
qdfTemp.CacheSize = 1
Set rstTemp = qdfTemp.OpenRecordset(dbOpenForwardOnly, _
dbReadOnly)
' Try printing results from each of the three SELECT
' statements.
booNext = True
intCount = 1
With rstTemp
Do While booNext
Debug.Print "Contents of recordset #" & intCount
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop
booNext = .NextRecordset
Debug.Print " rstTemp.NextRecordset = " & _
booNext
intCount = intCount + 1
Loop
End With
rstTemp.Close
qdfTemp.Close
conPubs.Close
wrkODBC.Close
End Sub
This example uses the RecordStatus and DefaultCursorDriver properties to show how changes to a local Recordset are tracked during batch updating. The RecordStatusOutput function is required for this procedure to run.
Sub RecordStatusX()
Dim wrkMain As Workspace
Dim conMain As Connection
Dim rstTemp As Recordset
Set wrkMain = CreateWorkspace("ODBCWorkspace", _
"admin", "", dbUseODBC)
' This DefaultCursorDriver setting is required for
' batch updating.
wrkMain.DefaultCursorDriver = dbUseClientBatchCursor
' Note: The DSN referenced below must be configured to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
Set conMain = wrkMain.OpenConnection("Publishers", _
dbDriverNoPrompt, False, _
"ODBC;DATABASE=pubs;DSN=Publishers")
' The following locking argument is required for
' batch updating.
Set rstTemp = conMain.OpenRecordset( _
"SELECT * FROM authors", dbOpenDynaset, 0, _
dbOptimisticBatch)
With rstTemp
.MoveFirst
Debug.Print "Original record: " & !au_lname
Debug.Print , RecordStatusOutput2(.RecordStatus)
.Edit
!au_lname = "Bowen"
.Update
Debug.Print "Edited record: " & !au_lname
Debug.Print , RecordStatusOutput2(.RecordStatus)
.AddNew
!au_lname = "NewName"
.Update
Debug.Print "New record: " & !au_lname
Debug.Print , RecordStatusOutput2(.RecordStatus)
.Delete
Debug.Print "Deleted record: " & !au_lname
Debug.Print , RecordStatusOutput2(.RecordStatus)
' Close the local recordset without updating the
' data on the server.
.Close
End With
conMain.Close
wrkMain.Close
End Sub
Function RecordStatusOutput(lngTemp As Long) As String
Dim strTemp As String
strTemp = ""
' Construct an output string based on the RecordStatus
' value.
If lngTemp = dbRecordUnmodified Then _
strTemp = "[dbRecordUnmodified]"
If lngTemp = dbRecordModified Then _
strTemp = "[dbRecordModified]"
If lngTemp = dbRecordNew Then _
strTemp = "[dbRecordNew]"
If lngTemp = dbRecordDeleted Then _
strTemp = "[dbRecordDeleted]"
If lngTemp = dbRecordDBDeleted Then _
strTemp = "[dbRecordDBDeleted]"
RecordStatusOutput = strTemp
End Function