Excel ADO recordset

G

Guest

One problem I worked through was setting the command
values to numbers. example
objCmd.CommandType = 4 - instead of adCmdStoredProc...

The stored proc runs, but I can't open the recordset.
I tried all options.
The recordset state is always = 0
I now the record count from the Execute matches.

<operation is not allowed when the object is closed>

Appreciative of help. Jeff

Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "AMTexcelProjJWRTest550"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn

rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1

Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd

rsData.Open objCmd

testState = rsData.State

If Not rsData.EOF Then
' Dump the contents of the recordset onto the
worksheet.
Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(objConn.State And adStateOpen) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And adStateOpen) Then
rsData.Close
Set rsData = Nothing


End Sub
 
J

Jake Marx

Hi,

You're doing too much with the Recordset object.
Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn
rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1
Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd
rsData.Open objCmd
Click to expand...

All of this can be done in 2 statements:

Dim rsData As Object
Set rsData = objCmd.Execute

This is more efficient than creating the Recordset and opening it anyway.
You'll have to get rid of the adStateOpen as well (replace it with it's
value, which is 1). If you put Option Explicit at the top of your module,
you will get errors on undeclared variables instead of finding out the hard
way that you misspelled something or left in an undeclared constant.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
G

Guest

I changed the code and still cannot open the recordset.
< operation is not allowed when the object is closed >

Should I define the recordset parameters?
thanks, Jeff

****************************************************
Option Explicit
Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "StoredProc"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = objCmd.Execute

If Not rsData.EOF Then

Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(Conn.State And 1) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And 1) Then rsData.Close
Set rsData = Nothing

End Sub
*****************************************************
-----Original Message-----
Hi,

You're doing too much with the Recordset object.
Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn
rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1
Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd
rsData.Open objCmd
Click to expand...

All of this can be done in 2 statements:

Dim rsData As Object
Set rsData = objCmd.Execute

This is more efficient than creating the Recordset and opening it anyway.
You'll have to get rid of the adStateOpen as well (replace it with it's
value, which is 1). If you put Option Explicit at the top of your module,
you will get errors on undeclared variables instead of finding out the hard
way that you misspelled something or left in an undeclared constant.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


[email protected] said:
One problem I worked through was setting the command
values to numbers. example
objCmd.CommandType = 4 - instead of adCmdStoredProc...

The stored proc runs, but I can't open the recordset.
I tried all options.
The recordset state is always = 0
I now the record count from the Execute matches.

<operation is not allowed when the object is closed>

Appreciative of help. Jeff

Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "AMTexcelProjJWRTest550"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn

rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1

Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd

rsData.Open objCmd

testState = rsData.State

If Not rsData.EOF Then
' Dump the contents of the recordset onto the
worksheet.
Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(objConn.State And adStateOpen) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And adStateOpen) Then
rsData.Close
Set rsData = Nothing


End Sub
Click to expand...

.
Click to expand...
 
J

Jake Marx

Hi Jeff,

What line are you getting the error on?

A few things:

1) I typically use adDBTimeStamp (value 135) when passing date/time
parameters to SQL Server. You do not need to fill in the Size parameter for
anything but text-related parameters - you can leave it blank in this case.
FWIW, when I try adDBDate (value 133) like you use in your code, it fails
for me.

2) You can simplify the Parameter code since you're not looking for its
value on return. Instead of:
Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam
Click to expand...

you can use:

With objCmd
.Parameters.Append .CreateParameter("@ForecastBeginDate", _
135, , CDate(DateStr))
End With

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


I changed the code and still cannot open the recordset.
< operation is not allowed when the object is closed >

Should I define the recordset parameters?
thanks, Jeff

****************************************************
Option Explicit
Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "StoredProc"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = objCmd.Execute

If Not rsData.EOF Then

Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(Conn.State And 1) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And 1) Then rsData.Close
Set rsData = Nothing

End Sub
*****************************************************
-----Original Message-----
Hi,

You're doing too much with the Recordset object.
Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn
rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1
Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd
rsData.Open objCmd
Click to expand...

All of this can be done in 2 statements:

Dim rsData As Object
Set rsData = objCmd.Execute

This is more efficient than creating the Recordset and opening it
anyway. You'll have to get rid of the adStateOpen as well (replace
it with it's value, which is 1). If you put Option Explicit at the
top of your module, you will get errors on undeclared variables
instead of finding out the hard way that you misspelled something or
left in an undeclared constant.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


One problem I worked through was setting the command
values to numbers. example
objCmd.CommandType = 4 - instead of adCmdStoredProc...

The stored proc runs, but I can't open the recordset.
I tried all options.
The recordset state is always = 0
I now the record count from the Execute matches.

<operation is not allowed when the object is closed>

Appreciative of help. Jeff

Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "AMTexcelProjJWRTest550"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn

rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1

Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd

rsData.Open objCmd

testState = rsData.State

If Not rsData.EOF Then
' Dump the contents of the recordset onto the
worksheet.
Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(objConn.State And adStateOpen) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And adStateOpen) Then
rsData.Close
Set rsData = Nothing


End Sub
Click to expand...

.
Click to expand...
Click to expand...
 
G

Guest

I tried (value 135) and still get the error just after the
execute. The recordset state is never opened. It always
has a value of zero.
thanks for the help. I'll keep trying.
-----Original Message-----
Hi Jeff,

What line are you getting the error on?

A few things:

1) I typically use adDBTimeStamp (value 135) when passing date/time
parameters to SQL Server. You do not need to fill in the Size parameter for
anything but text-related parameters - you can leave it blank in this case.
FWIW, when I try adDBDate (value 133) like you use in your code, it fails
for me.

2) You can simplify the Parameter code since you're not looking for its
value on return. Instead of:
Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam
Click to expand...

you can use:

With objCmd
.Parameters.Append .CreateParameter ("@ForecastBeginDate", _
135, , CDate(DateStr))
End With

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


[email protected] said:
I changed the code and still cannot open the recordset.
< operation is not allowed when the object is closed >

Should I define the recordset parameters?
thanks, Jeff

****************************************************
Option Explicit
Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "StoredProc"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = objCmd.Execute

If Not rsData.EOF Then

Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(Conn.State And 1) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And 1) Then rsData.Close
Set rsData = Nothing

End Sub
*****************************************************
-----Original Message-----
Hi,

You're doing too much with the Recordset object.

Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn
rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1
Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd
rsData.Open objCmd

All of this can be done in 2 statements:

Dim rsData As Object
Set rsData = objCmd.Execute

This is more efficient than creating the Recordset and opening it
anyway. You'll have to get rid of the adStateOpen as well (replace
it with it's value, which is 1). If you put Option Explicit at the
top of your module, you will get errors on undeclared variables
instead of finding out the hard way that you misspelled something or
left in an undeclared constant.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


(e-mail address removed) wrote:
One problem I worked through was setting the command
values to numbers. example
objCmd.CommandType = 4 - instead of adCmdStoredProc...

The stored proc runs, but I can't open the recordset.
I tried all options.
The recordset state is always = 0
I now the record count from the Execute matches.

<operation is not allowed when the object is closed>

Appreciative of help. Jeff

Public Sub ExecProc150()

Dim DateStr As Date
DateStr = #3/1/2004#

Dim szConnect As String
szConnect = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Database;Integrated
Security=SSPI"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open szConnect

Dim objCmd As Object
Set objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "AMTexcelProjJWRTest550"
objCmd.CommandType = 4
Set objCmd.activeconnection = Conn

Application.ODBCTimeout = 0

Dim objParam As Object
Set objParam = CreateObject("ADODB.Parameter")

Set objParam = objCmd.CreateParameter
("@ForecastBeginDate", 133, 1, 8, CDate(DateStr))
objCmd.Parameters.Append objParam

Dim rsData As Object
Set rsData = CreateObject("ADODB.RecordSet")
Set rsData.activeconnection = Conn

rsData.cursortype = 1
rsData.locktype = 2
rsData.CursorLocation = 1

Set rsData = objCmd.Execute(AMT, objParam)
Set rsData.Source = objCmd

rsData.Open objCmd

testState = rsData.State

If Not rsData.EOF Then
' Dump the contents of the recordset onto the
worksheet.
Sheet1.Range("A1").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up our ADO objects.
If CBool(objConn.State And adStateOpen) Then Conn.Close
Set Conn = Nothing
If CBool(rsData.State And adStateOpen) Then
rsData.Close
Set rsData = Nothing


End Sub

.
Click to expand...
Click to expand...

.
Click to expand...
 
J

Jake Marx

Jeff,

The only other thing I can think of is that maybe your Stored Procedure is
not returning a recordset as expected? Can you execute the SP in Query
Analyzer and get a resultset?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


I tried (value 135) and still get the error just after the
execute. The recordset state is never opened. It always
has a value of zero.
thanks for the help. I'll keep trying.
Click to expand...

<snip>
 
G

Guest

Yes, the proc runs.
I can also see the record count change in the Execute line.
I know it is running the SP. Do you know about the parms -
adclient, adserver and other parms that should be set for
the recordset. Are you sure these don't need to be set
correctly. Maybe that's problem.
-----Original Message-----
Jeff,

The only other thing I can think of is that maybe your Stored Procedure is
not returning a recordset as expected? Can you execute the SP in Query
Analyzer and get a resultset?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


[email protected] said:
I tried (value 135) and still get the error just after the
execute. The recordset state is never opened. It always
has a value of zero.
thanks for the help. I'll keep trying.
Click to expand...

<snip>

.
Click to expand...
 
J

Jake Marx

Hi Jeff,

Hmm...You shouldn't have to modify any of the recordset properties to get
this to work. You could try setting the cursorlocation of the connection
object to adUseClient to see if that makes a difference.

Feel free to email me the workbook directly if you'd like me to try and
troubleshoot this more. (mvp (at) longhead [dot] com)

(make sure you mask the db username/pwd before sending)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Yes, the proc runs.
I can also see the record count change in the Execute line.
I know it is running the SP. Do you know about the parms -
adclient, adserver and other parms that should be set for
the recordset. Are you sure these don't need to be set
correctly. Maybe that's problem.
-----Original Message-----
Jeff,

The only other thing I can think of is that maybe your Stored
Procedure is not returning a recordset as expected? Can you execute
the SP in Query Analyzer and get a resultset?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


I tried (value 135) and still get the error just after the
execute. The recordset state is never opened. It always
has a value of zero.
thanks for the help. I'll keep trying.
Click to expand...

<snip>

.
Click to expand...
Click to expand...
 
G

Guest

I can now bring the data from SQL to Excel.
The problem was that I had a temp table coded in the
stored proc. I removed the temp table and all is well.
-----Original Message-----
Hi Jeff,

Hmm...You shouldn't have to modify any of the recordset properties to get
this to work. You could try setting the cursorlocation of the connection
object to adUseClient to see if that makes a difference.

Feel free to email me the workbook directly if you'd like me to try and
troubleshoot this more. (mvp (at) longhead [dot] com)

(make sure you mask the db username/pwd before sending)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


[email protected] said:
Yes, the proc runs.
I can also see the record count change in the Execute line.
I know it is running the SP. Do you know about the parms -
adclient, adserver and other parms that should be set for
the recordset. Are you sure these don't need to be set
correctly. Maybe that's problem.
-----Original Message-----
Jeff,

The only other thing I can think of is that maybe your Stored
Procedure is not returning a recordset as expected? Can you execute
the SP in Query Analyzer and get a resultset?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


(e-mail address removed) wrote:
I tried (value 135) and still get the error just after the
execute. The recordset state is never opened. It always
has a value of zero.
thanks for the help. I'll keep trying.

<snip>

.
Click to expand...
Click to expand...

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL not importing records from ACCDB 2
ADO access to excel workbook on website 1
SQL String 1
Follow up question. Get value of closed file. 5
Problem when copying a range from closed workbooks (ADO) 4
ADODB CopyFromRecordset problem 0
"acessor flags" creating pivot table from recordset 1
Help establishing ADO Connection 13

Top