troubleshooting Question

VBA in Access 2010 - Run-time Error 430

Avatar of Darlyne Pitt
Darlyne PittFlag for United States of America asked on
Microsoft AccessMicrosoft SQL ServerVB Script
9 Comments1 Solution1281 ViewsLast Modified:
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 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.

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

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?
ASKER CERTIFIED SOLUTION
Anthony Berenguel

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros