1

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 "..."

Luke Fairway
  • 87
  • 1
  • 5

0 Answers0