Solved

access read-only database

Posted on 2016-09-01
11
37 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 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 250 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 (Access MVP)
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 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