Creating Jobs using scheduler in Access

[twychopen22]

Does anybody know how to schedule in access like sql. Like a "job" in sql server?

 

[FoFa]

Access does NOT have a job scheduler. We usually use the windows SERVER schedule Tasks to do it. You just need to make sure the Access program does not have any interface, and does not hang due to any errors.

 

[twychopen22]

Access does NOT have a job scheduler. We usually use the windows SERVER schedule Tasks to do it. You just need to make sure the Access program does not have any interface, and does not hang due to any errors.

I am not familiar with windows Server schedule task? Please tell me more...(I know im ignorant) and I am not sure about the access interface... Sweet symbol by the way!

 

[FoFa]

Windows servers (and XP also I think) has a task scheduler with a GUI interface. Pretty stright forward really.
From an Access point of view, you need to setup your program to run automatically. I usually use a AUTOEXEC macro that kicks off as soon as that DB is opened. The task scheduler you have to specify the MSACCESS.EXE and feed the path\AccessDB.MDB file name to it is pretty much all.

 

[twychopen22]

Windows servers (and XP also I think) has a task scheduler with a GUI interface. Pretty stright forward really.
From an Access point of view, you need to setup your program to run automatically. I usually use a AUTOEXEC macro that kicks off as soon as that DB is opened. The task scheduler you have to specify the MSACCESS.EXE and feed the path\AccessDB.MDB file name to it is pretty much all.

Ok, I am still confused. I have an expense table. The expense can be either Monthly, quarterly, semiannually, or annually. If the user creates an expense that is monthly, I want it to write a record automatically next month. How does this work? I am not familiar with this process at all.

 

[FoFa]

You need to sepeprate the scheduled part from the applicatiion part.
First decide what the application needs to do.
Next how is this to occure?
Just saying writing a record next month means nothing.
Do you want to have it write a record with an effective date of next month, or actually write a record next month? What is going to tell the application, it is next month, and it is time to write the record? You hae not supplied enough information so far to answer that.

 

[twychopen22]

You need to sepeprate the scheduled part from the applicatiion part.
First decide what the application needs to do.
Next how is this to occure?
Just saying writing a record next month means nothing.
Do you want to have it write a record with an effective date of next month, or actually write a record next month? What is going to tell the application, it is next month, and it is time to write the record? You have not supplied enough information so far to answer that.

Well, I have a form that the user enters the name of the expense, amount etc. and then the date it occurs. Say I have a an expense (a duplicate) on the 15th, I want a record to automatically be written next month on that day. (I would also have to write something that says: If date entered is 31 and the next month has only 30 then enter on 30. (or just have it automatically write any date over 28 to the 28th. this is all for expense reports, so the exact dates are not necessary (seeing that the smallest reoccurrance is monthly) I would want it to write with both an effective date of next month on that date of next month.

 

[FoFa]

Sounds like the data is already there. So why are you writing it to another table?

 

[twychopen22]

Sounds like the data is already there. So why are you writing it to another table?

Well, I just want to add the record to the table, not another table. I guess the thing to do would be to have 2 tables. Anyways, I need the records to reoccurr. I have the form and can create the expenses, but next month, they will not automatically show up as an expense of next month. That is what I want them to do.

 

[FoFa]

How about an append query you say run the first of every month, that adds nw records for anything outstanding from last month. You just have to figure out then what is outstanding.

 

[The_Doc_Man]

This is as much a design problem that can be handled by a divide-and-conquer approach.

1. Figure out the most frequent schedule you want to support.

2. Assume that you will be able to run things on that schedule.

3. Build a sequence of queries that do what you want if you run them.

4. Make a macro that runs the sequence of queries in proper order.

5. (Look up COMMAND LINE in MS Access Help for details.) Build an ICON that runs MS Access for you. Right-click the Icon to expose its properties. You will be able to modify the "implied" command line to include /X macroname and the name of the database. If you need a special workgroup, that is also a command-line capability. You can also name the actual database on the command line. (Yes, the command line could get very long.)

6. Now use Windows Task Scheduler (which has existed since Win 3.1) to run MS Access using your special icon.

The hardest part of this will be #3.

 

[twychopen22]

How about an append query you say run the first of every month, that adds nw records for anything outstanding from last month. You just have to figure out then what is outstanding.

I really appreicate your help and it has gotten me thinking. Thanks! What about this (this is borrowed from an earlier answer to another post of mine last week). Have 2 tables...One that the original data is selected on. Then another one that all subsequent expenses are written on. (otherwise, every month (or whenever the expense reoccurrs) the user would have to reselect the occurance again right) Use a query to show outstanding expenses, then an append query off of that. Then call the queries by the windows scheduler. This prolly is complicated but its a start would you agree? If you have any suggestions or ideas please let me know

 

[The_Doc_Man]

By George I think he's got it!

 

[twychopen22]

By George I think he's got it!

Hahahaha! nothin like a good quote to lift everybodies spirits. Thanks for ya'lls help I really appreciate it. Next comes the queries!!!!
Also, Doc Man, what did u mean
1. Figure out the most frequent schedule you want to support.

2. Assume that you will be able to run things on that schedule.

I will put access on the server and have it run automatically daily, is this the correct answer to those 2 points?

 

[twychopen22]

I have a make table/query, but your right I am having trouble... any ideas on how to, after I have created the expense, show up in the new table...I am not sure how to recognize the record as a new record. Right now I would add records, but also re-add all the old records already in the database

 

[twychopen22]

Alright, I have started to figure it out but I think I need some help. I want an if statement but I am not familiar with date functions. In an append query I want to look at my original table and add records if it is past a certain date.
For a monthly expense (entry date is 01/01/2006)

If (dateentered) month, +1
Then write the record.

 

[The_Doc_Man]

what did u mean

If you want something to update every month and something else to update every 3 months, you will be disappointed if you make the scheduled event occur every 3 months. Then, if you really wanted it to run at least once per month, your design should assume that is possible; otherwise, why try?

The "right" way to do it is to decide the lowest common denominator of your time interval and schedule this "thing" run at the rate implied by the lowest common denominator. In my example, that would be monthly.

Now the next trick is that the "every 3 months" update will run then, too. So what you have to do is make the data and query combination smart enough that if you run once a month and you hit an "every 3 months" item that is not due yet, skip it. Filter it out. Just don't do it.

The USUAL way to do this is to have some supporting fields that tell you some things that might be useful to know in these queries.

1. Date of last automatic update for this item.
2. Interval (days) of automatic update - i.e. frequency
3. Some marker, maybe yes/no is enough.

Then, do this.

query 1 - update (reset) the marker
query 2 - using (DateDiff of today vs. last auto update in days) vs. interval, update the mark on the items due for an update.
query 3 - do the update
query 4 - update (reset) the date of last auto update
query 5 - optional: reset the marker for the next sweep.

 

[twychopen22]

Thanks for your, once again, quick reply! Well I have a field that has only 4 options (Obviously by now) monthly, quarterly, semiannually, annually. Can't I just create 4 different queries, run them every day and if they are empty, then they are empty, if there is a record, it will update (add to your suggested 2nd table). I will group by "monthly" Next I will group by "quarterly" then if initialexpensedate = Month +3, append to table2. And so on and so forth. Wouldn't this work. Also, I was thinking, put access on the server to allow it to remain on all the time and then I will set the autoscheduler to run the needed queries and so forth to keep it up to date.

 

[The_Doc_Man]

You can surely do 4 queries and 4 macros if that is easier for you to visualize. Be aware that if you try to put the monthly and quarterly on the same day of the month at the same time of day you run the risk of locking the database against yourself. Using two separate scheduler entries, if you run both at the same time the database is open twice. (It's a side effect of the way the scheduler works.) This leads to record locking issues.

 

[twychopen22]

I c. I did not know that. Also, I had asked the same question on another forum and here is what was replied, I would love to know what you think, you seem to be very knowledgable. I am not sure, the following sounds like an easy way to do it but I am not sure if it is what Iwant to do.

"I'm not clear on your structure here. Its certainly unorthodox and may not be normalized. You really shouldn't have two tables to record the expenses. You should have a table of expense types (Paper, Toner, et,) and a table of when these expenses actual occur.

The Expense type table can include a time frame for when the next expense would be due. You could then calculate that using the last expense date and the Date Add function."

Also, I was just thinking, about the locking up of the database, Could I just run them in sequence somehow? I am not sure how that works if there are 2 or more that happen at the same time like u said but would that be possible?