How to Suspend Permissions Temporarily

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

I have a report that prints but before doing so a query runs that
updates data for a certain field in the main table for the report.
But that table is protected and some users cannot edit it which causes
a problem when they try to print it. So only users that have
permissions for that table can run that particular report. But I want
all to run the report with that particular field's data especially.

If the added field is in an attached query only (and not pasted into
the main table) the system takes too long.

What is the best way around this?

Can I create a macro that suspends permissions---there is a mac
already---and than turns them on again?

Do I have to create a whole new table for this data and link it in for
these users (and while I'm at it, for all users)?

Or is there any easier solution I am not thinking of?

Matt
 
I'm not sure you understood the question. The query itself is an
action query that pastes data into a table. Then the report runs with
that data.

So I can give permission for all users to run the query. But that
doesn't solve the problem, does it? The problem is that the query
updates a table they do not, and cannot, have permission to edit.

The only solution I can come up with is to create a table on each
user's frontend to paste the data (just the one field with key data)
into and have the report run with that and the main table.

I can't work straight off the query because it takes too long. It is
much faster if I paste the data in to the table, or, I'm guesing, if I
create a frontend table.

But before doing that I was just wondering if there was an easier
solution. Can I suspend permissions to that table for this report,
from the moment the command button is pressed till the moment the
report pops up?

Matt
 
I've decided to create a table, which will be on everyone's own front
end, to store the data for this one field. The query deletes the data
in the table than updates the table then runs the report using both
the frontend table and a backend table. I do this sort of thing a
lot. Thanks for your time.

Matt
 
I'm not sure you understood the question. The query itself is an
action query that pastes data into a table. Then the report runs with
that data.

So I can give permission for all users to run the query. But that
doesn't solve the problem, does it? The problem is that the query
updates a table they do not, and cannot, have permission to edit.
Click to expand...

So you create a RWOP SELECT query that your report uses instead of the
table.
The only solution I can come up with is to create a table on each
user's frontend to paste the data (just the one field with key data)
into and have the report run with that and the main table.

I can't work straight off the query because it takes too long.
Click to expand...

No properly constructed query against properly indexed tables should "take
too long". Fix your problems that make it too slow.
 
No properly constructed query against properly indexed tables should "take
too long". �Fix your problems that make it too slow.>
Click to expand...

Thanks. Next time I come across this type of thing I will look at the
indexing.

But doesn't indexing have a cost? Is there actually no situation where
one should not do the method I chose? Consider the size of the table,
the number of fields already indexed, the size of the database
itself.

The query did not take long to run by itself. When I attached it to
another query it took a bit of time but not too much. When I ran the
report with only one PO selected, it took only a bit longer than
normal. But when I selected ten POs, it started to take much too
long. Creating the table solved this problem.

Matt
 
Thanks. Next time I come across this type of thing I will look at the
indexing.

But doesn't indexing have a cost?
Click to expand...

Only in making the file a bit larger and there is a limit on the number of
indexes on a single table (32?). Updates can be a bit slower with more
indexes, but I have never seen that as a noticeable issue.
Is there actually no situation where
one should not do the method I chose? Consider the size of the table,
the number of fields already indexed, the size of the database
itself.
Click to expand...

There are cases where it is a legitimate way to speed things up. But as a
general rule you should always be VERY dubious of every situation that
appears that way. In most cases the tables and query can be tweaked to
achieve satisfactory performance without taking that step.
 
Thanks. I have a rather large and complicated database with many
users. I wouldn't want to tweak much right now in the main table and
find problems later, in procedures I wrote 6 years ago. My solution
is a safe one and works fast and will cause no problems. But you may
be right and there may have been an easier solution.

Matt
 
Back
Top