The following error message pops up when I execute my VBA code to run a query on an external SQL database and place results in a cell:
Run-time error '430' Class does not support Automation or does not support expected interface.
It occurs specifically on the line when i try to CopyFromRecordset
I know for a fact that my query is running successfuly and recordset is being filled because my recordcount = 1, and I get the value that I want returned when I run "?SeqRecordset.Value" in the immediate window. So its not an issue where there is a blank recordset.
Sub GetData()
Dim seqConn As ADODB.Connection
Dim vinConn As ADODB.Connection
Dim SeqRecordset As ADODB.Recordset
Dim VinRecordset As ADODB.Recordset
Dim strSQLSeq, strSQLVin As String
'Variables
Set seqConn = New ADODB.Connection
Set SeqRecordset = New ADODB.Recordset
Set vinConn = New ADODB.Connection
Set VinRecordset = New ADODB.Recordset
'Open Connections
seqConn.connectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=...;Password=...;Initial Catalog=...;Data Source=..."
seqConn.Open
vinConn.connectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=...;Password=...;Initial Catalog=...;Data Source=..."
vinConn.Open
'Set and Execute SQL Commands
strSQLSeq = "..."
strSQLVin = "..."
'Open Recordset
SeqRecordset.CursorType = adOpenStatic
SeqRecordset.LockType = adLockReadOnly
SeqRecordset.CursorLocation = adUseClient
Set SeqRecordset.ActiveConnection = seqConn
SeqRecordset.Open strSQLSeq, seqConn
'Copy Data to Excel'
ActiveSheet.Range("B1").CopyFromRecordset (SeqRecordset)
ActiveSheet.Range("B2").CopyFromRecordset (VinRecordset)
End Sub
Two queries are being ran from two different data catalogs. I am trying to place results of those 2 queries in two different cells, but with no success as of right now due to the error shown.
Note: The content of the connection and query strings have been replaced with "..."