Changing query at runtime

  • Thread starter Thread starter Adrian Jansen
  • Start date Start date
A

Adrian Jansen

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
 
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.
 
Thanks Joan, I already tried that, but after giving the qtemp object Read
Design, Modify Design and even Admin permissions, I still get the "You do
not have the necessary permissions...." error when I change the SQL of the
query in code. It seems to have something to do with the "With owneracces
option" clause in the query. Taking this out lets me modify the SQL, but of
course then the query wont run, since the tables it gets the data from are
secured from the user.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Well you've hit the nail on the head. RWOP in code is senseless.

You can base qtemp on RWOP queries (rather than the tables). Just create a
RWOP query for each base table and use these in qtemp.
 
Thanks a million Joan.

So simple when you see the problem !

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top