Prev: Conversion from 123 to Excel
Next: Shortcut key and UserForm
From: Steve Haack on 20 Jan 2010 09:23
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
From: Jeff on 20 Jan 2010 10:23
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


"Steve Haack" wrote:

> 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
 | 
Pages: 1
Prev: Conversion from 123 to Excel
Next: Shortcut key and UserForm