Stored Procedures - First Attempt
 

I am trying to figure out how to call a stored procedure. I have entered the
following lines of code ....

Dim cmd As New Command
cmd.CommandType = adCmdStoredProc
Dim parm1 As Parameter
With parm1
.DataType = xlParamTypeInteger
.Name = "i_User_ID"
.Value = 1
End With
Dim parm2 As Parameter
With parm2
.Name = "i_Time_ID"
.DataType = xlParamTypeInteger
.Value = 136
End With
cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)
cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYY;User
ID=XXXXX;Data Source=ZZZZZZZ;Persist Security Info=True"
cmd.ActiveConnection = cnn
cmd.Execute

The code fails when I attempt to place something into my Parameter with the
following error "Object Variable or with block not set". Sounds like it
wants me to instatiate the object, but I can not use the "new" with
"Parameter" object.

So what am I doing wrong? How do I associate the Command Object with the
Connection Object....I was guessing "cmd.ActiveConnection".

Thanks in advance for your assistance!!!

Stored Procedures - First Attempt
 

This doesn't look like Excel to me.

Maybe you wanted to post in one of the Access newsgroups.

Jim Heavey wrote:

I am trying to figure out how to call a stored procedure. I have entered the
following lines of code ....

Dim cmd As New Command
cmd.CommandType = adCmdStoredProc
Dim parm1 As Parameter
With parm1
.DataType = xlParamTypeInteger
.Name = "i_User_ID"
.Value = 1
End With
Dim parm2 As Parameter
With parm2
.Name = "i_Time_ID"
.DataType = xlParamTypeInteger
.Value = 136
End With
cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)
cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYY;User
ID=XXXXX;Data Source=ZZZZZZZ;Persist Security Info=True"
cmd.ActiveConnection = cnn
cmd.Execute

The code fails when I attempt to place something into my Parameter with the
following error "Object Variable or with block not set". Sounds like it
wants me to instatiate the object, but I can not use the "new" with
"Parameter" object.

So what am I doing wrong? How do I associate the Command Object with the
Connection Object....I was guessing "cmd.ActiveConnection".

Thanks in advance for your assistance!!!

--

Dave Peterson

Stored Procedures - First Attempt
 

These objects are all available in VBA within Excel. Not sure why you are
suggesting that this is not Excel related....it is.

I have figured out that I need to specify ADODB.Parameter and this gets me
by the error that I was experiencing, but then it fails which I attempt to
load the parameters into the Command Object.

I get the error "object required" for the following commands

cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)

The code to create parm1, looks like the following:

Dim parm1 As New ADODB.Parameter
With parm1
.Type = adInteger
.Name = "i_User_ID"
.Value = 1
End With

When I hover over parm1, is see the value of "1" being displayed back to me.
Why does the cmd.Parameters.Append (parm1) fail?

Stored Procedures - First Attempt
 

I think, at the very least, you should be using ADO data types, not Excel.

So

.DataType = xlParamTypeInteger

should be

.DataType = adInteger

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jim Heavey" wrote in message
...
I am trying to figure out how to call a stored procedure. I have entered
the
following lines of code ....

Dim cmd As New Command
cmd.CommandType = adCmdStoredProc
Dim parm1 As Parameter
With parm1
.DataType = xlParamTypeInteger
.Name = "i_User_ID"
.Value = 1
End With
Dim parm2 As Parameter
With parm2
.Name = "i_Time_ID"
.DataType = xlParamTypeInteger
.Value = 136
End With
cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)
cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYY;User
ID=XXXXX;Data Source=ZZZZZZZ;Persist Security Info=True"
cmd.ActiveConnection = cnn
cmd.Execute

The code fails when I attempt to place something into my Parameter with
the
following error "Object Variable or with block not set". Sounds like it
wants me to instatiate the object, but I can not use the "new" with
"Parameter" object.

So what am I doing wrong? How do I associate the Command Object with the
Connection Object....I was guessing "cmd.ActiveConnection".

Thanks in advance for your assistance!!!

Stored Procedures - First Attempt
 

Did you declare the command as adodb?

Dim cmd As New ADODB.Command


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jim Heavey" wrote in message
...
These objects are all available in VBA within Excel. Not sure why you are
suggesting that this is not Excel related....it is.

I have figured out that I need to specify ADODB.Parameter and this gets me
by the error that I was experiencing, but then it fails which I attempt to
load the parameters into the Command Object.

I get the error "object required" for the following commands

cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)

The code to create parm1, looks like the following:

Dim parm1 As New ADODB.Parameter
With parm1
.Type = adInteger
.Name = "i_User_ID"
.Value = 1
End With

When I hover over parm1, is see the value of "1" being displayed back to
me.
Why does the cmd.Parameters.Append (parm1) fail?