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.

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

"WildlyHarry" <(E-Mail Removed)> wrote in message
news:346D0733-D111-4DC2-B707-(E-Mail Removed)...
>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.

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.

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" <(E-Mail Removed)> wrote in message
news:346D0733-D111-4DC2-B707-(E-Mail Removed)...
>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.

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?

"Paul Shapiro" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:346D0733-D111-4DC2-B707-(E-Mail Removed)...
> >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.
>
>

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" <(E-Mail Removed)> wrote in message
news:0071C6F6-44A7-4DA6-BEF7-(E-Mail Removed)...
> 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?
>
> "Paul Shapiro" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:346D0733-D111-4DC2-B707-(E-Mail Removed)...
>> >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.
>>
>>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0071C6F6-44A7-4DA6-BEF7-(E-Mail Removed)...
> > 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?
> >
> > "Paul Shapiro" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:346D0733-D111-4DC2-B707-(E-Mail Removed)...
> >> >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.
> >>
> >>
>
>
>

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

"WildlyHarry" <(E-Mail Removed)> wrote in message
news:5BD809D6-BEB1-4611-96F9-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:0071C6F6-44A7-4DA6-BEF7-(E-Mail Removed)...
>> > 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?
>> >
>> > "Paul Shapiro" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:346D0733-D111-4DC2-B707-(E-Mail Removed)...
>> >> >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.
>> >>
>> >>
>>
>>
>>

WildlyHarry wrote:
> 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 Removed)

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

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