On Jan 8, 3:56*pm, JLGWhiz wrote:
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" wrote:
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- Hide quoted text -

- 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