CODEWITHSUNDEEP
sqlvba
Witcher
Witcher

Reputation: 69

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

Upvotes: 2

Views: 8568

Answers (2)

mellamokb
mellamokb

Reputation: 56779

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

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 1

Related Questions