Switching from ODBC to OLEDB and getting "Object Required"

Question

I have been tasked with modifying some VBA code to switch from using an ODBC connection to using an OLEDB connection so we can just code a username and password into a subroutine.

I'm certainly not experienced with this but I think I'm managed to get the proper connection string for the new OLEDB connection.

I've tested the connection string in a standalone test sub and it worked fine.

I'm attempting to replace the previous ODBC connection strings in the code but I'm starting to get "Run-time error '424' Object required"

Here is the sub I am currently trying to get to work:

Sub getdata()
Dim tableName As String
Dim tableRecords As New ADODB.Recordset
Dim Cnxn2 As ADODB.Connection 

tableName = "TABLE_NAME"

Set Cnxn2 = New ADODB.Connection

'Old Connection String Using ODBC
'Cnxn2.ConnectionString = "Data Source='DATASOURCE';" '& "Integrated Security = SSPI;"

'New Connection String Using OLEDB
Cnxn2.ConnectionString = "Provider=sqloledb;Data Source=SERVER\INSTANCE;User ID=USER;Password=PASSWORD;"

Cnxn2.ConnectionTimeout = 30
Cnxn2.Open
tableRecords.Open tableName, Cnxn2, adUseClient, adLockOptimistic, adCmdTable

With tableRecords
ReturnData.Range("A1").CopyFromRecordset tableRecords(10)

.Close
End With
        Cnxn2.Close

        Set Cnxn2 = Nothing
        Set tableRecords = Nothing
    End Sub

The error is highlighting the line stating "ReturnData.Range("A1").CopyFromRecordset tableRecords(1)

Why am I getting Object Required errors when I only changed the connection string?

Note: There was a very similar sub in the code in another location and I simply deleted the "ReturnData." from the previously mentioned line and it seemed to start working. If I do that here though I get this: Run-time error '430': Class does not support Automation or does not support expected interface

Any help into why switching to OLEDB is breaking this sub is much appreciated.

Sam Sam · Accepted Answer · 2015-04-29T14:25:59

From you code ReturnData is not being created or anything, unless you are declaring it to be global variable else where. I imagine you are doing this in Excel. In which case replace ReturnData.Range("A1").CopyFromRecordset tableRecords(10) with something like

activeworkbook.sheets("Sheet1").Range("A1").CopyFromRecordset tableRecords(10)

EDIT. Managed to replicate it and then fixed it. Try this.

replace ReturnData.Range("A1").CopyFromRecordset tableRecords(10) with something like

activeworkbook.sheets("Sheet1").Range("A1").CopyFromRecordset tableRecords

Switching from ODBC to OLEDB and getting "Object Required"

1 Answers