Link Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to link a query from a backend to a frontend in access?
I've searched around a bit and seen nothing, so this is my last hope. I
understand that I can link tables and that I can do a make-table query and
then link that table. However, the most efficient scenario for what I want
to make available for my users is to link one query from the backend
database. Anyone figured out how to do this?

Thanks,
cam
 
CAM said:
Is there any way to link a query from a backend to a frontend in access?
I've searched around a bit and seen nothing, so this is my last hope. I
understand that I can link tables and that I can do a make-table query and
then link that table. However, the most efficient scenario for what I want
to make available for my users is to link one query from the backend
database. Anyone figured out how to do this?
Click to expand...

Why would that be "more efficient"? The query is always executed by the front
end regardless of where you might store it. Just put the query in the front
ends.
 
It would be more efficient because this particular query is based on 2 other
queries. If I can't link the final query then everyone is going to have to
import 3 queries instead of linking just one. Which basically means that
I'll have to do it for them. Just trying to save myself some hassle.

thanks,
cam
 
Why would that be "more efficient"? The query is always executed by the front
end regardless of where you might store it. Just put the query in the front
ends.
Click to expand...


I did a bunch of experimentation with this concept about two years
ago, and contrary to expectations, a query executed in the backend is
*much* faster than a query executed in the frontend. Doesn't seem like
it should be, but I found a 10 times increase in speed. I used some
query-timing software to measure the results, but the speed difference
was noticeable even to the naked eye.

My technique returned a DAO recordset, and I never figured out a
generic solution to bind it to a form. So without making the leap into
unbound forms, I was unwilling to modify my applications to use the
technique and take advantage of the speed increase.

Speed increases were especially noticeable when the backend was on a
network server, as one would expect.

Here's what I did:

1. Make a public function in the backend that returns a recordset. The
input parameter to the function should be the name of a query in the
backend. Alternatively, an SQL statement will work just as well.

2. The function should open a DAO recordset on the query, and return
it as the function return value.

3. The frontend must REFERENCE the backend. This additional "binding"
of the frontend to the backend was also a detraction to using the
technique.

4. Call the public function in the backend from the frontend. Public
functions in referenced databases can be called from an external
database.

Action queries were similarly speed enhanced.



**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Jack MacDonald said:
I did a bunch of experimentation with this concept about two years
ago, and contrary to expectations, a query executed in the backend is
*much* faster than a query executed in the frontend. Doesn't seem like
it should be, but I found a 10 times increase in speed. I used some
query-timing software to measure the results, but the speed difference
was noticeable even to the naked eye.

My technique returned a DAO recordset, and I never figured out a
generic solution to bind it to a form. So without making the leap into
unbound forms, I was unwilling to modify my applications to use the
technique and take advantage of the speed increase.

Speed increases were especially noticeable when the backend was on a
network server, as one would expect.

Here's what I did:

1. Make a public function in the backend that returns a recordset. The
input parameter to the function should be the name of a query in the
backend. Alternatively, an SQL statement will work just as well.

2. The function should open a DAO recordset on the query, and return
it as the function return value.

3. The frontend must REFERENCE the backend. This additional "binding"
of the frontend to the backend was also a detraction to using the
technique.

4. Call the public function in the backend from the frontend. Public
functions in referenced databases can be called from an external
database.

Action queries were similarly speed enhanced.
Click to expand...

I don't understand how that is possible since all of those queries are being
executed on the front end computer. The back end computer will normally not
even have Access installed and will certainly not have it running. It is
nothing more than a hard drive on the end of a really long cable.
 
I don't understand how that is possible since all of those queries are being
executed on the front end computer. The back end computer will normally not
even have Access installed and will certainly not have it running. It is
nothing more than a hard drive on the end of a really long cable.
Click to expand...


I understand your skepticism, and I would have agreed if I hadn't
tried it for myself and seen the results. I suggest that you try it
for yourself to see if the results are repeatable or just my
imagination <g> I replicated the experiment on my office and home
networks, and got similar results on both.








**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Thanks,
I will try this. However, the way I understand this, it will just return
the entire query, but not allow the users to use this in another query to
only return certain records by adding critera. Or am I understanding this
incorrectly?

Thanks,
Cheryl
 
If you ask for it to return a query, then it will return all the
records. However, if you ask it to return a subset from a query, then
it will return just that subset. It is entirely up to you, and how you
write the public function in the backend.

To do what you are asking for, you would need a function that would
accept an SQL string as an input, and return the resulting recordset
as an output. The calling program would need to construct the SQL
statement, including the criteria required by the user.

I am not saying this is particularly *easy*... but your original
question was about whether it was *possible*.

HTH


Thanks,
I will try this. However, the way I understand this, it will just return
the entire query, but not allow the users to use this in another query to
only return certain records by adding critera. Or am I understanding this
incorrectly?

Thanks,
Cheryl
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Thanks for all of your help. I think I'll just go the 'easier' route of
having people import the query or doing it for them.

cheryl
 
Back
Top