Hi Sam,
Trust me, you're not lost and confused yet. Read on. I apologize for the length of this post, but ADO recordset objects and cursors behave in very unexpected and peculiar ways, and you really have to get your hands dirty with a little code to understand their idiosyncrasies.
Quote:
|
quote:do i need to set rst to nothing? what if i want to use rst later on.
|
First, you have total control over the lifetime of ADO objects. It's completely up to you when you create them and destroy them. To create them, use:
Dim rst AS ADODB.Recordset
Set rst = New ADODB.Recordset
The Set statement explicitly creates a new instance of the object. To destroy the object use:
rst.Close
Set rst = Nothing
If you destroy a recordset object and need another one, just re-Set your object variable to a New instance (Set rst = New ADODB.Recordset), and call the new instance's Open method (rst.Open string, connection, cursor, lock, etc.). That's all there is to it.
One word of warning. You may run into the following declaration syntax:
Dim rst As New ADODB.Recordset (instead of Dim rst As ADODB.Recordset). The New keyword is added to the declaration.
Here, the New keyword enables implicit creation of the recordset object. The first time the object is referenced a new instance of it will be created. No Set statement is needed. AVOID THIS SYNTAX. Watch what happens when you try and destroy an object instantiated in this way. Place the following code in a module in the Northwind database and run it from the Debug window:
Sub rstTest()
Dim rst1 As ADODB.Recordset
' implicit instantiation
Dim rst2 As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM Customers"
' explicit instantiation
Set rst1 = New ADODB.Recordset
rst1.Open strSQL, CurrentProject.Connection, adOpenStatic
rst2.Open strSQL, CurrentProject.Connection, adOpenStatic
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
If rst1 Is Nothing Then
MsgBox "Explicit rst1 is nothing."
Else
MsgBox "Explicit rst1 is NOT nothing"
End If
If rst2 Is Nothing Then
MsgBox "Implicit rst2 is nothing."
Else
MsgBox "Implicit rst2 is NOT nothing"
End If
End Sub
Notice that the implicitly instantiated object (rst2) isn't destroyed, even though you've set it to Nothing. I don't know exactly why this occurs, but the fact that it does will lead to memory leak problems if you use implicit instantiation.
Quote:
|
quote:How can i use the execute method with adDynaSet option, Is it even possible to do that? I mean, should i use execute method instead of rst.open???
|
It isn't possible to use the adOpenDynamic (Dynaset is a DAO term) cursor type with the Execute method of a Connection or Command object because the Execute method always returns a forward-only, read-only recordset (if it returns records at all, and it doesn't have to). I only use the Execute method to execute queries that don't return records (i.e., "action queries" like INSERTs, UPDATEs, DELETEs). Using the Open method of the Recordset object will allow you to use cursor types, like adOpenDynamic, that provide more functionality.
Now here's where it gets pretty weird. I hope you're sitting down. While adOpenDynamic is part of the ADO cursor enum, and you can use it in your code, Jet does not support dynamic cursors, so using adOpenDynamic will give you either a Keyset cursor or a Static cursor, depending on your Lock Type. If you use adOpenDynamic and adLockReadOnly you will get a Static cursor. All other combinations of adOpenDynamic and the LockType enum will give you a Keyset cursor. A true dynamic cursor would allow users in a multi-user environment to instantaneously see data modifications made by other users. Jet doesn't support this functionality. A Keyset cursor allows users to make changes, but if they want to see changes made by other users, they have to refresh their cursor (by closing and reopening a recordset object).
To see what type of cursor you are actually getting with the various Cursor Type/Lock Type combinations, I put together the following module in a new db and just imported the Customer table from Northwind. You can run it from the debug window:
Sub WhatsMyCursorType()
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strCursor As String
Dim strLock As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
' Change the cursor type/lock type properties to see which
' kind of cursor the OLE DB provider gives you.
rst.Open "Customers", cnn, adOpenDynamic, adLockOptimistic
' rst.Open "Customers", cnn, adOpenDynamic, adLockBatchOptimistic
' rst.Open "Customers", cnn, adOpenDynamic, adLockPessimistic
' rst.Open "Customers", cnn, adOpenDynamic, adLockReadOnly
strCursor = GetCursorType(rst.CursorType)
strLock = GetLockType(rst.LockType)
Debug.Print "The OLE DB provider opened a " & strCursor & " cursor using " & strLock & " locking."
End Sub
Function GetCursorType(CursorType As Integer) As String
' *******************
' CursorTypeEnum
'
' adOpenDynamic = 2
' adOpenForwardOnly = 0
' adOpenKeyset = 1
' adOpenStatic = 3
' adOpenUnspecified = -1
'*******************
Select Case CursorType
Case 2
GetCursorType = "Dynamic"
Case 0
GetCursorType = "ForwardOnly"
Case 1
GetCursorType = "Keyset"
Case 3
GetCursorType = "Static"
Case -1
GetCursorType = "Unspecified"
End Select
End Function
Function GetLockType(LockType As Integer) As String
'*******************
' LockTypeEnum
'
' adLockBatchOptimistic = 4
' adLockOptimistic = 3
' adLockPessimistic = 2
' adLockReadOnly = 1
' adLockUnspecified = -1
'*******************
Select Case LockType
Case 4
GetLockType = "BatchOptimistic"
Case 3
GetLockType = "Optimistic"
Case 2
GetLockType = "Pessimistic"
Case 1
GetLockType = "ReadOnly "
Case -1
GetLockType = "Unspecified"
End Select
End Function
Running the above as is outputs this message to the debug window:
"The OLE DB provider opened a Keyset cursor using Optimistic locking." (even though I specified the adOpenDynamic cursor type).
Another example would be using the adOpenForwardOnly cursor type. You'd expect to get a cursor that would generate an error if you attempted to use the MovePrevious method, right? That's what happens if you use adOpenForwardOnly and adLockReadOnly. You get a true ForwardOnly/ReadOnly cursor. But if you use adOpenForwardOnly and adLockOptimistic, you are suddenly able to use the MovePrevious method. Is this because your ForwardOnly cursor miraculously gained added functionality from the new lock type? Nope. It's because this combination doesn't give you a ForwardOnly cursor, it gives you a Keyset cursor. The true ForwardOnly/ReadOnly cursor only supports the following methods:
Find
Notifications
The Keyset cursor supports these methods (with Optimistic Locking):
AddNew
Delete
Find
Holding Records
MovePrevious and Move
Notifications
Update
batch updating
I'm working on a module at the moment that will output all of the methods supported by all of the Cursortype/LockType combinations, just to help me keep this straight.
Anyway, just wanted to let you in on two of ADO's little secrets: 1. The object you thought you destroyed may still be alive and well if you didn't instantiate it correctly, and 2. The cursor you asked for is frequently not the one you get. It takes a little VBA to interrogate your recordset objects thoroughly, and find out what they are really up to.
Oh, I typically use adOpenKeyset/adLockOptimistic if I want an updateable recordset I can navigate through, and adOpenForwardOnly/adLockReadOnly if I just want to fetch read-only data.
Here are some pretty standard ADO blocks:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT JobTitle FROM tblJobTitle " _
& "ORDER BY JobTitle"
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
' code
rst.Close
Set rst = Nothing
or here the conection string references a split backend .mdb:
' module level recordset variable
Dim mrstClients As Recordset
Private Sub Form_Open(Cancel As Integer)
Dim cnnClient As ADODB.Connection
Dim strProvider As String
Dim strDataSource As String
strProvider = "Microsoft.Jet.OLEDB.4.0"
strDataSource = CurrentProject.Path _
& "\dbOutpatient.mdb."
Set cnnClient = New ADODB.Connection
cnnClient.Open "Provider = " & strProvider & _
"; Data Source = " & strDataSource
Set mrstClients = New ADODB.Recordset
mrstClients.Open "qryClients", cnnClient, adOpenKeyset, _
adLockOptimistic
Regards,
Bob
converting DAO code to ADO
