CODEWITHSUNDEEP
vbaexceladodb
Rajat Solanki
Rajat Solanki

Reputation: 87

Excel VBA ADO Recordset - Issue with SELECT columns

I get

Runtime error '-2147467259 (80004005)': Unspecified Error

when I use the following code:

Set conn1 = CreateObject("ADODB.Connection")
conn1.Open strcon1
strquery3 = "SELECT * FROM TABLE_NAME"
Set rs3 = CreateObject("ADODB.Recordset")
Set rs3 = conn1.Execute(strquery3)

However the code works when I take lesser number of columns in my SELECT statement:

Set conn1 = CreateObject("ADODB.Connection")
conn1.Open strcon1
strquery3 = "SELECT COLUMN1, COLUMN2 FROM TABLE_NAME"
Set rs3 = CreateObject("ADODB.Recordset")
Set rs3 = conn1.Execute(strquery3)

The table contains total 17 columns. Is there a restriction of number of columns? If not, is there any reason why I couldn't get all the 17 columns in my recordset?

I'm connecting to Oracle DB. Driver used - "Microsoft ODBC for Oracle"

Upvotes: 1

Views: 2323

Answers (1)

Daniel Martínez Sarta
Daniel Martínez Sarta

Reputation: 771

Try this way:

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim cs As String
Dim sql As String


' Initialize objects
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

' Create connection string and query string
cs = "Provider=SQLOLEDB; Data Source=<YOURSERVER>; Initial Catalog=<YOURDATABSE>; User ID=<YOURUSER>; Password=<YOURPASSWORD>"

sql = "select column1, column2 from table"

' Open connection and execute query
con.Open cs
cmd.CommandType = adCmdText
cmd.ActiveConnection = con
cmd.CommandText = sql

Set rs = cmd.Execute

Then, you can access the column fields like:

Dim col1, col2 As String
col1 = rs.Fields("Column1").Value
col2 = rs.Fields("Column2").Value

Upvotes: 0

Related Questions