Querying Data from Access

  • Thread starter Thread starter Steve Haack
  • Start date Start date
S

Steve Haack

All,
I am using the charting features in Excel 2007 to build various charts from
data stored in Access. I want the user to be able to select the Region,
Country, or Site that he wishes to see data for, along with a range of dates.

I have queries in Access that will summarize the data the way I need it.
What I would like to know, is how do I "paramaterize" the queries so that I
can use the dates the user chooses, for example?

Thanks,
Steve
 
Here is a good example from EXCEL VBA 2003. for I think what you're wanting

Public Sub CallStoredProcedure()

Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" + _
"Persist Security Info=False;Initial Catalog=NorthwindCS;" + _
"Data Source=LAP800;Workstation ID=LAP800;"

Dim Command As Command
Set Command = New Command

Command.ActiveConnection = ConnectionString
Command.CommandText = "[Sales by Year]"
Command.CommandType = CommandTypeEnum.adCmdStoredProc

Dim BeginningDate As ADODB.Parameter
Dim EndingDate As ADODB.Parameter

Dim StartDate As Date
StartDate = #1/1/1995#

Dim EndDate As Date
EndDate = #1/1/2004#

Set BeginningDate = Command.CreateParameter("@Beginning_Date", _
DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , StartDate)

Set EndingDate = Command.CreateParameter("@Ending_Date", _
DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , EndDate)

Call Command.Parameters.Append(BeginningDate)
Call Command.Parameters.Append(EndingDate)

Dim Recordset As ADODB.Recordset
Set Recordset = Command.Execute

Call Sheet1.Range("A1").CopyFromRecordset(Recordset)

End Sub
 
Back
Top