users running their own queries...

[Brad Pears]

I have a situation in an access 2000 app where I would like to allow users
to build and save their own queries. Then, when they want to re-run a stored
query down the road, I want to list only their own queries for them, to
choose from - not the ones that the app uses on a regular basis or anyone
elses queries - just their own). Is there an easy way to accomplish such a
thing?I took a look at system table "mSysObjects" and I see that all queries
have a 'type' of 5 and there is an "Owner" field there but it is not
populated at all. How would one get that owner field populated - and
continue to populate the owner field for each new query that is created???

Thanks, Brad

 

[Douglas J. Steele]

I would not recommend trying to update the system tables in any way.

Depending on how you're going to let them build their own queries, you might
just have a table that contains them. Of course, the general recommendation
is that Access applications be split into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relationships), with each user having his/her own
copy of the front-end, which would imply that you don't really need to do
anything...

 

[Brad Pears]

The only problem with that being that all of the other queries that the app
needs to run properly must also reside with the front end copy - so they
would wind up seeing all those as well as any they have built themselves...

No big deal however, because there is security applied to them all but it
would be nice to just display only the ones they have been building...

Thanks for your help - I will figure something out I guess...

Brad

 

[Guest]

Roger Carlson has a search/query builder form with a technique to only show
objects that start with the specific string you specify.

http://www.rogersaccesslibrary.com/TableOfContents3.asp

This is a combo box record source
SELECT DISTINCT "Table: " & MSysObjects.Name , MSysObjects.Name AS Expr1
FROM MSysObjects WHERE (((MSysObjects.Name) Like "tbl*") AND
((MSysObjects.Type)=1)); UNION SELECT DISTINCT " " & MSysObjects.Name ,
MSysObjects.Name AS Expr1 FROM MSysObjects WHERE (((MSysObjects.Name) Like
"Search*") AND ((MSysObjects.Type)=5));


Hope this helps.

 

[Brad Pears]

Thanks for that. I'll give it a shot.

Brad

Roger Carlson has a search/query builder form with a technique to only
show
objects that start with the specific string you specify.

http://www.rogersaccesslibrary.com/TableOfContents3.asp

This is a combo box record source
SELECT DISTINCT "Table: " & MSysObjects.Name , MSysObjects.Name AS Expr1
FROM MSysObjects WHERE (((MSysObjects.Name) Like "tbl*") AND
((MSysObjects.Type)=1)); UNION SELECT DISTINCT " " & MSysObjects.Name ,
MSysObjects.Name AS Expr1 FROM MSysObjects WHERE (((MSysObjects.Name) Like
"Search*") AND ((MSysObjects.Type)=5));


Hope this helps.