Parameter table within Access 2

[MwTV]

Producing 4 reports per month by importing data into Excel (Using Microsoft Query) from tables within a Microsoft Access 2002 database. The tables within the MS Access database are created with make-table queries.

Each of the make-table queries require three or more parameters. For example, the August 2007 Roster
Report have the following parameters - "Begin_Date >= 8/01/07", "End_Date <= 8/31/07" and "Annual_Review_Dt >= 8/1/07."

The parameters for the September 2007 Roster Report will be
"Begin_Date >= 9/01/07", "End_Date <= 9/30/07" and "Annual_Review_Dt >= 9/1/07."

Questions/Comments:

1. Can I store parameters in an Access table and have the make-table queries "automatically" kick off
during the night and "pull" in the correct value for the parameter? This way, I would not have to
manually enter the parameters?

For example:

Month------Begin_Date--End_Date--Annual_Review_Dt
Jan--------1/01/07-----1/31/07---1/01/07--------


The other option is to create 48 queries (4 for each month in a year) and "hard-code" the dates and not
worry about entering parametes.

2. If I create the 48 queries, any insight as to how I would set up vba code to "conditionally run"
the queries depending on the month that the query was run? In other words, if a scheduler kicks off
during September 2007, then, the query titled "qrySeptember" should be run. If the scheduler kicks off during October 2007, then the query titled "qryOctober" should be run.

Is this doable?

Thanks in advance for any insight.

 

[orna]

You can insert the patrameters like this:
The last date of the month:
DateSerial(Year(Date()),Month(Date())+1,0)
The first date of the month:
DateSerial(Year(Date()),Month(Date()),1)

 

[jrbarnett]

Although Orna is right in that you can use formulae to put in the correct value, using a parameters table is certainly feasible.

There are two ways of approaching this:

1. A dedicated column per parameter.
a) Create a table tblParameters with a column for each parameter. Give the column the data type of the parameter type. Create a dummy field as a primary key.
b) Enter the parameter values into the record as needed.

So for example you would have:

PKValue, BeginDate, EndDate, AnnualReviewDate

c) Put code in your startup routines to check that there is exactly one row of data of data in this table and throw a critical error if this is not the case then terminate, with a note to contact IT Helpdesk or whoever.

d) In each query where this is needed, add this table and use the values of the columns in b) where you would use them.
For example:
MyField.StartDate >= tblParameters.BeginDate


Second approach.
This is a more long term approach, but because you are using text fields for everything, validating data becomes more difficult. On the other hand, you aren't limited to 254 columns as with the first (max fields per table minus 1 for the "primary key" value).

a) Create a table tblParameters with columns:
ParameterName - Text Primary Key Not Null
ParameterValue - Text Not Null

b) You then add one row for each parameter: BeginDate, EndDate, etc and enter the parameter value. Long term, you could also add a parameter type (T = text, D = Date/Time, N = Numeric etc) and then use this to validate entered data against on a parameters maintenance form.

c) In your queries where you need this you need to create a specific instance of the parameters table with a where clause limited to your parameter name. For example:

SELECT *
FROM mytable INNER JOIN tblPArameters BegDate On MyTable.Start_Date >= BegDate.PArameterValue
INNER JOIN tblParameters EndDate On myTable.End_Date <= EndDate.ParameterValue
WHERE BegDate.ParameterName = "StartDate" And EndDate.ParameterName = "EndDate"

Disadvantages: Validating specific types of data and range values is harder as you have to manully code and enforce validation rules (rather than have them at a table level), but on the plus side it does give you a lot of future expansion room.
You also require a named alias for each parameter to be used in each query, so 4 parameters = 4 instances of same table.

John

 

[MwTV]

Note, the make-table queries are all based on pass-through queries. The pass-through queries are extracting data from a sql server database.

I am not allowed to create stored procedures.

I will try all of the aforementioned options.

Currently, I have to manually initiate the make-table queries with some of the queries taking several hours to run.

Ideally, I would like to sort of queue up all of the make-table queries in VBA code and kick-off prior to leaving the office.

For example;

Sub

qryMakeTable1

qryMakeTable2

qryMakeTable3

End Sub


The other option that will incorporate some of the suggestions include the use of a scheduler to kick off this process.

For example, if I set up the scheduler to run on a daily basis, is it possible for the scheduler to "realize" the first day and the last day of the month and incorporate the correct parameter?


Any further insight is greatly appreciated.

 

[MwTV]

Currently, I am setting up the parameter table in MS Access.

I assume that I can use this to insert the parameters into the passthrough queries.

 

[jrbarnett]

Yes - you will need to build the SQL for the pass through queries based on your data - if its possible to store this parameters table in SQL server directly then that would be better as you wouldn't be joining a remote table to a local one, which would have an adverse affect on your query performance.

John

 

[MwTV]

Unfortunately, the parameter table will be a local table.

I don't have rights to store the parameter table in sql server.

 

[jrbarnett]

Could you ask your DBAs to set up the SQL server parameter table/stored procedures?

John

 

[MwTV]

DBA will not setup.

Therefore, it appears that I have to create separate query for each month with the parameters hardcoded into the query.

Further, maybe I can setup the make-table queries in one database and have a separate database just for the tables that were created from the make-table queries.

Finally, I will attempt to setup all of the make table queries within vba code to "kick off" on a scheduled basis.

Thanks in advance for any additional insight.