[TriLUG] OT (not Linux) Database Help Request

[Tim Jowers]

I trhinki VB has some print lasterror or something. Probably not
loading the driver or having the right run-time env needed for what
you want to do I'd guess as VBA is crappy like that.

HTH,
TimJowers

On Mon, Nov 1, 2010 at 12:00 PM,  <matt at noway2.thruhere.net> wrote:
> 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]
> --
> This message was sent to: timjowers <timjowers at gmail.com>
> To unsubscribe, send a blank message to trilug-leave at trilug.org from that address.
> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
> Unsubscribe or edit options on the web  : http://www.trilug.org/mailman/options/trilug/timjowers%40gmail.com
> TriLUG FAQ          : http://www.trilug.org/wiki/Frequently_Asked_Questions
>