Front-end db run Parameter query in back-end Access db using ADO?

[BogMonster via AccessMonster.com]

Hi,

I have a parameter query in a back-end Access database that I would like to
be able to execute from front-end Access databases, using ADO. The query
creates a view of specific fields from about 15 tables and limits the
returned records based on supplied parameters. This allows the front-end user
to work offline with this snapshot.

I've seen some code using DAO that seems to achieve this by using querydefs
(I think). My understanding of this is that it effectively modifies the back-
end query. Would this not cause problems with concurrent access if 2 or more
try to modify the query at the same time? Still, I'm already using ADO in
this project and prefer to keep it that way.

Can I call this parameter query from a front-end Access database and pass the
required parameters to it or do I have to move the query to the front-ends,
link to all the required tables in the back-end Access db and execute the
query against the linked tables?

If it's possible, how do I do it please?

Many thanks

 

[Douglas J. Steele]

Not quite sure I understand the issue.

Realistically, all queries should be in the front-end, not the back-end. And
using DAO is the preferred way to modify QueryDef objects, even if you then
use ADO to run the query.

 

[BogMonster via AccessMonster.com]

Douglas,

Thanks for your reply.

The only reason that I wanted to keep this query in the back-end was because
it uses 15 tables and will be called infrequently, so it would have saved me
having to have the front-end link all the tables.

I just wondered if calling a parameter query in a back-end was the same as
calling one in the local front-end but with the appropriate connection (can't
get that working though!). Guess it isn't so I'll probably write some code to
link the required tables on the fly before running the query on the front-end.


Do you know if there is a performance overhead with multiple clients linking
to a back-end Access database or is the overhead only incurred when a
recordset is opened against the linked table? In other words, does the act of
linking in itself put any demands on the back-end or only when there are
active open recordsets against those tables?

Thanks again.