Solved

VBA in Access 2010 - Run-time Error 430

Posted on 2013-12-06
9
1,141 Views
Last Modified: 2013-12-06
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

Open in new window

or this
Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs

Open in new window

. 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

Open in new window


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

Open in new window


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?
0
Comment
Question by:cartb4horse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39702061
Where are you declaring the rs object?
0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39702078
In a separate module:

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=vnysql;DSN=RecordsMgmt_SQLDB;UID=DMP;Trusted_Connection=Yes;DATABASE=RecordsManagementDB;"
        con.Open
    End If

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

Open in new window

0
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 500 total points
ID: 39702093
Try setting the cursor type to adOpenDynamic and LockType to adLockOptimistic. Just a thought?? Otherwise, I hope someone else has a better idea for you.

Good luck!

ab
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39702104
>Where are you declaring the rs object?
>In a separate module:
I don't see a 'Dim rs as ...' line in that function, which means it's being declared somewhere else, likely by the calling code block.

If you intend on that rs being available everywhere in your app, then in the top declaration section of any code module instead of Dim use..
Global rs as Recordset ' DAO. or ADODB., your call

Open in new window

If you intend on rs being an indifidual thing, then in the code block that calls the function use this
Global rs as Recordset ' DAO. or ADODB., your call

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39702127
>when I use DAO Querydef and a passthrough query
Just for kicks and giggles, when you locate the 'Dim rs as...' line eyeball whether it is DAO.Recordset, ADODB.Recordset, or just rs.

If just rs, in any code window do a Tools:References and see whether DAO or ADODB is checked, and if so which is higher.   Reason I ask is because Access will interpret 'just rs' as whichever one is higher in the References dialog.
0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39702164
Okay, I think we're getting somewhere.  I did as @aebea suggested and changed the cursor type and the lock type and I'm no longer getting the Run Time error.  But what's happening now is that I'm getting  "run time error 7965: the object you entered is not a valid recordset property".  @jimhorn - I do have a global declaration in my module for rs.  Looks like this:

Option Compare Database
Option Explicit

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

Open in new window


My Access references

Access References
0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39702178
I forgot to mention that the new error is on the same line of code as the previous error:  
Set Me.lstResults.Recordset = rs

Open in new window

0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39702347
I was able to get rid of the latest runtime error by removing the IF..THEN..ELSE statement referencing the .CursorLocation in my public function. Now it only read .CursorLocation = adUseClient. Viola, it now works perfectly! Thanks for all your help.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39702391
Glad you're all squared away. Have a good weekend!

ab
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question