How configure ADO command object for a view and not a stored procedure?

Question

0.00/5 (No votes)

See more:

I'm using MS Access 2013 to connect to 2 views in a Caché database using a DSN. I have linked these views like tables with success, but we want to limit connection to fetching during a function call rather than a linked "table". I find nothing online about using ADO 2.8 to configure a Command object for a View. There is no CommandType for a View. Depending on the CommandType selected, I either get an error stating that I need to provide SQL text, or no records returned at all. The connection object status is fine.

What I have tried:

I have tried each CommandType and a number of different ways to call for the recordset return. Here is a test procedure I've been using to play with this code:

Public Function TestADO()
    Dim adoConn As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim adoRs As ADODB.Recordset
    Const DSN = "CWSCACHEMSACCESS" 'The name of my tested DSN
    Const ALLERGY_VIEW = "SYSTEM.client_allergies_nondrug" 'The name of my View
   
    Set adoConn = New ADODB.Connection
   
    'open the connection
    adoConn.Open DSN
   
    If adoConn.State = adStateOpen Then
        MsgBox "Connection Open"
    Else
        MsgBox "Connection Not Opening"
        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 'have tried all options
        .CommandText = ALLERGY_VIEW
        .CommandTimeout = 120
       
        Set adoRs = adoCmd.Execute
        'The following doesn't work either:
        '===================================
        'Set adoRs = New ADODB.Recordset
        'adoRs.Open adoCmd, adoConn, adOpenStatic, adLockReadOnly
    End With
   
    'close the connection
    'adoConn.Close
   
    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

Posted 11-Apr-17 13:23pm

Doug Booth

Updated 12-Apr-17 6:54am

Add a Solution

Comments

PIEBALDconsult 11-Apr-17 20:19pm

You can't.

_Asif_ 12-Apr-17 2:44am

Views are accessed as tables (they don't have specialize syntax) and correct syntax would be like
SELECT * FROM client_allergies_nondrug. That is :
.CommandText = "SELECT * FROM client_allergies_nondrug";

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Doug Booth · Answer 1 · 2017-04-12T06:54:00

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