?
Solved

access read-only database

Posted on 2016-09-01
11
Medium Priority
?
40 Views
Last Modified: 2016-09-01
Hi Folks
how does one place a database into read-only mode with respect to data.
The /ro switch stops editing of objects: I wish to stop editing of data, but allow users to open all forms, queries, tables and reports for viewing. (ideally including sorting and filtering but obviously not saving the 'view')
thanks
0
Comment
Question by:COACHMAN99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 58
ID: 41780132
If this is a JET based data store, then you can't protect the tables themselves.  You have to limit access to data via forms.

If a SQL server backend, then it's just security on the tables/roles.

Jim.
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41780138
this is a 2010 ACE FE/BE arrangement.
is there any easy way to open the FE in R/O mode that blocks editing data, but allows all viewing?
thanks
0
 
LVL 58
ID: 41780145
<<is there any easy way to open the FE in R/O mode that blocks editing data, but allows all viewing?>>

 No.  Your only way to limit access to data is via forms.

 and I should have been clearer with my last comment.  A JET based BE (.MDB), yes as workgroup security is available.   If ACE (.accdb), then no as workgroup security was deactivated.

 I always think of "ACE" as being nothing more than JET (really it was more of just a name change), and I assumed you were using a later version.

 I should have been clearer when answering.

Jim.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 41780186
If you hide the navigation pane and disable Access special keys in the Access Options, then your users will only have access to the database forms that you provide for them.  This can be bypassed, but not easily by your basic user.

If you need to ensure that some only have read-only Access, then you need to include some form of UserPermissions table, where you identify what permissions your users have.  This may be as simple as a table with their Windows login and a checkbox for ReadOnly.  When they log in, your program would determine whether they have ReadOnly permission and when each form is loaded, you would use the Form_Current event to loop through each of the textbox, combo, list, and checkbox controls to set the Locked property based on the value in the ReadOnly field of that UserPermissions table.  This is not as difficult as it might appear, because you can write a single procedure to do the locking/unlocking for you, and pass that procedure the form object that you want it to act upon.

Alternately, the UserPermissions could be more complex where you assign people to roles and provide specific permissions based upon the roles they are assigned to.
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41780200
Thanks Jim and Dale
that (unfortunately) confirms my unhappy suspicions.
I was hoping to avoid coding permissions for thousands of controls on many forms with a simple switch.
My next choice would be to make all form-recordsets snapshots - presumably this would be quicker than for individual controls?
My objective is to improve performance across the system by limiting db activity to the data-entry people, and 'lock' all query/reporting/viewing users.
cheers
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 41780205
<<My next choice would be to make all form-datasources snapshots >>

  Don't use snapshots. Instead, change the "Allowxxxx" properties to false.

 Using a snapshot means JET will lock all the records (or the table) and make a copy of them locally, which is a big performance drain.

Jim.
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41780211
That is bad news. Are we 100% sure the snapshot will make things slower?
and that setting form/data permissions (allow...) will improve performance (over current state)?
cheers
0
 
LVL 58
ID: 41780225
<<That is bad news. Are we 100% sure the snapshot will make things slower?>>

 Depends on your recordset sizes, but in general yes.

 While a "Snapshot" sounds fast, what your actually asking JET to do is give you the way records look at a given point in time.

 The only way it can do that is to make a complete copy of every record.

Jim.
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41780238
Thanks Jim and Dale
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41780276
You really are not talking about a lot of code to lock/unlock controls.  A single procedure would do it:

Public Sub FormLockUnlock(frm As Form)

    Dim bReadOnly As Boolean
    Dim strCriteria As String
    Dim ctrl As Control

    strCriteria = "[UserID] = '" & fosusername() & "'"
    bReadOnly = Nz(DLookup("ReadOnly", "UserPermissions", strCriteria), -1)

    For Each ctrl In frm.Controls
        
        Select Case ctrl.ControlType
            Case acCheckBox, acListBox, acComboBox, acTextBox
                ctrl.Locked = bReadOnly
            Case Else
                'do nothing
        End Select
        
    Next

End Sub

Open in new window

You can get the API code for the fOSUserName function here.

Then, in the Form_Open event of each of your data forms, you would include a line of code:
FormLockUnlock me

Open in new window


By default, the subroutine above would give everyone whose userid had not been entered into the UserPermissions table ReadOnly permissions.
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41780331
Thanks Dale
that is very useful and worth considering.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question