Re: Using ADODB.Recordset

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <DanThorman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8D306FE3-DA96-44AA-A0A7-85202F18EEDC@xxxxxxxxxxxxxxxx
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 work*** 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


.

Quantcast