Microsoft® Access 97 Quick Reference
Each table, query, form, and report is a database object that you can copy, rename, create a description of, and set properties for. You can create database objects by importing them from another Access database. You can create tables in Access by importing them from or linking them to another data source. You can import as well as export files.
To keep your database in good shape, you need to work with several procedures. Backing up your data is the most important of these operations; however, compacting the database and repairing will be necessary at times. If you are on a network with multiple users, you may want to explore replication to decrease network traffic while still updating your databases. With multiple users, you also need to consider security--that is, who should and who should not have access to the database file and the objects inside.
It is very important to back up your database in order to protect your data. Access automatically saves results to disk, and will overwrite data based on queries and other actions you perform. Often, your backup is the only protection you have from data loss.
You can also copy the database file using any of the following methods: Windows NT Explorer, Microsoft Backup, MS-DOS COPY command, or any other backup software that works with Windows 95.
TIP: Compact the database before you back it up to save disk space. (See "Database: Compact.")
The backup file name is Copy of <Your Database Name>.
NOTE: If you use the security features of Access, you should also back up the workgroup information file occasionally. In Access 1 and 2, the default name for the file is SYSTEM.MDA. In Access 95 and 97, the default name is SYSTEM.MDW.
Access does not create a new record until you actually enter data into the first field of a new record.
Not only can you back up the entire file, you can also back up individual objects such as a form or report before you make a major change to the object. For example, if you experiment with action queries, a good idea would be to copy the underlying table.
NOTE: When you copy a table with copy and paste, Access asks you if you want to copy just the structure (design), the structure and data, or append the data to an existing table. Copying the structure enables you to modify the table for another use. Copying the structure and data creates a backup copy of the table. Append is an alternative to an Append Query. (See "Action Query: Append Query" in the Queries and Filters part of this book.)
TIP: When you use this copy feature for backup purposes, keep your naming consistent. For example, name the object "Backup of <original name>" or "ZZZ of <original name>." This will put all backups in one spot. When you're done with the backups, delete them (see "Database Object: Delete") and Compact the database (see "Database: Compact").
If you no longer need an object (for example, if you have created a backup), it is a good idea to delete the object from the Database window to save space and then compact to speed up the database. (See "Database: Compact.")
CAUTION: Deleting an object is an irreversible operation. Make sure you truly want to delete the object. Deleting the object will affect any other object that is based on the deleted object. For example, when you delete a table, any related table, query, report, or form will not work.
In addition to long names of up to 64 characters for the objects, you can also have descriptions for each object in the Database window. To see the descriptions, click the Details button on the Database toolbar.
In addition to the description, each object has two other properties: Hidden and Replicable. You can hide an object if you want it out of the way or you don't want the user to know about the object. With the Replicable property, you choose whether or not you want this object replicated (copied) when you replicate your database. (See "Replication: Using Briefcase.")
To unhide an object you must first be able to see it. Here is an inherent contradiction. However, you can show all hidden objects by choosing Tools, Options, clicking the View tab, and checking the Hidden Object box.
When the current name of the object needs to change you can rename the object. After you import a table (see the Import Data tasks in this part), the table name is the same as the old file name. You might want to change the object name in this circumstance and when you copy an object. (See "Database Object: Copy.")
CAUTION: If you rename a table or query that is used in a form or report, the form or report will no longer work. You can change the Data Source property to re-establish the connection to the table or query (see "Forms and Reports: Data Source" in the Forms and Reports part of this book).
Just as each individual object has properties, so too does the entire database. The database properties include summary information such as author name, subject, comments, and keywords. If you are using hyperlinks (see "Hyperlinks: Create" in the Table and Database Design part of this book), the Hyperlink base enables you to specify the initial folder for all relative links. The Contents tab of the Database Properties dialog box provides the names of all the database objects (just like the Database window). If you want, you can store additional information about your database through the Custom tab.
NOTE: The General tab of the Database Properties dialog box shows file attributes (such as Read-Only, Hidden, or Archive) but the check boxes are grayed so you can't make a change. If you want to change these properties, close the database, choose the Open button on the toolbar. Right-click the file name and choose Properties.
Access stores all of its objects and data in a single file. As you delete the information in tables and the tables themselves, not all of the space is reclaimed efficiently. Therefore, every so often you should compact your database to shrink its size, remove free space, and improve performance. During compacting, Access checks data and validates database structure.
To compact the current database, choose Tools, Database Utilities, Compact Database.
NOTE: If you delete records at the end of a table with an AutoNumber field, Access normally skips these numbers. When you compact a database, Access resets the AutoNumber field so that the next record added is one more than the largest existing AutoNumber. Because compacting improves the efficiency of your database, you might want to programmatically build compacting into your application. The VBA statement for compacting is DBEngine.CompactDatabase olddatabase, newdatabase.
When you try to open a database created in a prior version of Access, you will be prompted on whether you want to convert the database or Open the database. When you open the database you can use a prior version to enter data, but you cannot change the design or create a new object.
The database opens and is converted into your new version of Access.
NOTE: If you open the old version database (rather than convert it), Access might not bring up the Convert dialog box again. In this case, close any database and choose Tools, Database Utilities, Convert Database; then, choose the database and give it a new name.
If you store most of your database files in one folder, you can have Access automatically go to that folder when you first start Access and choose to open a database.
You can repair a database that isn't currently in view. The process is only slightly different than repairing an open database. (See "Database: Repair Open Database.") You might try to open a database and Access informs you that the database needs to be repaired before it is opened. Your database might be corrupted because the power went off while the database was open.
Access will perform data validation and other procedures, and repair the database, if possible.
CAUTION: Access isn't always capable of repairing badly damaged database files. You should always maintain an active system for backing up your database so that you can revert to your last backup should your current file be unusable.
When you open, compact, encrypt, or decrypt a database that is damaged, Access will inform you of the damage and post a dialog box that offers to repair it. In instances when you find erratic behavior (maybe tables don't sort correctly or a report takes an unusually long time to run), you might want to initiate the procedure of repairing a database manually before you get an error message.
NOTE: The VBA statement for repairing a database is DBEngine.RepairDatabase databasename. If you are designing an application for other users, you may want to include a command button for repairs or do automatic repairs somewhere in your code.
You can convert Access tables, queries, forms, and reports to Excel worksheets by using the Office Links feature. Excel's strength in this instance is its capability to analyze the data.
This process Opens Excel, converts the selected object, and saves the name of the object with an XLS extension.
If the object was a form, Excel creates one row for each record with the field names as the first row. Excel ignores any information on a subform. If the object was a grouped report, the Excel worksheet is in outline format, enabling you to show or hide detail.
If the file format does not appear on your Files As Type when you are exporting or importing data (see "Export Data: Access to Another File Type"), you might need to install additional data drivers. Access has two sources for these drivers (some of which overlap): the setup program and the ValuPack folder. (See "Export and Import: Installing Additional Drivers Through ValuPack.")
If you cannot get drivers from the setup program (see "Export and Import: Installing Additional Drivers Through Setup") you can also look at the ValuPack. This is especially necessary for Lotus 1-2-3 and Paradox files.
If you need to export any objects from one Access database to another Access database, you can use the File Save As/Export menu choice. Another option is to import from an Access database. (See "Import Data: Access.")
Access copies the object (or table definition) to the database you indicated.
TIP: Exporting tables in this manner works for all versions of Access. If you are exporting to Access 95 or 97, you can use the same procedure to copy queries and macros from one Access database to another.
Access can export to different database file types, or to other data file formats. Single records, multiple records, tables, queries, forms, and reports can all be exported depending on the file type.
Access creates a file in the format you specify. If a file doesn't support long table names, as is the case for FoxPro 2.5 for example, Access truncates the field names appropriately in the conversion. In some cases, you may need to install the file driver for the external program. (See "Export and Import: Installing Additional Drivers Through Setup" and "Export and Import: Installing Additional Drivers Through ValuPack.")
When you export a table or query, Access offers you the following file formats as types: Access files; text files (.TXT) in either delimited or fixed width format; Microsoft Excel 3, 4, 5-7, 97; HTML files; dBASE III, IV and V; Microsoft FoxPro 2.x and 3.0; Microsoft Word Merge; Rich Text Format (.RTF); Microsoft IIS 1 and 2 O (Internet Information Server); Microsoft ActiveX Server; and ODBC Databases. When you purchase the Office 97 ValuPack, included are drivers for conversion to Paradox databases versions 3.x, 4.x, and 5.0; and Lotus 1-2-3 versions 2 and 3.
NOTE: When you select text files as your export type, Access opens the Export Text Wizard, which enables you to set the format of your text to Windows (ANSI), DOS, OS/2 (PC-8); date, time, and number format; and which fields get exported. This wizard also lets you select whether you create a delimited or fixed width text file. (See "Export Data: Text Files.")
Access 97 ships with HTML templates that you can use to create Web pages of a particular style. They are stored in the <path>\Program Files\Microsoft Office\Templates\Access folder by default.
Publishing dynamic HTML files to a Web server requires that you determine the format that your particular server requires. For Microsoft IIS, that format is IDC/HTX files; for ActiveX servers, that format is ASP files.
You can create view forms that display records; data entry forms that add, modify, or delete records; or switchboard forms that navigate to other Web pages. The forms appear similar to the way they look in your database.
NOTE: When you create a form, you can only output it as an ActiveX Server (.ASP) file. When a form is exported to HTML, most controls become ActiveX controls and all Visual Basic code associated with the controls is ignored. All data types are output as unformatted text, and the Format and InputMask properties of the controls are also ignored.
Exporting to text files is common because most database, spreadsheet, and word processing programs will accept this format when another format is not available. There are actually two formats for text files: Fixed Width and Delimited. Fixed Width is the less common of the two. Each field in a record is a set width (first name goes from positions 1-8, last name is 9-15, and so forth). When you use this option, the Export Wizard enables you to manually change the width between columns. Delimited is the more common of the text file formats and is described in the following steps. Delimited means that there is some character (usually a comma) separating the fields. Text also is usually indicated by quotes.
To begin this procedure, first follow the steps in "Export Data: Access to Another File Type" in the previous task.
You can convert Access tables, queries, forms, and reports to Word documents by using the Office Links feature. You might want to use Word for its formatting capabilities and to add additional text describing the data. Access also enables you to create a Word mail merge. (See "Mail Merge to Word" in the Outputting part of this book.)
This process Opens Word, converts the object, and saves the name of object with a RTF (rich text format) extension.
If the object is a table, query, or form, Word creates a table. Word ignores any information on a subform. If the object is a report, Word creates tabbed entries.
When you import data, you store the data in an Access table. When you import a text file or spreadsheet, you can append the data directly to a table. When you import from a database table the data goes into a new table. Then you can use an Append query to add the data to another table in your database. (See "Action Query: Append Query" in the Queries and Filters part of this book.)
You begin importing the same way, regardless of the data type. If the data type is not in your Files of Type list, you may need to install a driver. (See "Export and Import: Installing Additional Drivers Through Setup" and "Export and Import: Installing Additional Drivers Through ValuPack.")
If the file is a database type file from another application (dBASE, FoxPro, or Paradox), these steps are sufficient to import the database to create an Access table.
If the file is text, see "Import Data: Text." If the file is a spreadsheet (Excel or Lotus 1-2-3), see "Import Data: Spreadsheet." If the file is an Access database, see "Import Data: Access."
NOTE: After you import the data, Access creates an object in the Database window. If the import procedure does not prompt you for a new table name, Access makes the table name the same name as the file. Access does not overwrite existing objects, but instead avoids duplicate names by adding numbers to each imported table sequentially (such as Employee1, Employee2). After importing, you might consider renaming the object. (See "Database Object: Rename.")
If you want to copy objects from another Access database, you can use this import procedure or export. (See "Export Data: Access to Access.") You first need to follow the steps in "Import Data:1st Steps" in the previous task and choose an Access database file.
Access adds each of the objects into their appropriate places in the Database window.
When you import data between different sources, you need to make sure the import has a reasonable chance of success. If the data in your original source is not all of the same type, Access will convert the data type to text or another data type. If the field names are invalid for Access, the import might not work at all.
In some cases, it might be easier to update the data in the original source. In other cases (especially when you no longer have the original program), you have to update the data in Access. It would be unusual if you didn't have to clean up some data. This is notably true when you are importing data into an existing Access table versus importing the data into a new table.
Before you even launch Access, check the following in your old program.
TIP: For information concerning update queries from Step 2, see "Action Query: Update Query." For material supplementing Step 4, see "Queries: Create," "Queries: Duplicates: Remove," and "Unmatched Queries" in the Queries and Filters part of this book.
When you import data, you create a copy of the data in a table in your Access database, but you leave the original data source intact. You can import an HTML table or list data source.
Imported data is copied into your database and can be altered. Imported data is an independent copy of the original data.
NOTE: The Advanced button on every step of the Import HTML Wizard enables you to see or change the choices you made for the preceding Step 4, as well as identify delimiters and symbols for dates, times, and numbers, and save this specification or retrieve another.
When you import a spreadsheet (Excel or Lotus 1-2-3 data files), you have the option of importing a range or an entire sheet. You first must follow the steps earlier in this section ("Import Data: 1st Steps") to choose an Excel or Lotus 1-2-3 spreadsheet file. The spreadsheet must be in the standard database format, with each row being a separate record and each column a different field.
Text data can be from many sources, including mainframes. If your data is not in one of the other sources, this might be the only option for you to move your data from your program to Access. Text is often identified with quotes and the fields separated by commas. This is called a delimited text file. Another option is for every field to be a set number of characters. This text file is called fixed width. You first need to follow the steps earlier in this section ("Import Data: 1st Steps") to choose a Text file.
NOTE: The Advanced button on every step of the Import Text Wizard enables you to see or change the choices you made for the preceding Steps 2 and 4, as well as identify delimiters and symbols for dates, times, and numbers, and save this specification or retrieve another.
Although it is more rare than exporting to Word and importing from other file formats, there might be an occasion when you need to import text from Word into an Access database. The text needs to be in tab or table format. Each column is a field and each row is a record. You might need to design a table first in Access to accept Word's data. (See "Database: Create Blank" in the Table and Database Design part of this book.)
The process of linking to a table in Access is similar in to importing a table. (See "Import Data: 1st Steps.") With linking, the original table serves as the data source and only a reference to that table is contained in your Access database. When you link to a table, you can view and often also modify the data in that table. Access takes care of the details of opening the table and saving it in the appropriate data format. Linking can be contrasted with importing. When you import a new table, a copy of the table is created in the Microsoft Access format. The source table is left intact.
What happens next depends on the data source you selected. See the following sections for notes on the various data types.
For Access, unencrypted Paradox tables, or spreadsheets, Access tables are imported directly. For an encrypted Paradox table, you will be prompted for a password. A linked Access table has an arrow with the icon in the Tables tab of the Database window. Paradox shows a Px icon.
When you link to dBASE and FoxPro, an index is requested. Normally, that index is the primary index, but it can also be candidate indexes for these two database programs. To start, first follow the steps in the earlier task, "Links to External Data: 1st Steps." After these initial procedures, complete the following steps.
When you link to a dBASE table, Access' Database window shows the dB symbol, FoxPro shows a fox icon.
Before you link to an Excel file, the data must be in the appropriate format for a database. Columns will identify fields (with the field name usually at the top row) and rows indicate records. To start, first follow the steps in the earlier task, "Links to External Data: 1st Steps." After these initial procedures, complete the following steps.
The Tables tab of the Database window shows a linked Excel spreadsheet indicated by an Excel X icon.
When you link data, you store a reference to that data object in its original location, and generally you can modify or update the data from within Access. HTML linked data is read-only. You cannot change the data from within Access.
Access imports or links to each table or list in an HTML file as if it were an individual table. You will need to repeat this procedure if your HTML file contains two or more tables or lists in it.
NOTE: The Advanced button on every step of the Link HTML Wizard enables you to see or change the choices you made for the preceding Step 5, as well as identify delimiters and symbols for dates, times, and numbers, and save this specification or retrieve another.
Linking to a text file involves the same steps as importing to a text file. First, follow the steps in the earlier task, "Links to External Data: 1st Steps." After these initial procedures, complete the following steps. You might need to use this procedure rather than import the data if you have other programs that use this text file.
The Tables tab of the Database window shows a linked text file indicated by a small notebook icon.
For details on the Import Text Wizard, see also "Import Data: Text."
NOTE: The Advanced button on every step of the Link Text Wizard enables you to see or change the choices you made for the preceding Steps 2-3, as well as identify delimiters and symbols for dates, times, and numbers, and save this specification or retrieve another.
If you no longer need to view the data from a linked file, you can delete the link. Deleting the link does not delete the data--it only deletes the access to the file.
If the source files for the links move, you will not be able to view or edit the information in the files unless you update the location for the links.
NOTE: If you want Access to ask you for the file locations each time you open the database, check Always Prompt for a New Location on the Linked Table Manager dialog box.
If you distribute database applications to other users, you might want to remove some options to keep users from modifying your work. An MDE file removes the capability to design or edit forms, reports, and modules (VBA programming). The database also runs quicker because it is compiled and compacted during the process of making the MDE file.
CAUTION: Retain your original database file. This is where you will make design changes. The design of MDE files cannot be modified (and you will have to re-create and redistribute your MDE file when you make a change).
Access temporarily closes your database file and then reopens the original file. To open the MDE file, choose the Open database button on the toolbar and under Files of Type, choose MDE files (*.mde). Double-click the MDE file name in the file list.
There are a number of scenarios in which you might want to create a replica of your database. If you work on a laptop, you would want a copy of your company's database. If you create and use your replica, the changes you make on your laptop are replicated into the main database when you synchronize the replica. (See "Replication: Synchronize.") You might also want to use replication for distributing updates of your software development or for backing up the database.
When you make a replica, you will have two files: the Design Master and the Replica. You can change the design of the Design Master but not of the Replica. You can change data in either of the files.
When finished, the Design Master opens. When you open up either the Design Master file or a replica, Access indicates Design Master or Replica in the Database window title bar.
One of the benefits of using the replication feature is that if your Design Master is damaged you can use one of the replicas as backup and upgrade it to the Design Master.
CAUTION: If you made design changes to your Design Master since the last time you synchronized, these design changes will not be in the replica. For this reason, do not recover the Design Master until you have attempted to repair it. (See "Database: Repair Closed Database.")
To do this task you must first synchronize the Design Master and Replica. (See "Replication: Synchronize.") If you made a change to the same record in the Design Master and a Replica, there will be a conflict. Which change do you want to keep?
You need to resolve conflicts from the replica. You can resolve conflicts directly from the menu as listed here or you might be finishing another procedure. If you are at the end of synchronizing replication and Access is prompting you to resolve conflicts, you will be at Step 3. When you open the replica, you might also be prompted to resolve conflicts at Step 3.
NOTE: It is possible that changes were made to different fields in the same record and that you want information from both databases. In Step 4, you can Copy (Ctrl+C) and Paste (Ctrl+V) items back and forth between the records. You can also type in the record you want to keep.
When you want to check for conflicts with the Design Master and Replica, you first need to synchronize the two database files. You must first have created a Replica (see "Replication: Create Replica") before proceeding with these next steps.
If you are in the Replica, you can choose to resolve the conflicts now or later. (See also "Replication: Resolving Conflicts.")
With the Briefcase Replicator, you can reproduce an Access database and transfer that file to or from another computer. The database is converted to a Design Master, and a replica is created. This method is used to work with a replica database on a laptop. When you connect the laptop with the replica to a network or computer with the original copy, you can synchronize the changes made in both copies so that both databases are updated.
Drag the database into the Briefcase file on your desktop, a Design Master and the replica are created. On a server, the Briefcase may contain replicas for each use in the office. You cannot distinguish between the Replica or the Design Master by looking at the file name. However, when you open the file, the title bar of the Database window will include Design Master or Replica.
Unlike Word, Excel, and other programs, you do not have to constantly save your file to avoid losing changes. When you move off a record (in a datasheet or form), any changes to the data are automatically saved. You can save before you move off an edited record, but this is unnecessary because even the process of closing the table, query, or form will automatically save changes to the record. However, if you are designing any object (rather than inputting data), you will need to save your file for the changes to be accepted. The design of any object (table, query, form, report) needs to be saved if you want to see the changes again.
If the save button is dimmed, there is nothing to save.
User-level security limits particular objects in a database that a user or group of users can read or write to. Here, a user account is created and a username and password is associated with it. Groups of users can be given specific privileges, and users can be associated with accounts. This information is stored in a workgroup information file.
The workgroup information file that comes with Access (SYSTEM.MDW) is not secure because every copy of Access has this file. Before you implement security, you need to create and join a new workgroup.
CAUTION: Write down the name, organization, and Workgroup ID, and keep this information in a secure location--you will need it if your file is damaged and has to be re-created.
CAUTION: Backup your workgroup information file when changes are made. If the file is damaged you won't be able to open your databases.
When you encrypt a database, you scramble its data and definitions, making the file unreadable to anyone trying to decipher the data from another program. You use encryption in conjunction with user-level security. (See "Security: User Level.") Encryption also compacts the database file. When you decrypt a database, a reverse algorithm unscrambles the database and makes it available for use. In order to encrypt a database, you must have exclusive or single-user use of the database file. For exclusive use, check the Exclusive box on the Open dialog box when you open the file. (See "Database: Open" in the Database Essentials part of this book.)
NOTE: When user-level security has been assigned in a database, you must have a Modify Design permission (see Tools, Security, User and Group Permissions) for any and all tables in a database in order to encrypt or decrypt the database successfully.
You can secure a database by creating a password that allows the user full access to the database file. When password access is on, a user supplies a password to open the file. The password is encrypted and secure.
CAUTION: If you set a password for opening a database, you must remember the password. If you forget that password, you will lose access to your file.
CAUTION: If you set a password for opening a database, you will not be able to synchronize databases using replication. (See "Replication: Create Replica.")
You can also create user-level security that limits the particular objects in a database that a user or group of users can read or write to. (See also "Security: Limit User Input.")
Access provides you with a number of different options for securing a database. To limit user input, you must create and define user-level security in your database. This provides password access based on a username through a challenge/response mechanism.
You can use the User-Level Security Wizard to define which features and database objects can be used by which users. For information, choose the Help button of the User-Level Security dialog box. After that point, only users with an Administrative permission level can perform the most sensitive functions such as database replication, password creation, and so on.
Information about user group permissions, accounts, and access privileges are stored in the workgroup information file (see "Security: Create Secure Workgroup"), and are opened when you log on. You must have Administration privileges to accomplish this procedure.
To help you set user-level security for your database, Access offers you the User-Level Security Wizard. This wizard will help you start defining accounts and privileges. You must first join a secure workgroup or create a new workgroup information file before you complete this step. (See "Security: Create Secure Workgroup.")
When you finish running the User-Level Security Wizard, you will want to add new users and groups and then modify permissions for each of the objects in the database. (See "Security: Limiting User Input.")
©Copyright, Macmillan Computer Publishing. All rights reserved.