Public Function AdoOpenRS(ByRef wADORecordSet As ADODB.Recordset, _
sqlSELECTCommand As String, _
Optional Param As Variant = , _
Optional ActiveADOConn As ADODB.Connection = Nothing, _
Optional ShowMsgBoxIfFail As Boolean = True, _
Optional CloseRSIfFail As Boolean = True, _
Optional SetRSNothingIfFail As Boolean = True, _
Optional sRetErrDesp As String = ) As Boolean
'Param 参数数组11 On Error GoTo errOpenRs
If Len(sqlSELECTCommand) = 0 Then Exit Function
If TypeName(Param) = Variant() Then
Dim i As Byte
For i = 1 To UBound(Param)
sqlSELECTCommand = Replace(sqlSELECTCommand, ? & i, Param(i))
Next
End If
wADORecordSet.CursorLocation = adUseClient '客户端游标
If ActiveADOConn Is Nothing Then
wADORecordSet.Open sqlSELECTCommand, SQLConn, adOpenDynamic, adLockOptimistic
Else
wADORecordSet.Open sqlSELECTCommand, ActiveADOConn, adOpenDynamic, adLockOptimistic
End If
Exit Function
errOpenRs:
If ShowMsgBoxIfFail Then MsgBox Err.Description, vbExclamation, ErrOpenRs
'If CloseRSIfFail Then wADORecordSet.Close
If SetRSNothingIfFail Then Set wADORecordSet = Nothing35 End Function
调用例子:
Dim strsql As String
Dim Param(1 To 1) As Variant
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
strsql = select OrderID From [Order] & _
where convert(nvarchar(7),OrderDate,120) = '?1'
Param(1) = Format(Now, YYYY-MM)
AdoOpenRS rs, strsql, Param
sqlSELECTCommand As String, _
Optional Param As Variant = , _
Optional ActiveADOConn As ADODB.Connection = Nothing, _
Optional ShowMsgBoxIfFail As Boolean = True, _
Optional CloseRSIfFail As Boolean = True, _
Optional SetRSNothingIfFail As Boolean = True, _
Optional sRetErrDesp As String = ) As Boolean
'Param 参数数组11 On Error GoTo errOpenRs
If Len(sqlSELECTCommand) = 0 Then Exit Function
If TypeName(Param) = Variant() Then
Dim i As Byte
For i = 1 To UBound(Param)
sqlSELECTCommand = Replace(sqlSELECTCommand, ? & i, Param(i))
Next
End If
wADORecordSet.CursorLocation = adUseClient '客户端游标
If ActiveADOConn Is Nothing Then
wADORecordSet.Open sqlSELECTCommand, SQLConn, adOpenDynamic, adLockOptimistic
Else
wADORecordSet.Open sqlSELECTCommand, ActiveADOConn, adOpenDynamic, adLockOptimistic
End If
Exit Function
errOpenRs:
If ShowMsgBoxIfFail Then MsgBox Err.Description, vbExclamation, ErrOpenRs
'If CloseRSIfFail Then wADORecordSet.Close
If SetRSNothingIfFail Then Set wADORecordSet = Nothing35 End Function
调用例子:
Dim strsql As String
Dim Param(1 To 1) As Variant
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
strsql = select OrderID From [Order] & _
where convert(nvarchar(7),OrderDate,120) = '?1'
Param(1) = Format(Now, YYYY-MM)
AdoOpenRS rs, strsql, Param