queries in front end

[Craig]

We have a standard FE/BE database setup. Each user has their own copy of the
FE and all link into the same BE. Some users are writing queries which get
saved in their local FE version. When I publish a new copy of the FE and
they download it they lose their saved queries. They also would like to
share queries with the other users.

I have created a table for storing the queries, I can populate the table
with a record (SELECT * FROM Employees) and then create a new querydef, drop
in the SQL from the table and then open the querydef for editing. Works a
charm, but if the person changes the query somehow, then saves it, the
changes dont get relected back in the table. What I am looking for is a way
to trigger an onSave event for the query builder that lets me update the SQL
in the table.

How is the saving of local queries usually handled in a FE/BE setup?

cheers
Craig
(e-mail address removed)

 

[Rick Brandt]

We have a standard FE/BE database setup. Each user has their own copy
of the FE and all link into the same BE. Some users are writing
queries which get saved in their local FE version. When I publish a
new copy of the FE and they download it they lose their saved
queries. They also would like to share queries with the other users.

I have created a table for storing the queries, I can populate the
table with a record (SELECT * FROM Employees) and then create a new
querydef, drop in the SQL from the table and then open the querydef
for editing. Works a charm, but if the person changes the query
somehow, then saves it, the changes dont get relected back in the
table. What I am looking for is a way to trigger an onSave event for
the query builder that lets me update the SQL in the table.

How is the saving of local queries usually handled in a FE/BE setup?

User-created queries are "usually" not allowed. The front end I distribute
is my application. If the user has the skills and desire to create their
own queries I tell them to create their own file.

 

[Dale Fye]

I'm with Rick on this one, but that may be because the few users I have that
are familiar with Access are likely to screw up the query and start producing
"bad" analysis (like using an inner join when they should use an outer join).
If they need added functionality, I provide it.

However, assuming that you really want to do this, you could do something
like having a table in the backend for user queries, where you store their
UserID, the query name, and the SQL. When the FE application closes, it
could check to see whether there are any queries in the front end that are
not part of the basic application, and if so, update/write to that table.

Then, when the user logs into the frontend, it could check to see whether
that user has saved queries (in the table) that are not in their copy of the
front end, and create them programmatically.

HTH
Dale

 

[Tony Toews [MVP]]

Some users are writing queries which get
saved in their local FE version. When I publish a new copy of the FE and
they download it they lose their saved queries. They also would like to
share queries with the other users.

For the few power users who want to do this I create them an empty MDB
linked to the tables in the BE. Sometimes I'll give them some basic
queries to get them going. If they want to give a copy to someone
else then they give them the MDB. Also occasionally, if I've added
new tables then I have to add those new linked tables to thier MDB.

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/