Saturday, January 07, 2006

Excel VBA, SQL Stored Procedures and Michelob Light

There is no difference between how Excel VBA passes parameters to stored procedures and any other VB based app.

Michelob Light on Saturday night will make me forget to leave a SPACE after the procedure name before the close quote though.

SQL="SP_MyStoredProc " & data1 & "," & data2 & ";" will work.
SQL="SP_MyStoredProc" & data1 & "," & data2 & ";" will not.

For future reference, stolen from Patrick Molloy, Microsoft Excel MVP, here's some basic Excel and SQL stuff to drink to:

Option Explicit
Property Get MyDatabase() As String
MyDatabase = ControlsA1
End Property
Property Get MyServer() As String
MyServer = ControlsA2
End Property

Sub LoadFromSQL()
Dim RST As ADODB.Recordset
Dim db As Connection
Dim SQL As String
Dim i As Long
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=" & MyServer & ";uid=;pwd=;database=" & MyDatabase & ";"
Set RST = New Recordset
RST.Open SQL, db, adOpenStatic, adLockOptimistic
' prepare active sheet
With RST
For i = 0 To .Fields.Count - 1
Cells(1, i + 1).Value = .Fields(i).Name
End With
Range("A2").CopyFromRecordset RST
Set RST = Nothing
Set db = Nothing
End Sub

No comments:

Post a Comment