Access 2010中的VBA-运行时错误430 [英] VBA in Access 2010 - Run-time Error 430

问题描述

我收到运行时错误'430':该行代码Set Me.lstResults.Recordset = rs或此Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs上的类不支持自动化或不支持预期的接口."我正在尝试获取ADO Recordset.基于SQL的存储过程出现在Access窗体的未绑定列表框或子窗体中.我在Win 7 Machine上，使用Access 2010连接到SQL Server 2008:

I’m getting a Run-time error '430': Class does not support Automation or does not support expected interface" on this line of code Set Me.lstResults.Recordset = rs or this Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs. I am trying to get the ADO Recordset based on a SQL stored procedure to appear in an unbound Listbox or Subform of an Access form. I’m on Win 7 Machine using Access 2010 connecting to SQL Server 2008:

On_Click事件:

On_Click event:

Private Sub cmdRun_Click()
'On Error Resume Next

    Dim strSQL As String

    'Stored procedure + parameters called from form
    strSQL = "Exec sqlsp_searchalltables " & Me.txtTables & _
       ", " & "'%" & Me.txtSearchTerm & "%'"

    OpenMyRecordset rs, strSQL

    'debug - view procedure
    Me.lblQuery.Caption = strSQL
    Me.Repaint

    Set Me.lstResults.Recordset = rs
    'or this
    'Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs
End Sub

我在网络上找到了针对此错误的一些解决方案，并尝试了所有解决方案，但均无济于事.大多数建议建议检查我所做并验证的参考文献.

I found some solutions for this error on the web and tried all of them to no avail. Most suggested checking the references which I did and verified.

当我使用DAO Querydef和直通查询或使用此.listbox方法时，我能够成功连接到SQL Server，并在列表框和子窗体中显示结果:

I am able to successfully connect to the SQL server and have the results display in both a Listbox and Subform when I use DAO Querydef and a passthrough query or if I use this .listbox method:

With Me.lstResults
    Do
       strItem = rs.Fields("CLIENT_ID").Value
       .AddItem strItem
       rs.MoveNext
    Loop Until rs.EOF
End With

我不想使用DAO方法，因为我发现我需要ADO的编码灵活性，尤其是在连接到SQL中的多个Recordset时.有想法吗?

I would prefer not to use the DAO method because I found I need the coding flexibility of ADO especially with connecting to multiple Recordsets in SQL. Thoughts?

仅供参考:我在模块中的OpenMyRecordset公共功能:

FYI: My OpenMyRecordset public function in Module:

Option Compare Database
Option Explicit

Global con As New ADODB.Connection
Global rs As ADODB.Recordset
Global NoRecords As Boolean

Public Enum rrCursorType
    rrOpenDynamic = adOpenDynamic
    rrOpenForwardOnly = adOpenForwardOnly
    rrOpenKeyset = adOpenKeyset
    rrOpenStatic = adOpenStatic
End Enum

Public Enum rrLockType
    rrLockOptimistic = adLockOptimistic
    rrLockReadOnly = adLockReadOnly
End Enum

Public Function OpenMyRecordset(rs As ADODB.Recordset, strSQL As String, Optional rrCursor As rrCursorType, _
    Optional rrLock As rrLockType, Optional bolClientSide As Boolean) As ADODB.Recordset

    If con.STATE = adStateClosed Then
        con.ConnectionString = "ODBC;Driver={SQL Server};Server=mysqlsvr;DSN=RecordsMgmt_SQLDB;UID=XXX;Trusted_Connection=Yes;DATABASE=RecordsManagementDB;"
        con.Open
    End If

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = con
            .CursorLocation = adUseClient
        .CursorType = IIf((rrCursor = 0), adOpenDynamic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockOptimistic, rrLock)
        .Open strSQL
        If .EOF And .BOF Then
            NoRecords = True
            Exit Function
        End If
    End With

End Function

推荐答案

您绝对不必为了只填充列表框而执行循环方法.我不熟悉您使用的OpenMyRecordset命令，但我怀疑其功能中的某些因素导致了此错误(即，它没有以与列表框兼容的方式打开记录集).这就是我连接到SQL Server Express的本地实例并能够填充列表框的方式.

You definitely do not have to do the looping method to just to populate the listbox. I'm not familiar with the OpenMyRecordset command you used, but I suspect that something in its functionality is what is causing this error (i.e., it's not opening the recordset in a manner compatible with the listbox). This is how I connected to a local instance of SQL Server Express and was able to populate a listbox.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

With cn
    .ConnectionString = _
        "Provider=SQLOLEDB;Data Source=localhost\SQLEXPRESS;" & _
        "Initial Catalog=Northwind;Trusted_Connection=yes"
    .Open
End With

Set rs = New ADODB.Recordset

With rs
    Set .ActiveConnection = cn
    .Source = "SELECT FirstName, LastName FROM Employees"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
End With

Set Me.lstTest.Recordset = rs

Set rs = Nothing
Set cn = Nothing

您必须确保在项目中启用了Microsoft ActiveX数据对象库引用.

You will have to make sure that you have the Microsoft ActiveX Data Objects Library reference enabled in your project.

这篇关于Access 2010中的VBA-运行时错误430的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！