如何为视图而不是存储过程配置ADO命令对象？ [英] How configure ADO command object for a view and not a stored procedure?

问题描述

我正在使用MS Access 2013使用DSN连接到Caché数据库中的2个视图。我已经将这些视图与表格相关联，但是我们希望在函数调用期间将连接限制为提取而不是链接的表。我在网上找不到有关使用ADO 2.8为View配置Command对象的信息。视图没有CommandType。根据所选的CommandType，我得到一个错误，指出我需要提供SQL文本，或者根本没有返回任何记录。连接对象状态很好。



我尝试过：



我已经尝试了每种CommandType和许多不同的方法来调用记录集返回。这是我用来玩这段代码的测试程序：

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

推荐答案

谢谢，PIEBALDconsult，这对我来说是一个重要的信息。我咨询了我们的报告编写者，他们整天在Cache数据库上运行SQL，我们决定尝试只拉一个字段。我仍然得到一个空的记录集，但至少没有错误。请记住，我一直在Access中将此视图用作Access中的链接表而没有问题（除非存在网络中断，因此获取记录的方法发生了变化）。我使用相同的DSN，连接打开正常。



这是我修改后的代码，不返回任何记录：

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

这篇关于如何为视图而不是存储过程配置ADO命令对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！