Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

access read-only database

Posted on 2016-09-01
11
Medium Priority
?
49 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 59
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 59
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

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 59

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 59
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 49

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

578 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