How can I create an un-updateable database?

[WildlyHarry]

I have a database that is used for data entry among a handful of users. I
need to make a searchable copy of that database where no records can be
added/deleted/edited. The searches are queries that are driven by selections
made on a form. I have it all set the way it needs to be. But I cannot set
it up so that no changes can be made to any piece of the database, especially
the records. Changing the properties to read only will not work because the
users can just change them back. Setting up user roles will not work because
the database will be e-mailed to multiple users operating on multiple
servers. Does anyone have any other ideas? Thank you in advance for your
help.

 

[Mark Andrews]

You could use read-only fields and also make the database into an MDE so
nobody can change the design.

If you don't want an MDE, you could make it more difficult to change the
database by setting things to read-only in code.

However if the users have permissions to change the design of a database
they can always get to the data.

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com

 

[Fred]

Here's the low Tech Fred idea

(Do all of the other stuff just so that the get error messages etc. when
they try to make changes. Or make their access through forms, where you cna
easily make the controsl do what you want.)

Make a copy of the database, and make is to that they are accessing only the
copy. You can overwrite it with the master to wipe any changes that snuck
in.

I do that here with other files that people were messing up. The overwrite
is automated, runs each night.

 

[Paul Shapiro]

If it's really important that users cannot edit the data, you could
investigate Access user-level security, but that feature has been listed as
deprecated and will be discontinued, so it wouldn't be my choice.

You could move the data to a SQL Server backend, retaining Access for the
front-end. In SQL Server, it's easy to establish rigid user permissions, and
the database enforces them so you don't have to. You can create the same
queries in SQL Server, and just give that user group permissions to read the
data in those queries, nothing else. Or more robustly you could create a few
stored procedures to do the searches and just give execute permission on
those procedures.

If redoing the whole application isn't appealing, you could make a SQL
Server db with just the necessary lookup data, and set up a process to copy
data from the live Access db to the read-only SQL Server db every night. SQL
Server has some very good tools (SSIS) for transferring data.

 

[WildlyHarry]

Thank you all for the good suggestions. Unfortunately, the users that I want
to have read only access are dispersed across the country. And I have no
global servers that all of them can access. Therefore, the SQL tables are
not feasible. This also means that I cannot set user level security because
the database will be e-mailed to them and copied to thier individual
machines. I converted the database to an .mde format and the forms are not
editable. But the records can be changed. And for audit purposes, I need to
make sure that no one can edit the records, forms, queries, vba, modules, etc
once I distibute the database. Any other suggestions?

 

[Mark Andrews]

That's fine if you don't have a network you need to distribute the dbs.
If you are using an MDE, can't you also set the forms so that everything is
read-only and not editable (then make it an MDE).

Then the only way they could get to the data is to hold down shift and
access the tables directly.

HTH,
Mark

 

[WildlyHarry]

Good point Mark. But I am not as concerned about the forms as the records in
the table resides on top of the forms. I guess what I am asking ultimately,
is there a way to distribute an access database with an active form that
drives queries, but where the records on the tables are not editable?

Thoughts?

 

[Mark Andrews]

Now I'm confused. If you are making the forms so a user cannot type in any
field and the user can only use the forms, how would they edit/add or delete
data?

You make it so the user can press buttons or pick choices from areas that
are not bound to tables to pick dropdown choices or things of that nature
but any datasheets or bound control are not enabled.

Does that make sense?
Mark

 

[James A. Fortune]

Good point Mark. But I am not as concerned about the forms as the records in
the table resides on top of the forms. I guess what I am asking ultimately,
is there a way to distribute an access database with an active form that
drives queries, but where the records on the tables are not editable?

Thoughts?

You could move the backend data to SQL Server without creating a new
primary key field .

James A. Fortune
(e-mail address removed)

It is turning a disadvantage into an advantage. -- Riva, Star Trek

 

[John Spencer MVP]

I know of no way to completely lock up the data, but you can make it a
challenge for the users

Make queries that return all the fields from each table and set the recordset
type of the query to Snapshot.
-- Name all your existing tables so they start with msys (msysEmployees) -
This will hide the table immediately (Use Tools: Options: View tab and check
to show System Objects until you have built all the queries)
-- Make for each table that shows all the fields. For instance, a query named
Employees for the msysEmployees table and set the query recordset type to
Snapshot. The query will look like
SELECT * FROM msysEmployees
-- Hide all your base queries. (RIght Click: Properties and check hidden) You
will use these base queries as the source for all your queries, reports, etc.

Set the startup options of the database to
-- Not show the database window
-- Not allow full menus
-- Not allow Built-in toolbars
-- Not allow Toolbar/Menu changes
-- Not allow Use Special Access keys
-- Do set a startup form
-- Look for options on how to disallow the shift bypass key
(http://www.mvps.org/access/general/gen0040.htm)

Make the database an MDE to lock down VBA code, forms, etc.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[WildlyHarry]

For my purposes this will work. Thank you