Limit future queries to read-only.

  • Thread starter Thread starter David G.
  • Start date Start date
D

David G.

I want to give certain users access to Access's query builder, but
prevent all data changes. Can I monitor the query builder and stop any
action queries?

The data resides in a back end file. Switchboards are used for
navigation, so I have an easy access point for controlling the
application.


THANKS!
David G.
 
I want to give certain users access to Access's query builder, but
prevent all data changes. Can I monitor the query builder and stop any
action queries?

The data resides in a back end file. Switchboards are used for
navigation, so I have an easy access point for controlling the
application.


THANKS!
David G.
Click to expand...

Only by instituting Workgroup Security and locking down all the tables tight,
tight, tight. Difficult to do; if you give users the Query window then you're
handing them the whole ring of keys to the Office.

If you want to institute security, download the Microsoft Access 2000 Security
FAQ:

http://support.microsoft.com/kb/207793/en-us

Study it VERY CAREFULLY.
Get a good night's sleep.
Study it again, even MORE carefully.
Make a backup of your frontend AND backend, and secure them both, following
the instructions in the FAQ fastidiously.

Test, test, test!!!!

Then (if all is well) apply the security to your production instance. Good
luck.
 
I want to give certain users access to Access's query builder, but
prevent all data changes. Can I monitor the query builder and stop any
action queries?

The data resides in a back end file. Switchboards are used for
navigation, so I have an easy access point for controlling the
application.

THANKS!
David G.
Click to expand...

I would avoid giving any users access to the query builder. If you are
wanting your users to be able to query data, you should do it in a
controlled manner. One of the more common solutions I see and use is
called 'query by form'. Essentially you set up a form with the fields
and controls you want to allow your users to use and create the query
programatically.

http://support.microsoft.com/kb/304428 is a Microsoft TechNet article
that gives a good overview of the basic process. It doesn't get too
complicated so it's probably a good place to start, but there are lots
of other resources that get much more sophisticated. Google "query by
form" and you'll find lots of results.

Good luck.

Keven Denen
 
I want to give certain users access to Access's query builder, but
prevent all data changes. Can I monitor the query builder and stop any
action queries?

The data resides in a back end file. Switchboards are used for
navigation, so I have an easy access point for controlling the
application.


THANKS!
David G.
 
I would avoid giving any users access to the query builder. If you are
wanting your users to be able to query data, you should do it in a
controlled manner. One of the more common solutions I see and use is
called 'query by form'. Essentially you set up a form with the fields
and controls you want to allow your users to use and create the query
programatically.

http://support.microsoft.com/kb/304428 is a Microsoft TechNet article
that gives a good overview of the basic process. It doesn't get too
complicated so it's probably a good place to start, but there are lots
of other resources that get much more sophisticated. Google "query by
form" and you'll find lots of results.

Good luck.

Keven Denen
Click to expand...

Keven:
Thanks for your comments. The QBF was my initial thought, but the more
I thought about it, it seemed I was trying to recreate exactly what
the query builder does.

I've also not been able to find a good example of a flexible QBF. I
don't know how to create the "JOIN" portion of a SQL statement given
only a set of field names from various tables. (The "Select", "From"
and "Where" I think I understand how to implement.)
THANKS!
David G.
 
Only by instituting Workgroup Security and locking down all the tables tight,
tight, tight. Difficult to do; if you give users the Query window then you're
handing them the whole ring of keys to the Office.

If you want to institute security, download the Microsoft Access 2000 Security
FAQ:

http://support.microsoft.com/kb/207793/en-us

Study it VERY CAREFULLY.
Get a good night's sleep.
Study it again, even MORE carefully.
Make a backup of your frontend AND backend, and secure them both, following
the instructions in the FAQ fastidiously.

Test, test, test!!!!

Then (if all is well) apply the security to your production instance. Good
luck.
Click to expand...
John:
Thanks much for your comments.
I want to consider the QBF method further mentioned by Keven. I'm also
thinking about duplicating the Back end file. I'd change the linked
tables link to connect to the shadow back end when on occasion a key
user wants to build a custom query.
THANKS!
David G.
 
David said:
I've also not been able to find a good example of a flexible QBF. I
don't know how to create the "JOIN" portion of a SQL statement given
only a set of field names from various tables. (The "Select", "From"
and "Where" I think I understand how to implement.)
THANKS!
David G.
Click to expand...

FWIW, I'd probably do join implicitly so the users doesn't have to know
about the keys. On a QBF, I'd probably provide a multi-select listbox
listing all tables I want user to freely query upon, and when they make
selections, add all field names into 2nd multi-select listbox for users
to select. Throw in a continuous subform bound to a temporary dummy
table with 2 comboboxes & a textbox; first combobox to select the
selected fieldname, 2nd combobox to select the operator and textbox the
expression. That way, they can add as many criterias as they want then
you can build the SQL statement based on the fields selected and when
you detect more than one table are selected, have VBA do some logic to
retrieve the relations and thus create the join portion behind the
curtain for the users.

Maybe that will suit for the kind of querying your users want to do.


An simpler alternative may be to create a 2nd database that takes a
'snapshot' of the production database and is set to read-only and
distribute a 2nd front-end application. Maybe even add a button to your
original front-end to open the 2nd application.
 
FWIW, I'd probably do join implicitly so the users doesn't have to know
about the keys. On a QBF, I'd probably provide a multi-select listbox
listing all tables I want user to freely query upon, and when they make
selections, add all field names into 2nd multi-select listbox for users
to select. Throw in a continuous subform bound to a temporary dummy
table with 2 comboboxes & a textbox; first combobox to select the
selected fieldname, 2nd combobox to select the operator and textbox the
expression. That way, they can add as many criterias as they want then
you can build the SQL statement based on the fields selected and when
you detect more than one table are selected, have VBA do some logic to
retrieve the relations and thus create the join portion behind the
curtain for the users.

Maybe that will suit for the kind of querying your users want to do.


An simpler alternative may be to create a 2nd database that takes a
'snapshot' of the production database and is set to read-only and
distribute a 2nd front-end application. Maybe even add a button to your
original front-end to open the 2nd application.
Click to expand...

Banana:
Thanks for your comments.
I'm considering your alternative concept as well. A shadow back end
with duplicated data. This protects the data and provides a far
better (more flexible and sophisticated than I could create using a
form and VBA.
Have you ever implemented such a concept?
THANKS!
David G.
 
I want to consider the QBF method further mentioned by Keven. I'm also
thinking about duplicating the Back end file. I'd change the linked
tables link to connect to the shadow back end when on occasion a key
user wants to build a custom query.
Click to expand...

Probably prudent. Unless you REALLY REALLY trust both the ethics and the
technical expertise of your users, giving them free rein of the Queries window
is risky: the DELETE and UPDATE buttons are just right there, and AFAIK cannot
be turned off.
 
Back
Top