[TriLUG] OT (not Linux) Database Help Request

[matt at noway2.thruhere.net]

Group,

For the last couple of days, I have been unsuccessful in trying to figure
something out that shouldn't be that difficult and I am hoping that
someone can tell me what I am missing.  I marked the tread OT because it
is a Windows based question, which is what they use at work.

I am trying to write an Excel VBA script that upon execution will
establish a connection to SQL Server, execute a stored procedure and then
dump the result into the Excel spreadsheet.  If I use the connection
wizard, I get the correct results, but I would rather execute the query on
command.  I seem to be able to establish the connection and (I think)
execute the query, but I can't seem to get the return data into excel.

I have tried several examples from the MS and other how to docs, but most
of those return exceptionally helpful error messages along the lines of
"error 424, object expected".  I think the answer has something to do with
a "record set" but I haven't found anything that works yet. If anyone can
help point me in the right direction, I would greatly appreciate it.

The partially working code is shown below:

[code]
Private Sub CommandButton1_Click()

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection

    cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=reporting;Password=password;" & _
            "Initial Catalog=Historian;Data Source=decwhawk03;Use
Procedure for Prepare=1;" & _
            "Auto Translate=True;Packet Size=4096;Workstation
ID=ES-CHW-LAPTOP19;" & _
            "Use Encryption for Data=False;Tag with column collation when
possible=False"

    'Import by using OPENDATASOURCE.
    strSQL = "declare @today as datetime;" & _
                "set @today = GETDATE();" & _
                "set @today = @today - 90;" & _
                "declare @yesterday as datetime;" & _
                "set @yesterday = @today - dbo.Time(0,1,0);" & _
                "declare @todayI as bigint;" & _
                "set @todayI = dbo.ToBigInt(@today);" & _
                "declare @yesterdayI as bigint;" & _
                "set @yesterdayI = dbo.ToBigInt(@yesterday);" & _
                "Execute Historian.dbo.GetInterpolatedSamples
'Bridge.B133_TLB' , @yesterdayI , @todayI , 1000000,
'Bridge IO'"

    'set myData = cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

    cn.Close
End Sub
[/code]