Re: Newbie question about formula based values


"Mintyman" <mintyman@xxxxxxxxxxxx> wrote in message
news:fieaeq$a3i$1$8300dec7@xxxxxxxxxxxxxxxxxxx
Sounds perfect Neil.....only problem is that I don't have access to the
PC. The database is located on a hosting account on a remote server (where
my website is hosted). So, I can't get access to the Windows Scheduler. I
was hoping there would be a way for Access to run these updates
automatically.

Well Access can run it automatically if you leave Access running. Obviously,
if Access is closed, it can't. What you'd do is set a timer to check the
time every minute or every ten minutes or whatever. If the time equals or is
past the time to run the code, then execute the query. Don't need the macro
or anything. And, of course, remove the Docmd.Quit line. :-)

Of course, if the query hangs or if Access crashes or is someone restarts
the machine, then you're screwed (unless you can put the MDB in the Startup
folder).

FMS has a utility that runs as a Windows service, so that's a more solid
approach, and can automatically restart the machine if it crashes.. I
haven't used this utility myself, so I can't give you any more information
on it. But you can go to http://www.fmsinc.com/products/Agent/index.html to
get more info on their "Agent" product.

Of course, that assumes that you're able to install the utility on that
machine.

If you want to execute the code from your web site, you can do that as well.
If your web site can execute SQL code against an Access database, then just
do that. That would be very simple. If not, then you can possibly open
Access through Automation and execute the SQL that way. But accessing
Access through a web site is a bit out of my area of knowledge.


I agree this query only needs to be run once a day. However, the only way
I know of calling this update would be to put a trigger in the ASP code on
a designated page that I know will be visited at least once a day - the
homepage soudns ideal. However, I don't want it to trigger every time the
page is requested. That would be a waste of overheads. The only way I
could do it would be to enclose it in an IF statement that would only
allow the code to run during a given period in the day e.g. run the update
ASP code between 11am and 11.20am. Then all I need to do is hope that I
receive a vistior within that time period.

I know it's not elegant but it should work......unless you can think of a
better idea?

Well, first, will the visitor need updating only for their record? If so,
then you can modify the query to only update that visitor's record.

Second, you would create a table in your Access database which tracks when
the record was last updated for that visitor. When you execute the code, you
store the current date (without the time component). Then, whenever the user
logs in, you execute your code, but check the date it was last run, and, if
it was run today, then skip.

Of course, there'd still be the overhead of opening the Access app each
time. So a better approach would be to store the date it was last updated
for that user in your web database. Do the same thing, check last date
against current date, and then run the SQL against the Access app only if
the dates don't match. That should do it for you. (Still have the overhead
of looking up the last date; but what can you do?)

If the above is not correct regarding needing to update the visitor's
record, but, instead you want to update all records, then you can do this
same process of checking the date it was last run, only there would be a
single value (for all users) instead of one for each user. That would work.
But it seems a bit hokey to me to rely on someone clicking on your web page
in a given day to run a process. Better would be if you could put some sort
of automation in place.

HTH,

Neil




"Neil" <nospam@xxxxxxxxxx> wrote in message
news:5fl2j.24521$JD.4523@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
You can use Windows Scheduler (in Control Panel) to run the query
whenever you want. The way you'd do it is as follows:

1) Create your query.

2) Create a macro. In this macro have a single item that calls RunCode.
Have it call a routine you create.

3) In the routine that you referenced in the macro, do the following:

Currentdb.Execute "MyQuery", dbfailonerror
Docmd.Quit

That will run your macro and then close the application when it's done.

4) In Windows scheduler, create a new scheduled task based on Microsoft
Access. Go into the task and edit it, telling it to open your database.
At the end of that line, put /x macroname. Example:

c:\program files\office\msaccess.exe c:\somedir\mydatabase.mdb /x
mymacroname

Note that you have to specify the path to msaccess.exe if you want to use
the supplemental arguments.

Is that clear? If not, let me know.

Neil



"Mintyman" <mintyman@xxxxxxxxxxxx> wrote in message
news:6Ni2j.5612$B97.4576@xxxxxxxxxxxxxxxxxxxxxxx
Hi Neil,

Will this query only run when I run it in Access? I would like these
values to be updated on a constant basis without me having to do
anything. I wasn't sure if Access could do this or whether i'm getting
confused with SQL stored proedures.

An alternative would be to place the code on a webpage on my site that
will be accessed on a daily basis. That way, the query could be
triggered every time a visitor loads that specific page.

"Neil" <nospam@xxxxxxxxxx> wrote in message
news:dq_1j.1362$AR7.112@xxxxxxxxxxxxxxxxxxxxxxx
You can create a query; don't add any tables; click the SQL button; and
then add the following:

Update MyTable Set Level=1 Where EndDate<Date()

If you want to ONLY update if the level was 2 (e.g., not 3), then you'd
do:

Update MyTable Set Level=1 Where EndDate<Date() AND Level=2

Save your query, and then, when you open it from the database window,
it will update your records.

(Be sure to substitute the actual name of your table for MyTable.)


"Mintyman" <mintyman@xxxxxxxxxxxx> wrote in message
news:NRZ1j.12827$Ew3.9757@xxxxxxxxxxxxxxxxxxxxxxx
HI,

I'm not that experienced in Access so am turning to the experts for
some basic help.

I have the following fields in a table :

Name Type
Level Number
Start Date Date
End Date Date

Is it possible to have some code that will automatically change the
value of 'level' from 2 to 1 if today's date is greater than 'End
Date'? Effectively, I want the customer's access level to drop once
their contract has run out. At the moment I have to keep an eye on
when customer contracts are due to end and manually update the value
in the 'level' field from 2 to 1.

Can anyone show me how to do this?

Thanks!













.