Using ADODB.Recordset

[Dan Thorman]

Hello all...

I wrote the following Macro to try to figure out how to use ADODB.Recordset.
Basically, I am grabbing a one row recordset, and i would like to assign the
values of that recordset to cells in my Excel worksheet based on the column
name. Unfortunately, I'm getting an error when I try to open the recordset
(the two assignments below the recordset were my attempts at figuring out if
i could assign a value based on either the position of the column or the name
of the column). If anybody could tell me what i'm doing wrong, that would be
outstanding.

Thanks in advance!

Sub test()

Dim DealID As Long

DealID = Range("Inputs!DealID")

sSql = "select (case when project_type_id in (2, 11) then 'Conversion' " &
vbCrLf
sSql = sSql & "when project_type_id in (3,7,9) then 'New Build'" & vbCrLf
sSql = sSql & "when project_type_id = 1 then 'Adaptive Re-Use'" & vbCrLf
sSql = sSql & "when project_type_id = 6 then 'Change of Ownership'" & vbCrLf
sSql = sSql & "when project_type_id in (5,8) then 'Re Up'" & vbCrLf
sSql = sSql & "when project_type_id is NULL then 'Data Not Found in ABCD'
end) Project_Type, " & vbCrLf
sSql = sSql & "deal_name as Deal_Name from abcd.deal where deal_id = " &
DealID

Dim objRS As ADODB.Recordset
Dim objField As ADODB.Fields

Set objRS = New ADODB.Recordset

objRS.Open sSql, ActDB, adOpenKeyset, adLockReadOnly, adCmdText
objRS.MoveFirst

Range("Inputs!ProjectType") = objRS.Fields(4).Value
Range("Inputs!DealName") = objRS.Fields("Deal_Name").Value

End Sub

 

[Bob Phillips]

You have to connect to your database first. Here is an example

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dan Thorman]

Sorry, I forgot to mention that in the code i wrote above, ActDB is a
globally defined active database connection.

 

[Dan Thorman]

Ok, so here's what i have now. at the line where i open the recordset, i'm
getting the following error, for which the "help" button provides no details:
"Run-time error '3001': Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another". A Google search of
that particular error brought back nothing i could figure out how to apply to
this situation.

Here's my code:

Sub test()

Dim DealID As Long, sSql As String
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object

DealID = Range("Inputs!DealID")

sSql = "select (case when project_type_id in (2, 11) then 'Conversion' " &
vbCrLf
sSql = sSql & "when project_type_id in (3,7,9) then 'New Build'" & vbCrLf
sSql = sSql & "when project_type_id = 1 then 'Adaptive Re-Use'" & vbCrLf
sSql = sSql & "when project_type_id = 6 then 'Change of Ownership'" & vbCrLf
sSql = sSql & "when project_type_id in (5,8) then 'Re Up'" & vbCrLf
sSql = sSql & "when project_type_id is NULL then 'Data Not Found in ABCD'
end) Project_Type, " & vbCrLf
sSql = sSql & "deal_name as Deal_Name from abcd.deal where deal_id = " &
DealID

Dim objField As ADODB.Fields

Set objRS = CreateObject("ADODB.Recordset")

objRS.Open sSql, ActDB, adOpenForwardOnly, adLockReadOnly, adCmdText
objRS.MoveFirst

Range("Inputs!ProjectType") = objRS.Fields(4).Value
Range("Inputs!DealName") = objRS.Fields("Deal_Name").Value

End Sub

 

[Bob Phillips]

Dan,

You don't say where you get the error.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dan Thorman]

Terribly sorry. My error is on the objRS.Open line:

objRS.Open sSql, ActDB, adOpenForwardOnly, adLockReadOnly, adCmdText

Oh, and by the way Bob, thanks for your help thus far, it is greatly
appreciated!

 

[Dan Thorman]

anybody have any ideas? unfortunately, i'm completely stumped :-(

 

[Robin Hammond]

What happens

1. If you try the query you are using in the rsopen statement directly in
the sql query analyser?
2. If you remove al the vbcrlf statements and replace them with spaces. They
are not needed but the spaces may be.
3. Is there something wrong ahead of Project_Type. Needs an 'as'?. Or SELECT
'Project_Type' = case when ....