I have used a linked table to a Cache database View but want to switch to an ADO call instead. I can open a connection using DSN, but cannot find info to configure a Command object for a View instead of a Stored Procedure or Table. I've played with it and nothing returns a recordset on Execute or RS.Open. I have 10 years experience with ADO and Stored Procedures, but this View issue is puzzling. There seems to be nothing I can find on the web about ADO and Views. What am I missing on the configuration of a Command object to get a recordset from a view?
Code:
Public Function TestADO()
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRs As ADODB.Recordset
Const DSN = "NameOfMyDSN"
Const ALLERGY_VIEW = "SYSTEM.name_of_my_view"
'open the connection
Set adoConn = New ADODB.Connection
adoConn.Open DSN
If adoConn.State = adStateOpen Then
MsgBox "Connection Open"
Else
MsgBox "Connection Closed"
GoTo proc_exit
End If
'Open the view and get contents into recordset
Set adoCmd = New ADODB.Command
Set adoCmd.ActiveConnection = adoConn
With adoCmd
.CommandType = adCmdUnknown 'have tried all options
.CommandText = ALLERGY_VIEW
.CommandTimeout = 120
'Set adoRs = adoCmd.Execute
Set adoRs = New ADODB.Recordset
adoRs.Open adocmd
End With
MsgBox "RS Count: " & adoRs.RecordCount & vbCrLf & "RS BOF: " & adoRs.BOF & vbCrLf & "RS EOF: " & adoRs.EOF
proc_exit:
On Error Resume Next
adoConn.Close
Set adoConn = Nothing
Set adoCmd = Nothing
Set adoRs = Nothing
Exit Function
proc_err:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "TestADO() Error"
Resume proc_exit
End Function
Tried all Command Types
Text returns an error that SQL code is expected
SP returns an error that SP name not found
Table returns no error but does not result in any records
Unknown returns an error that SQL code is expected