Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using ADODB.Recordset
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Using ADODB.Recordset
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,726
Default Using ADODB.Recordset
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" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Using ADODB.Recordset
Sorry, I forgot to mention that in the code i wrote above, ActDB is a
globally defined active database connection.

"Bob Phillips" wrote:

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" wrote in message
...
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Using ADODB.Recordset
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" wrote:

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" wrote in message
...
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,726
Default Using ADODB.Recordset
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" wrote in message
...
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" wrote:

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" wrote in message
...
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





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Using ADODB.Recordset
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!

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Using ADODB.Recordset
anybody have any ideas? unfortunately, i'm completely stumped :-(

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 45
Default Using ADODB.Recordset
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 ....

--
Robin Hammond
www.enhanceddatasystems.com


"Dan Thorman" wrote in message
...
anybody have any ideas? unfortunately, i'm completely stumped :-(

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
ADODB.RecordSet Dan Thorman Excel Programming 6 January 30th 07 05:11 PM
ADODB Recordset problem Stefen Percoco Excel Programming 2 July 26th 04 06:31 PM
0 with ADODB Recordset Stefen Percoco Excel Programming 1 July 8th 04 09:54 PM
adodb.recordset with excel nate axtell Excel Programming 14 June 11th 04 01:32 PM
ADODB Recordset Seth[_3_] Excel Programming 0 August 5th 03 02:15 PM


All times are GMT +1. The time now is 09:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement