我收到运行时错误“ 430”：该行代码Set Me.lstResults.Recordset = rs或this上的类不支持自动化或不支持预期的接口Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs。我试图基于SQL存储过程获取ADO Recordset，以便在其中显示我在Win 7机器上使用Access 2010连接到SQL Server 2008：
On_Click事件：
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
我在网络上找到了针对此错误的一些解决方案，并尝试了所有解决方案，但均无济于事。多数建议检查我所做并验证的参考资料。
当我使用DAO Querydef和直通查询或使用此.listbox方法时，我能够成功连接到SQL Server并将结果显示在列表框和子窗体中：
With Me.lstResults
Do
strItem = rs.Fields("CLIENT_ID").Value
.AddItem strItem
rs.MoveNext
Loop Until rs.EOF
End With
我宁愿不使用DAO方法，因为我发现我需要ADO的编码灵活性，尤其是在连接到SQL中的多个Recordset时。有什么想法吗？
仅供参考：我OpenMyRecordset在模块中的公共职能：
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的本地实例并能够填充列表框的方式。
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数据对象库引用。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句