VBA/SQL openrecordset(strSQL) Error 3001

I am trying to run a query on a database server (Oracle 10g) using VBA/Excel and to display the results in a excel table. I have created the ODBC connection (read-only) and tested the connection. I works fine if I import data as a data source in excel (I am able to filter through a query as well) However my VBA code is giving me Error 3001

Sub Test()

Dim cnn As ADODB.Connection开发者_高级运维
Dim canConnect As Boolean
Dim rs As Recordset
Dim strSQL As String

Set cnn = New ADODB.Connection
cnn.Open "DSN=blah;Uid=blah;Pwd=blah"

strSQL = "select job_start_dttm, job_end_dttm from c_job"
Set rs = cnn.openrecordset(strSQL)
ActiveCell = rs(0)

End Sub

I get Error 3001 - Arguemnts are of worng type, are out of acceptable range, or are in confilct with one another

The query itself runs fine in SQL developer. Thanks

edit: The error is on "Set rs = cnn.openrecordset(strSQL)" line

---

Try:

Sub Test()
Dim cnn As New ADODB.Connection
Dim canConnect As Boolean
Dim rs As New ADODB.Recordset
Dim strSQL As String

cnn.Open "DSN=blah;Uid=blah;Pwd=blah"

strSQL = "select job_start_dttm, job_end_dttm from c_job"
rs.Open strSQL, cnn
ActiveCell = rs(0)

End Sub

You seem to be mixing up a little DAO with your ADODB. You could have used Execute, but the above should suit.

---

Try qualifying the type name of rs with the ADODB prefix to make sure it is not being defined as the built-in Access Recordset object type instead.

Dim rs As ADODB.Recordset

Edit:

You will also need to use the ADO .Execute command instead of the DAO .OpenRecordset:

Set rs = cnn.Execute("...")