getting error message 3704 when running excel vba

[TG]

Hi!

I have the following code that gets start date from a cell, end date
from another cell and previous date from a thrid cell. Then it runs a
stored proc off SQL Server with those 3 parameters and should spit the
recordset outon another worksheet.

Sub Billing_Report()


'Clear previous contents
Sheet1.Range("A9:AZ5000").Select
Selection.ClearContents


Sheet1.Range("C3").Value = "(for the period of " &
Sheet2.Range("E10").Value & " to " & Sheet2.Range("E20").Value & ")"


'release the selection from the previous step
Sheet1.Range("E5").Select


'initialize variables with data in those cells
Start_Date = Sheet2.Range("E10").Value
End_Date = Sheet2.Range("E20").Value
Prev_Date = Sheet2.Range("E30").Value



'checking if end_date happens earlier than start_date
If End_Date < Start_Date Then
MsgBox "Start Date must be earlier than End Date. Check your
selection and try again"
Worksheets("Selection Data").Activate
Exit Sub
End If


'checking if prev_date happens after than start_date
If Prev_Date > Start_Date Then
MsgBox "Previous Date must be earlier than Start Date. Check
your selection and try again"
Worksheets("Selection Data").Activate
Exit Sub
End If


'Declare the variables
Dim conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim strSQL As String, ConnStr As String


'Set the connection string
ConnStr = "Provider=SQLOLEDB;Initial Catalog=master; Data Source=dr-ny-
sql001; " & _
"INTEGRATED SECURITY=sspi;"


'create sql string for stored proc
strSQL = "exec usp_DR_Monthly_Billing_Report '" & Start_Date & "', '"
& End_Date & "', '" & Prev_Date & "'"

'MsgBox strSQL


'create and open the connection and recordset
Set conn = New ADODB.Connection
Set Rs = New ADODB.Recordset
conn.CommandTimeout = 0
conn.Open ConnStr
Rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly


'Instantiate the Recordsetobject and execute the question.
Set Rs = conn.Execute(strSQL)


'If conn.State = adStateClosed Then
'MsgBox "connection is closed"
'Else
'MsgBox "connection is open"
'End If


'If Rs.State = 1 Then
'MsgBox "Recordset is closed"
'Else
'MsgBox "Recordset is open"



If Not Rs.EOF Then
MsgBox "The report has been populated with data from " &
Start_Date & " to " & End_Date
Else
MsgBox "There is no data for the period from " &
Start_Date & " to " & End_Date
End If


'Dump the records into the worksheet.
Sheet1.Range("A9").CopyFromRecordset Rs


'Release objects from memory.
If Rs.State = 1 Then Rs.Close
Set Rs = Nothing
conn.Close
Set conn = Nothing



' Exit the macro so that the error handler is not executed.
Worksheets("Selection Data").Activate


'WrongDate:

'MsgBox "End Date cannot happen before Start Date. Please check
your selection and try again."



'Errhandler:

'MsgBox "Error # " & Err & " : " & Error(Err) & ". Please make a
note of this error message and contact Technical Support."

' Exit the macro so that the error handler is not executed.
'Exit Sub

End Sub



I am getting runtime error 3704 application defined or object defined
error
when hitting the following line:

'Dump the records into the worksheet.
Sheet1.Range("A9").CopyFromRecordset Rs


Any help will be greatly appreciated.

Thanks!

JT

 

[TG]

Moon,

I tried what you suggested and still get the sane error.

Any other ideas are welcome.

Thanks!


JT

 

[JLGWhiz]

You have released Rs from memory a few lines up and the dump to sheet cannot
execute if Rs = Nothing. You need to do the dump before settint Rs = Nothing.

 

[JLGWhiz]

I think I read that wrong. You do execute the dump first. So there must be
another disconnect somewhere. The syntax does not appear to be the problem.

 

[TG]

I think I read that wrong.  You do execute the dump first.  So there must be
another disconnect somewhere.  The syntax does not appear to be the problem.






























- Show quoted text -

JLGWhiz,

I have tested that the connection is open before dumping the data and
it is open.

Any other possible ideas?

Thanks a lot!

JT