Solved

access read-only database

Posted on 2016-09-01
11
17 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 57
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 57
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
 
LVL 47

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 57

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 57
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 47

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now