'**************************************
' Name: A basic ADO Open and Requery rou
' tine
' Description:New to ADO? Worried about
' ADO? This little subroutine gets round t
' he problems of opening ADO recordsets. P
' lease look elsewhere on this site for in
' fo on opening the database itself.
If you open a Recordset in your code, ADO expects you to close it before re-opening. But if it's not open you can't close it... (Oh My!).
Here's my solution. It requires a public ADODB.Connection - I call it gCn.
The routine will open a new recordset (compatible with Janus GridEx), or refresh it if it's open or if the SQL has changed. Optional ReadOnly argument.
' By: Steve Mann
'
'
' Inputs:rs - an ADO recordset (eg Dim r
' sMine as New ADODB.Recordset)
szSource - (eg "select * from customers")
Optional - bReadOnly (True for read-only)
'
' Returns:Sets the supplied Recordset.
'
'Assumes:Assumes your public ADO Connect
' ion object is called gCn, and that the s
' upplied szSource is a valid SQL statemen
' t.
'
'Side Effects:If you pass invalid SQL, y
' ou'll get an error. Ctrl+Break and you'l
' l be ready to F8 out and see where you w
' ent wrong.
'This code is copyrighted and has limite
' d warranties.
'Please see http://www.Planet-Source-Cod
' e.com/xq/ASP/txtCodeId.11869/lngWId.1/qx
' /vb/scripts/ShowCode.htm
'for details.
'**************************************
Public Sub ADO_OpenRs(rs As Recordset, szSource$, Optional bReadOnly = False)
' Open or Requery a Recordset.
On Error GoTo lab_Err
If rs.State = adStateClosed Or rs.Source <> szSource Then
If rs.State <> adStateClosed Then rs.Close
rs.Open szSource, gCn, adOpenStatic, IIf(bReadOnly, adLockReadOnly, adLockOptimistic)
Else
rs.Requery
End If
lab_Exit:
Exit Sub
lab_Err:
MsgBox Err.Description
GoTo lab_Exit
End Sub
Ref http://pscode.com/vb/scripts/ShowCodeAsText.asp?txtCodeId=11869&lngWId=1