Thank you, PIEBALDconsult, that was an important piece of information for me. I've consulted with our report writers who run SQL against the Cache database all day, and we decided to try pulling just one field. I am still getting an empty recordset, but at least no errors. Remember, I've been using this view as a linked table in Access for months without issue (except when there are network interruptions, thus the change in approach for fetching the records). I'm using the same DSN, and the connection opens fine.
Here is my revised code that returns no records:
Public Function TestADO()
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRs As ADODB.Recordset
Const DSN = "CWSCACHEMSACCESS"
Const ALLERGY_VIEW = "SYSTEM.client_allergies_nondrug"
Const DIETARY_VIEW = "SYSTEM.active_diet_order"
Set adoConn = New ADODB.Connection
'open the connection
adoConn.Open DSN
If adoConn.State = adStateOpen Then
MsgBox "Connection Open"
Else
MsgBox "Connection Did Not Open"
GoTo proc_exit
End If
'Open the view and get contents into recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandType = adCmdText
.CommandText = "SELECT SYSTEM.client_allergies_nondrug.PATID FROM SYSTEM.client_allergies_nondrug"
.CommandTimeout = 120
'Set adoRs = adoCmd.Execute
Set adoRs = New ADODB.Recordset
adoRs.Open adoCmd, , adOpenStatic, adLockReadOnly
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