Getting to Know Microsoft Access, Part 10

Lesson 10: Some Final Things

We’ve covered all the major objects in Access up to this point. However, I would like to use this last tutorial in the series to cover some miscellaneous topics we haven’t discussed yet. In this tutorial you’ll learn how to create charts and graphs, how to improve the performance of your databases, how to create switchboards, how to exchange data with others, how to share, and how to use security to protect your database. We’ll discuss each of these topics in brief.

Charts and Graphs

Access allows you to create many different types of charts and graphs including pie, bar, line, and scatter. Before you create a chart, design it. Ask yourself: what is this chart supposed to show? When you have successfully defined your goal you must decide whether the chart will stand alone, apart from any other Access objects, or whether it will be included inside a form or report. You can link the chart to the underlying data in a form or report so the values of the chart change with the data, or you can set the chart apart from the underlying data, and it will show a static illustration of your data instead. You can even import charts into Access from Excel, for example. Just select the Insert | Chart menu option to do so.

Once you’ve decided on the data you want to use for the chart, either data from one table, or data from multiple tables, you must group the data so it is accessible to your chart. If the data is in one table, you don’t need to do anything new. If, however, the data is contained in multiple tables, you must create a cross tab or select query to group that data into one query structure, from which you can create your chart.

The easiest way to create your first chart, after you have successfully designed your query, is to use the Access Chart Wizard. To create a standalone chart, start a new form or report and choose the Chart Wizard in the New Form or New Report dialog box; select your table (or query) data. Choose the Insert | Chart menu option to start creating a new chart in a currently existing form or report. Once you’ve created your chart, you can modify it in many ways, such as by editing the row source property, editing the legend, or editing the appearance. The following screenshot shows the selection you want to make to create a new chart:

Improving Performance

The two tools you need to know about to improve database performance are the table analyzer and the performance analyzer. Choose the Tools | Analyze menu options to get started using both of these tools.

The Table Analyzer is useful for reducing redundancy in your tables. The Table Analyzer will look at your tables and show you ways to split them apart. The Performance Analyzer, in contrast, looks at all the objects in the database and suggests ways to improve their performance generally.

Switchboards

A switchboard is a dialog box you create that works as a single point of entry into the application and gives the user a list of things they can do when they first open the application. This can be helpful for the user of the database who is not familiar with the object-model and just wants to do data entry or see a report.

To create a switchboard for your application, use the Switchboard Manager by selecting the Tools | Database Utilities | Switchboard Manager menu option. Select ‘Yes’ when prompted to create a new Switchboard. Next, add items to the switchboard. Do so by clicking the ‘New’ button in the Switchboard Manager.

In the New dialog box you’ll specify the label for the item, the action the items runs when selected by the user, and any command arguments (in the Switchboard field). To make the switchboard the startup page for the application, choose the Tools | Startup menu option and click the arrow next to the Display Form/Page box and choose the Switchboard option from the list.

Exchanging Data with Others

To help speed up application development, Access provides tools that allow you to exchange Access objects and data with other Access databases, and other database systems such as SQL databases and dBase.

To copy objects from one Access database to another you first copy the objects by right-clicking them. Then, you open another instance of Access, tile the Windows, and then paste the object wherever you’d like it to be in the new database. You can also drag-and-drop an object from one Access database to another.

You can also import or link data from one Access database to another or from another type of database to Access. Importing is best used when you don’t need the other database application, while linking is best used if you will need the other database application. To import data, choose the File | Get External Data menu option; choose the ‘Import’ option. Select the database file that contains the object(s) you want to import; click import. The Import Objects dialog box allows you to choose which objects you wish to import. Choose ‘OK’ when you are done. If the tables you are importing contain Lookup Fields you’ll want to make sure to import the tables those fields refer to as well.

You’ll want to set import options before you import your data. To do so, click ‘Options’ in the Import Objects dialog box. In the options dialog that follows you can choose whether to import other table features such as relationships, menus and toolbars, and the import/export settings for the source database. You can also set whether to import both the specified tables and the data or just the table definitions. Finally, if you are importing queries, you can decide whether to import just the queries themselves, or the records produced when those queries are run.

As noted, you can also choose to link Access tables instead of importing them. Linking is both good and bad. It’s good because it reduces redundancy by keeping the data where it belongs (in the source database), but bad because it requires you move the linked database whenever you move the destination database. To link your source database tables to your destination database simple choose the File | Get External Data menu option and select the Link Tables menu option. Finally, select the database and the table objects you wish to link to your current database.

As indicated, you can also import from or link to other database types such as Paradox and dBase, Visual FoxPro, and Microsoft SQL Server. To do so, you’ll first need to have the ISAM drivers (for Paradox and dBase) or the appropriate connection to an ODBC data source defined (for SQL Server and FoxPro). To get the ISAM drivers visit the Microsoft technical support website. Other than that, importing data from a dBase or Paradox database uses the same method as for importing data from other Access databases.

If the database you are importing from is not compatible with Access you can always choose to import your data as a delimited text file. Most database types will allow you to export data as delimited text. When you are ready to import into Access, select ‘Text Files’ in the Files of type box in the Import window dialog. Then, locate the text file you want to import and click ‘Next.’ Access will validate the imported data and then ask you if it is what you want. Once you have verified that it is, Access imports the data.

Sharing your Data with Others

To share your database with others you’ll need to put the database in a central server where everyone can access it and then split it up so only certain people can access certain objects. You may also want to replicate the database using synchronization, or put the database on the Internet. The easiest thing to do is to simply put the entire database on a shared, central server. Just put the database in a shared folder that everyone can access and then split up the objects so each user has access on their own workstation to only the objects they need. This reduces network traffic because only data is transmitted. Select the Tools | Database Utilities | Database Splitter to split the database.

Database Security

The main purpose of database security is to prevent unauthorized access and tampering. You can add a password using the Tools | Security | Set Database Password option, but this only really applies to a single user of the database, and besides, it only applies to the initial opening of the database. Security in a multi-user environment is more complex. Not all users get access to every object in the database, but one user must have access to all the objects. The best way to manage this is to organize users into security levels by group. This information is stored by default in the System.wif file. The Access user-level security model uses four elements: users, groups, permissions, and objects.

The default Access security level is ‘Admin.’ To enhance security, you first need to change this so certain groups of users have certain access privileges. You will use the User-Level Security Wizard to accomplish this task. The database is owned by the person who runs the wizard. Choose the Tools | Security | User-Level Security Wizard menu option to get started. Through a series of dialog boxes you’ll choose which users and groups get access to which objects in the database. You’ll also choose passwords and personal IDs for the users. Finally you’ll encode the database.

That’s it for this 10-part tutorial on Microsoft Access. I hope you learned something new and expanded your Access skill set. Let me know if you have any questions or comments. You can email me at sage@grantstation.com.