"Background Processing" version of an MS Access app?

[(PeteCresswell)]

Tried to sell my guys on SQL server as their back end, but it was
no-go.

Now I'm on my fourth app for them and the chickens have come home
to roost.

In the latest app, each time somebody buys a security, we need to
compute daily yields and payment accruals. Sometimes 200-300 of
each for a single buy. Doesn't have tb done at "Buy" time...
but needs tb completed before the next biz day.

Also, ongoing, each day we have a bunch of new index rates (like
Libor, Fed Funds Target, and so-forth) and each day we have to
compute ongoing payment accruals and yields for everybody
affected by those new rates. Similar in that it doesn't have tb
done *right now*.... but something should be munching on the
numbers ASAP after the daily index rates have been updated.

This stuff brings a regular workstation - where somebody has to
get their own work done - to it's knees.

One possibility seems tb some sort of very-powerful terminal
server with the users logging on via clients like Remote Desktop.

But that's blue sky for me right now bc I don't have a clue in
that area.

What does occur to me, however, is putting a version of the
application on a box under somebody's desk where it just sits
there and loops 24-7 with a Timer event that makes it the heavy
lifting in batch mode during the wee hours - or at least after
COB..... and maybe start pecking away at the daily reset updates
ASAP.

Anybody been here? I tried something like this once before -
putting it on a LAN server. Won't divulge the whole sordid
tale, but suffice to say I've learned that's a definite no-no.

Suggestions?

 

[david]

Never got that far. We just go to lunch and leave it for half an hour.
The process can be run for a single client, so we only actually have
to do the whole batch update before end-of-month - otherwise we
only calculate the required individual.

No reason why you shouldn't create a server process unless you
have embedded User Defined Functions or Code Loops, but I
don't see how you are going to do a batch process without
effectively locking the whole database, so I'm not sure it matters
if you run it on a workstation: what else is the user going to be
doing if the database is locked?

(david)

 

[Tony Toews [MVP]]

What does occur to me, however, is putting a version of the
application on a box under somebody's desk where it just sits
there and loops 24-7 with a Timer event that makes it the heavy
lifting in batch mode during the wee hours - or at least after
COB..... and maybe start pecking away at the daily reset updates
ASAP.

Trouble is what happens if for some reason it doesn't start executing?
Someone powered it off or whatever? Otherwise I would agree with the
concept of a dedicated workstation.

I'd be real tempted to have it wait on a one minute timer and have it
crunch any data as soon as the data is ready.

And some means at the COB for someone to start running it
automatically not waiting for someone to remember to start running it.
Also have some kind of a notification process that it is or isn't
running or has successfully completed.

Maybe selected users should have a "tickler" field on their main menu
updated on the Activate event showing how many records/calculations
are waiting in the queue. So if you are doing some during the day
that they can see that the queue isn't getting too large.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[(PeteCresswell)]

Per Tony Toews [MVP]:

Trouble is what happens if for some reason it doesn't start executing?
Someone powered it off or whatever? Otherwise I would agree with the
concept of a dedicated workstation.

I'd be real tempted to have it wait on a one minute timer and have it
crunch any data as soon as the data is ready.

And some means at the COB for someone to start running it
automatically not waiting for someone to remember to start running it.
Also have some kind of a notification process that it is or isn't
running or has successfully completed.

Maybe selected users should have a "tickler" field on their main menu
updated on the Activate event showing how many records/calculations
are waiting in the queue. So if you are doing some during the day
that they can see that the queue isn't getting too large.

That makes a lot of sense. Thanks.

Also seems to clinch the concept of a dedicated box - just let
the "server" .mdb run 24-7, with something in the regular app
that keeps reaching out and touching the server to see if it's
still on the job.