Manglemender
Member
If it helps, The VBA code below is taken from Excel but it connects to a Database via ODBC and executes a stored procedure that returns a recordset.
Code:
Sub Fetch_TimeSeries_By_LocalID(myLocalID As Long)
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Dim mySheet, myFileName, strTemp As String
On Error GoTo ErrHandler
If myConfig.DSN = "" Then
ConfigOK = Read_Config
End If
myFileName = ActiveWorkbook.Name
mySheet = ActiveSheet.Name
Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."
Set WSP1 = Worksheets("Time Series Data")
WSP1.Activate
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(7, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents
' Log into our SQL Server, and run the Stored Procedure
con.Open myConfig.DSN, myConfig.UName, myConfig.PWord
cmd.ActiveConnection = con
Dim prmLocalID As ADODB.Parameter
'Dim myLocalID As Integer
'myLocalID = CInt(Range("C2").Value)
' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("myLocalID", adBigInt, adParamInput, 10, myLocalID)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "Return_TimeData_By_LocalID"
Set rs = cmd.Execute(, , adCmdStoredProc)
' Copy the results to cell B7 on the first Worksheet
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
Set WSP1 = Nothing
Workbooks(myFileName).Worksheets(mySheet).Activate
Application.StatusBar = "Data successfully updated."
Exit Sub
ErrHandler:
'rs.Close
Set rs = Nothing
Set cmd = Nothing
'con.Close
Set con = Nothing
Workbooks(myFileName).Worksheets(mySheet).Activate
Application.StatusBar = "VBSub:Fetch_TimeSeries_By_LocalID() error " & Err.Number & Err.Description
Err.Clear
End Sub