Run BE query from FE?

[Mark Hammer]

My problem: client has large BE file, Access 2000, at remote location.
Would like to create a smaller version by filtering old data.

Question: is there a way to include queries in the BE which would run on the
BE, but be triggered from the FE?

Or perhaps a separate small app, on the server, but still triggered from the
FE location?

Or is this stuff that only SQL Server can handle?

TIA,

Mark Hammer
Lake Oswego, Oregon

 

[Rick Brandt]

My problem: client has large BE file, Access 2000, at remote location.
Would like to create a smaller version by filtering old data.

Question: is there a way to include queries in the BE which would run on the
BE, but be triggered from the FE?

Or perhaps a separate small app, on the server, but still triggered from the
FE location?

Or is this stuff that only SQL Server can handle?

As long as the data is in a Jet (mdb) file then the server is nothing more
than a remote hard drive. ALL processing is done on the front end PC.
You're correct that you would need to move to a true server-based back end
(of which SQL Server is just one option) to get this kind of operation.

 

[Mark Hammer]

On the server machine, I could place a separate app which would run queries
to create my reduced size db.

Isn't there a way that I can simply trigger (run) that app from the FE
location?

--Mark Hammer

 

[Rob Oldfield]

I haven't done this, so haven't been through all the practicalities, but I
would have thought it was possible. Something like....

Table in the BE. Maybe just an autonumber PK and a single memo field named
SQL.
FE db appends an SQL make-table statement into that table.
BE has a timer event running (either a form timer event, or a scheduled task
every minute or whatever) to check for the presence of a record in that
table. If there is one, run it, then delete the row from the table.

Not sure if the made table would automatically appear in the FE or whether
you'd have to refresh in some way, but I think that should do the job.

 

[Rick Brandt]

On the server machine, I could place a separate app which would run queries
to create my reduced size db.

Isn't there a way that I can simply trigger (run) that app from the FE
location?

What kind of app? An Access app? Is Access running (or even installed) on
your server? How do you get one instance of Access to execute a query and
send the results to another instance?

This falls into the category of "if it were practical everyone would be
doing it".

MSDE is free and can do what you are asking. Have you looked into that?

 

[Mark Hammer]

The app is an Access 2000 parts database. Server and primary user located
in Seattle; subsidiary users in Portland with FEs attaching over WAN.

Good question, Rick, about whether Access is running on the BE. I had
assumed its availablility, but must admit I hadn't thought to ask.

I'm willing to assume for the moment that Access is available on the server,
or can be made so.

And I agree that this seems a dumb question, like you say, if it were
possible surely everyone would be doing it. Still, I'm willing to risk
looking stupid in the service of learning something.

Seems logical to me that if you can run an app on the BE, with queries which
create tables of reduced size in a second BE, you should be able to trigger
that action from a remote location. Given that, all I've gotta to in my
Portland FEs is relink to the tables in the second BE.

What's the impossible part that I'm missing here?

My impression is that upsizing to SQL Server / MSDE is a significant step
for an existing, fairly complex app. Perhaps I'm overestimating the size of
this step, though.

Your continued advice much appreciated.

--Mark Hammer

 

[Rick Brandt]

The app is an Access 2000 parts database. Server and primary user located in
Seattle; subsidiary users in Portland with FEs attaching over WAN.

I would never run an Access file share FE/BE over a WAN. There is simply not
enough speed and too much risk for corruption. The only recommended way to run
this setup over a WAN is to use Terminal Server or similar so that all of the
app is being executed on the LAN and only screen updates and keyboard/mouse
input is run over the wire.

Seems logical to me that if you can run an app on the BE, with queries which
create tables of reduced size in a second BE, you should be able to trigger
that action from a remote location. Given that, all I've gotta to in my
Portland FEs is relink to the tables in the second BE.

What's the impossible part that I'm missing here?

I didn't use the word impossible, I said "if it were practical...". Sure you
could enter the criteria into a table on the BE and have the Access instance on
the server poll that table every few seconds, run the query when it sees a new
entry and place the result in another table. Now, how do you do that for
multiple users at the same time and not have them step on each others toes and
THEN have the whole thing perform any better that just plain ol' linking and
querying?

My impression is that upsizing to SQL Server / MSDE is a significant step for
an existing, fairly complex app. Perhaps I'm overestimating the size of this
step, though.

There is some rework to be expected to make this move and "Optimize" for the new
environment, but just building the tables on a server database, linking to them
and doing NOTHING else to your design would undoubtedly work better over a WAN
because without any rework at all you will at least get "some" of the processing
done on the server. Surprisingly a lot actually. I work almost exclusively in
an Access FE / Server BE environment and the vast majority of queries will send
most of the work to the server without tweaking of any kind. Those that need
to be redesigned make themselves obvious and can be dealt with.