Locking and Transactions using MS Access

1
Locking and Transactionsusing MS Access

• Shaun Simpson
• MIS 372
• WSU Vancouver

2
Locking and Transactions

• Locking Strategies
• Bound Forms
• Locking Options
• Visual Basic Procedures
• Transactions

3
Locking Strategies

• Visual Basic Procedures
• More Flexible
• More Options
• May meet needs better
• Must be programmed
• Need to handle errors in code

• Bound Forms
• Easy
• Three levels
• May not meet needs

4
Bound Forms

• Three Levels of Locks
• No Locks - records can always be edited
• All Records - all underlying tables or queries
  are locked.
• Edited Record - page that is edited is locked
• To Set For All Forms Tools, Options, Advanced--
  Default Record locking
• Individual Form RecordLocks property of the form

5
Locking Options

• Number of Update Retries of retires of locked
  before displaying error
• Update Retry Interval milliseconds after which
  access tries to save a changed record that is
  locked by another user
• Refresh Interval seconds after which Access
  automatically updates records
• ODBC Refresh Interval refresh for ODBC records

6
Visual Basic Procedures

• 3 Levels of Locking Options
• Exclusive modes - prevents others from using
  database (read-only)
• Recordset locking - locks a Recordset objects
  underlying tables
• Page Locking - locks the 2K page where the data
  being edited resides (pessimistic or optimistic)
• All three modes can be used at different times or
  during specific processes.
• For example Page lock the Orders table during
  data entry. At the end of the day, set a
  Recordset lock on a summary table and update it
  with summary data. Use Exclusive each night to
  compact the database.

7
Microsoft Jet Transactions

• Visual Basic - DAO methods
• Workspace.BeginTrans
• Workspace.CommitTrans
• Workspace.Rollbak
• Up to 5 levels of transactions at one time
• Does not work on external data sources
• See help topic BeginTrans, CommitTrans, Rollback
  Methods for an example

8
Users and Groups

• Users and Groups (Tools, Security)
• Admin - default user - full permissions, all
  users
• Admins - default group - Change permissions,
  modify users and groups, clear passwords.
• Users - default group account

9
Permissions

• Explicit (User)
• Implicit (Group)
• Least Restrictive of explicit plus implicit
  permissions
• Create Groups and assign permissions to groups
• Permissions can be changed by
• Member of Admins group
• Owner
• Any user with Administer permission
• New Objects Default permissions ltNew Objectgt

10
Ownership

• User who creates object
• Owner can always grant permission
• Database Owner - can create objects and grant
  permission

11
Changing Ownership

• To change ownership
• Change Owner- Tools, Security, User and Group
  Permissions, Change Owner.
• Recreate object - copy, import, export
• Database Owner - must create new database and
  import all objects--User-Level Security Wizard
• To recreate an object you must have Read Design
  permission for object, for table or query Read
  Data permission
• Note Recreate does not change RunPermissions
  property set to Owner, you must own a query or
  RunPermission has to be set to user.

12
User-Level Security Wizard

• Create secure Workgroup information file
• Tools, Security, User and Group Accounts
• Create new user to be owner and administrator of
  database. Add that user to Admins group.
• Assign a password to the admin user. Causes
  logon to appear next time you start access.
• Remove Admin user from Admins group
• Exit access and login as the administrator you
  created. Open the database you want to secure.
• Select the objects you want to secure If you
  chose all objects the Users group Open/Run
  permission is removed from the database and only
  members of the Admin group can open and run the
  database.
• Create your own users and groups assigning
  appropriate permissions to the group

13
Assigning Permissions

• Users and Admins Group
• Assign appropriate permissions to these groups
• New users are added to Users by Default
• Your own Groups and Users
• For finer security
• Assign permissions to Groups
• Typical Permissions
• Tables and Queries - Read / Update Data
• Forms, Reports, Macros - Open / Run

14
Security without Log In

• Follow User-Level Security Wizard
• As member of Admins, assign permissions to Admin
  user for objects you want available to everyone.
  (Admin should no longer be member of Admins.)
  Users auto login as admin.
• Clear the password for Admin user.
• Do not distribute workgroup information file
  except to administrators.