Darlyne Pitt
asked on
VBA in Access 2010 - Run-time Error 430
I’m getting a Run-time error '430': Class does not support Automation or does not support expected interface" on this line of code
On_Click event:
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:
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?
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?
Where are you declaring the rs object?
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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..
>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
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
>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.
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.
ASKER
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:
My Access references
Option Compare Database
Option Explicit
Global con As New ADODB.Connection
Global rs As ADODB.Recordset
Global NoRecords As Boolean
My Access references
ASKER
I forgot to mention that the new error is on the same line of code as the previous error:
Set Me.lstResults.Recordset = rs
ASKER
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.
Glad you're all squared away. Have a good weekend!
ab
ab