Solved

access read-only database

Posted on 2016-09-01
11
24 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
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…

932 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

12 Experts available now in Live!

Get 1:1 Help Now