Here are some of the functions I use to get info from an ACCESS database.
I use ADO for several different queries, so I have the code broken up into 3
separate functions that (1) set up the Cmd object, (2) open the connection,
and (3) execute the query.
It will require changing the connection to connect to your SQL database, of
course. I don't know if more changes will be needed, but maybe it will give
you some ideas.
Function PricesForSpecifiedDate(DBName As String, TheDate As Date) As Variant
'uses parameter query stored in the database
Dim Cmd As ADODB.Command
Dim Param As ADODB.Parameter
Set Cmd = New ADODB.Command
Set Param = New ADODB.Parameter
With Cmd
.CommandText = "Prices_as_of"
.CommandType = adCmdStoredProc
Set Param = .CreateParameter("Target_Date", adDBDate, adParamInput)
Param.Value = TheDate
.Parameters.Append Param
End With
PricesForSpecifiedDate = GetAccessDataFromQuery(Cmd, DBName)
End Function
Function OpenConnection(dBaseName As String) As ADODB.Connection
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
With Cnxn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & XLDocDir & dBaseName
.Open
End With
Set OpenConnection = Cnxn
Set Cnxn = Nothing
End Function
Function GetAccessDataFromQuery(Cmd As ADODB.Command, _
DBName As String) As Variant
Dim Cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim v As Variant
ReDim v(0, 0)
Set Cnxn = OpenConnection(DBName)
Set rs = New ADODB.Recordset
Cmd.ActiveConnection = Cnxn
With rs
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open Cmd
'If (Not .BOF) And (Not .EOF) Then
If .RecordCount > 0 Then
v = .GetRows
Else
v(0, 0) = "No Records"
End If
.Close
End With
Cnxn.Close
Set Cnxn = Nothing
GetAccessDataFromQuery = v
End Function 'GetAccessDataFromQuery
