Can Access Automate Queries similar to triggers or Stored Procedur

[Guest]

I work with SQL Server. I would typically do this with a stored procedure,
but since i don't know much about Access, i don't know how to do this. I'm
just trying to get some clues before i get started on this thing and figure
out i can't do what i need.

In this small database, i have about 4 tables. Department, Employee,
VacationTime, SickTime

Currently payroll is using a manual system to calculate all this stuff and
it is riddled with errors.

1) When our fiscal year begins, each employee is given 2 personal days of 8
hours each. I want the database to add these hours for all employees
automatically.

2) On the anniversary of his hire date, each employee accrues vacation time
based on years served.

- 1 to 5 years, 80 hours
- 6 to 10 years, 120 hours
- 11 years or more, 160 hours

I would like Access to do this automatically so payroll doesn't have to
remember each month.

3) Sick leave accrual has to be added also. Every 2 weeks our hourly staff
accrues 4 hours sick leave. Salaried employees accrue 8 hours per month of
sick leave.

I don't think payroll should have to run a query to do this every single
month. What if staff tries to pull up their time before payroll runs the
query?

Don't worry about simultaneous users. I'm aware of that.

I'm assuming I will need some if statements and all that kind of stuff. I'm
not asking anyone to do the programming. I'm simply asking what i need to
know to get this done so i can read up on it.

Thanks guys!

 

[Guest]

Access does not have stored procedures, so you wont be able to do it in a
totally automated fashion like you are used to. It will take some update
queries to apply the hours based on the business rules.

If you want it to happen without a user having to initiate it, you might
search the groups for detailed info. Since I have never done this, I can't
give the detail, but the concept is to use a scheduler to kick off an Access
App that can do it off hours. When an mdb is opened and it has a Macro named
Autoexec, it will run. Use the RunCode action, and write some VBA to check
dates and execute the queries, then close itself.

 

[david epsom dot com dot au]

Which ever you choose, the starting point is one or a
number of update queries which check the dates and
append to or update the VacationTime and SickTime Tables.

Then you need to decide how and when you want to trigger
the script, and where you want to store it.

Access/Jet/DAO/ADO has a thing called a 'stored procedure',
but it can only have one SQL command in it. If you work on
your SQL, you should be able to put all of this into one
update query, and you would be able to run this from a
scheduler or script.

But you wouldn't really need to put everything into one update
query, because your scheduler would be set to run a script,
and your script could contain a number of actions, just like
a SQL Server stored procedure would. It just means that your
logic is split between the script and the SQL, instead of
all in one place, in a TSQL script.

Alternatively, Access (the user interface program, not
jet, not ado, not dao, not the database engine) has
macro's, so you can script a number of actions, and
VBA, which works like VB script and allows you to
script a number of actions.

So you could store your script in Access, just like
you would store your TSQL script in SQL Server.

The disadvantage of storing the script in Access is that
you would have to start Access - the massive office user
interface program - just to run your stored procedure.

Of course, you might decide to run the script when
you start Access anyway, instead of using a scheduler.
If your payroll people use the payroll program every
day, you can put a start-up or close-down script into
the payroll program, so that it does this update.

(david)