Changing query at runtime

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adrian Jansen (qqv_at_noqqwhere.com)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 15:42:08 +1000

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

  • Re: Update Query based on Current Recordset
    ... Gina Whipp ... 2010 Microsoft MVP (Access) ... can base your update query on this. ... I opened the Properties dialog of the form and looked at the Filter ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... in the criteria cell in the graphical query builder. ... use as the source in the routine. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... and have the query point to the combo box to get its value. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ... Set MyRS = MyDB.OpenRecordset" ...
    (microsoft.public.access.formscoding)
  • Re: Update Query based on Current Recordset
    ... let's make the UPDATE query control that. ... I opened the Properties dialog of the form and looked at the Filter row. ... "Gina Whipp" wrote: ... UPDATE tblData SET tblData.ThreatStatus = Closed WHERE ...
    (microsoft.public.access.formscoding)
  • Re: Filtering Via Form, and then Export
    ... I just converted they query ... the detail rows were bound to, to a make table action query, and have indexes ... But the filter form works ... Select MonthYear of Data to Export: <dropdown choices> ...
    (microsoft.public.access.queries)