Re: Changing query at runtime

From: Joan Wild (jwild_at_nospamtyenet.com)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 10:13:16 -0500

Instead of setting up the query in code, just create a query and save it.

Modify the sql property, and give users modify permissions to the query. 

-- 
Joan Wild
Microsoft Access MVP
"Adrian Jansen" <qqv@noqqwhere.com> wrote in message
news:406908ef@duster.adelaide.on.net...
> Hi all,
>
> I have developed a multi-user secured database in A2002, standard FE/BE
> setup.  One of the filter forms creates a query in code, by adding filter
> commands to the SQL for the query.  All works ok when the current user
> creates the query ( assuming it doesnt already exist ), and works with it.
> But if another user logs on to the same machine, and attempts to change
the
> filter conditions, of course the query doesnt belong to them, and they
cant
> modify or delete it.
>
> I tried changing the permissions for the query to include read/modify and
> even admin for all the groups, but security still wont let another user
> change the query.  There must be an easy way around this, but I cant find
> it.
>
> The code I use to set up the query is as follows:
> ( strSQL contains the full query string, built in code )
>
> On Error resume next
>
> 'see if query exists
> Set qdf = CurrentDb.QueryDefs("qtemp")
>
>
> If Error.num = 0  Then
> 'qtemp exists, reset its definition
>     qdf.SQL = strSQL
>
>   Else
>
>     Set qdf = CurrentDb.CreateQueryDef("qtemp", strSQL)
>   End If
>
> I dont want to delete qtemp at this stage, since I want to use it to run
> reports and set up other forms with the same filter applied.
>
> -- 
> Regards,
>
> Adrian Jansen
> J & K MicroSystems
> Microcomputer solutions for industrial control
>
>

Relevant Pages

  • Getting current DB with an adp connection
    ... I am trying to export a filtered subform in ms ... but I understand the best way to do it is to set up a query and change ... the sql of the that query to filter the subform and then export the ... Set qdf = db.CreateQueryDef ...
    (microsoft.public.access.adp.sqlserver)
  • Problem with CurrentDB
    ... I am trying to export a filtered subform in ms access to excel ... but I understand the best way to do it is to set up a query and change ... the sql of the that query to filter the subform and then export the ... Set qdf = db.CreateQueryDef ...
    (microsoft.public.access.externaldata)
  • Re: open and closed status
    ... Are you modifying a query, using a filter argument, using a parameter in the query? ... If you are passing a filter via some vba then you need to modify the filter. ... will not "pull records" so there must be more to it. ...
    (microsoft.public.access.reports)
  • Re: Filter Report
    ... > you could modify the code behind the command button to use ... > criterion in the query would look something like: ... >> apply a filter to only show the most recently created order? ...
    (microsoft.public.access.reports)
  • Re: Getting current DB with an adp connection
    ... All that I am trying to to do is export a subform which has been ... the subform as per filter options). ... but I understand the best way to do it is to set up a query and change ... Set qdf = db.CreateQueryDef ...
    (microsoft.public.access.adp.sqlserver)