New Perspectives on Microsoft Office Access 2003 Tutorial 12

1
Microsoft Office Access 2003

• Tutorial 12 Managing and Securing a Database

2
Analyze a database's performance

• Analysis tools will help you provide a database
  that runs as efficiently as possible.
• The Performance Analyzer will produce a list of
  ideas, suggestions, and recommendations.
• However, many of the suggestions and
  recommendations can be carried out for you.
• If you decide to allow Access to fix the problem,
  you will be able to check the results by viewing
  the objects pertaining to the suggestion.

3
The Performance Analyzer dialog box
4
The Performance Analyzer analysis window
5
View and print database documentation

• If you want to print a graphical view of the
  relationships in your database, you can have
  Access print the relationships window.
• This will print exactly what you see when you
  open this window.
• If you need to have more detailed information
  about the objects in the database, you can run
  the Documenter.
• In the Documenter, you can choose what objects to
  include in the Documenter's report and you can
  make several choices as to what should be
  included in the report.
• When you are finished making your selections, you
  will received a very detailed report containing
  Field names, Field types, Field sizes,
  relationships with other objects, etc.

6
Link a database to a table in another database

• There will be times when you will want to make an
  Access table available to a user for data entry,
  but you would not want them to be able to change
  the structure of your table.
• You can do this by providing a linked table in
  the database.
• A linked table is a table that is stored in a
  file outside the open database.
• Access will be able to update and retrieve
  records in the open database but will disallow
  changes to the structure of the table.
• When you establish a link to a table in the
  database, the linked table shows up on your list
  of table objects but the icon looks different.

7
The Link Tables dialog box
8
Use the Linked Table Manager

• When you link to a table, Access keeps track of
  where that table is stored.
• If you move the location of the database to a
  different folder, Access won't know where it is.
• You can use the Linked Table Manager to refresh
  the link to the table so that Access will be able
  to find the linked table.
• In the Linked Table Manger, point to the new
  location, and Access will then update the link
  information.

9
The Linked Table Manager dialog box
10
Use replication to create a Design Master and
replica of a database

• Sometimes there will be multiple users updating
  database data.
• If you gave each one a copy of the database and
  allowed them each to make changes in their copy,
  it would be quite difficult to pull all the
  changes together later into a single database.
• However, replication makes it easy to handle this
  kind of activity.
• When you create a replica of a database, you
  create a special copy of the database and the
  original copy becomes the Design Master of the
  database.

11
Update Design Masters from replica sets

• The Design Master and all of the replicas that
  have been made of it are called the replica set.
• Once you have created a replica set, Access keeps
  track of the changes that eventually will be
  incorporated into the Design Master.
• Any changes in the data in any replicas can be
  updated automatically in the Design Master
  through a process called synchronization.

12
The Database Window for a Design Master
13
Synchronize the Design Master and replica Data

• When you have a replica set, you can synchronize
  the records in the replica database(s) by running
  the Synchronize Database process.
• When the synchronization process is run, Access
  will check for changes in the data in the replica
  database(s) and, if any, will incorporate those
  changes into the Design Master.
• After the synchronization process is complete,
  you will be able to see the changes that have
  been made in the replica database(s) in the
  Design Master database.
• When you no longer need the replica(s), you
  should delete them.
• If there are no replicas being used, you should
  rename the Design Master to its original name.

14
The Synchronize Database dialog box
15
Split a database

• A database developer often will separate the
  interface to a database (its queries, forms, and
  other database objects) from the actual database
  data. This is known as splitting the database.
• The Database Splitter splits the database into
  two databases.
• The database that contains the actual data is
  called the back-end database.
• The database that contains the other objects is
  called the front-end database.

16
The Database Splitter dialog box
17
Front-end versus back-end database

• The users of the database will work through the
  front-end database to add, modify, and delete
  data in the back-end database.
• The developer does not make changes to the
  back-end database but periodically makes changes
  to the front-end database by updating its
  queries, forms, and reports.
• In a network environment, the back-end database
  is usually stored on the network where users can
  access it.
• The Front-End database is stored on all the user
  computers, as it is their interface to the
  back-end database.

18
An illustration of splitting a database
19
Set database startup options

• There are several options that can be set to be
  initiated at the time the database is opened.
• These options are called Startup Options and can
  be set by clicking Tools on the menu bar and then
  click Startup.
• On this Startup dialog box, you can specify a
  particular form you want to display when the
  database is opened.
• You can also choose from several options related
  to how much access users will have to the menu
  and toolbars.

20
The Startup dialog box
21
Implement database security

• Databases often have sensitive data that must be
  protected.
• Security refers to the protection of a database
  against unauthorized access.
• Startup options offer a degree of security but
  there is more that you could do to protect the
  data.
• You can use Encryption, Decryption, and Passwords
  to protect your database.

22
Encrypt and decrypt a database

• Encryption is a process that will convert the
  data in a database to a format that is
  unreadable.
• If a user is able to gain access to a database
  that is encrypted, the data could only be read if
  it was decrypted
• Decrypting a database is the process of returning
  the encrypted data to its original state.
• Another form of security includes the use of
  passwords.
• Passwords applied to a database makes it
  impossible to open the database unless you know
  the password

23
Set and unset a database password

• If data is very important and meant to be viewed
  only by authorized users, it is a good idea to
  assign a password to the database.
• When you set a password, you usually establish
  exclusive access, meaning that only one user can
  access the database at a time.
• You can also set the password so that the
  database is opened as Read-Only, meaning that no
  changes can be made to the database.
• In order to remove the password, you must use the
  password to get into the database and unset (or
  cancel) the password.

24
The Set Database Password dialog box
25
Implement user-level security

• The security measures discussed in previous
  sections apply to all users of the database.
• Sometimes you will want to allow some users
  access rights and disallow those rights for other
  users.
• You can accomplish this by setting user-level
  security.
• User-level security lets you grant users or
  groups of users specific permission to tables,
  queries, forms, reports, and macros.

26
Assign users permissions to a database

• You can assign each user a set of permissions,
  which specify what that particular user has
  rights to.
• You can specify that a user can modify data or
  can only read data.
• You can specify whether the user is allowed to
  delete data.
• This kind of activity is usually managed by a
  person designated as the database administrator,
  or a group of people called the database
  administration (DBA) group.

27
Assign group permissions for multiple users

• The DBA will be responsible for controlling and
  assigning permissions to certain users.
• Because there are often groups of people who
  should have the same permissions, the DBA can
  assign permissions to groups rather than
  individuals.
• Usually there is a workgroup information file
  that maintains group names, user names,
  passwords, and group membership information.
• This information is stored in hidden files and is
  available only to the DBA.

28
An illustration of user-level security
29
Use the Security Wizard to assign permissions
30
Protect VBA code from unauthorized changes

• Database developers often work long, tedious
  hours writing VBA code to augment the
  functionality of the database.
• Usually, the developer will want to protect the
  code from unwarranted changes.
• The code is readily available in the modules of
  the database unless the code is converted.

31
Create an MDE file

• You can save a database as an MDE file, which
  compiles all the VBA code into machine code and
  removes the VBA source code.
• The database will still work exactly as it did
  prior to this conversion, but no one will be able
  to alter the code because they will not be able
  to get to it.
• In Access 2003, you must make sure the database
  is in 2002 format before you try to convert it to
  an MDE file.
• This process is not always necessary and should
  only be done if there are many VBA modules in the
  database and the code must be protected from
  changes.