[TriLUG] OT (not Linux) Database Help Request
timjowers at gmail.com
Tue Nov 2 02:35:13 EDT 2010
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.
On Mon, Nov 1, 2010 at 12:00 PM, <matt at noway2.thruhere.net> wrote:
> 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:
> 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
> '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
> End Sub
> 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
More information about the TriLUG