Front-end / back-end queries

[dave k]

I have been working with a front-end / back-end configured database for a
while and it works great. I have been creating the queries in the front-end
but now think maybe it would be better in the backend. Is there any benefit
to either way?

Thanks,
Dave

 

[Rick Brandt]

I have been working with a front-end / back-end configured database
for a while and it works great. I have been creating the queries in
the front-end but now think maybe it would be better in the backend.
Is there any benefit to either way?

Thanks,
Dave

Queries are always executed in the front end anyway so that is where they
belong. If you put them in the back end file the definition of the query
would need to be pulled into the front end prior to execution.

 

[dave k]

Thanks for the confirmation.

Dave

Queries are always executed in the front end anyway so that is where they
belong. If you put them in the back end file the definition of the query
would need to be pulled into the front end prior to execution.

 

[Jeff Boyce]

A few years, back, to satisfy my curiosity, I created a back-end (data
tables) with a query in it. I added a function in the back-end to call that
query.

Then I referenced the back-end from a front-end and called the function. If
I interpreted the results correctly, the back-end ran the query and returned
the results to the front-end. So I suppose what you're asking is
technically feasible.

However, all the extra machinations are really not necessary if you create
the query in the front-end (although performance may be a different issue).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Rick Brandt]

A few years, back, to satisfy my curiosity, I created a back-end (data
tables) with a query in it. I added a function in the back-end to
call that query.

Then I referenced the back-end from a front-end and called the
function. If I interpreted the results correctly, the back-end ran
the query and returned the results to the front-end. So I suppose
what you're asking is technically feasible.[snip]

You're suggesting that an MDB file "ran" a query? How exactly would that
work? What was the executable that actually did the processing?

 

[Jack MacDonald]

A few years, back, to satisfy my curiosity, I created a back-end (data
tables) with a query in it. I added a function in the back-end to
call that query.

Then I referenced the back-end from a front-end and called the
function. If I interpreted the results correctly, the back-end ran
the query and returned the results to the front-end. So I suppose
what you're asking is technically feasible.[snip]

You're suggesting that an MDB file "ran" a query? How exactly would that
work? What was the executable that actually did the processing?

I've done a similar thing for experimentation. Write a function that
returns a DAO recordset. The function can open a table or query or run
SQL directly, as long as its return value is a recordset. Its input
parameters could be the name of a query in the backend, or a valid SQL
string. Whatever...

The function exists in a module in the backend database.

Then in the frontend, create a reference to the backend. All the
public functions thus become available to the frontend. Dimension a
DAO recordset variable in the frontend, and set its value to the
return value from your function.

It works, but it's convoluted and may be more trouble than it is
worth. The "worth" is that (contrary to logic), the query performs
about 10 times faster than using conventional methods.

I would like some independent corroberation of the performance benefit
-- I saw the performance boost on several of the computers and
networks that I use, but haven't gone any further with it. If you care
to do that and respond back, I will be out of contact for about a
week. Perhaps I will hear back then...



**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Tim Ferguson]

I have been creating the queries in the front-end
but now think maybe it would be better in the backend

For my tuppenceworth: I put in the backend any queries that are going to be
universally-useful, regardless of which "front-end" I might be using. This
saves me having to re-create or (even worse) re-typing the things,
especially with complex or difficult SQL.

For queries that are specific for a particular front end (e.g. data entry,
decision support, mail merge etc) then I'd make them in the front end.

Hope that helps



Tim F