Web based School

Chapter Three
File Management

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.

Back Up Data

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.")

Steps

1. From the Database window, click the Open button on the toolbar.

2. Navigate to your file's location by using the Look In drop-down text box and file list.

3. Right-click the file name and choose Copy.

4. Right-click the file list box in the white area (in other words, do not click a file or folder) and choose Paste.

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.

Database Object: Copy

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 guide.)

Steps

1. Close the object you want to copy and any related form, query, or report based on that object.

2. Press F11 to go to the Database window.

3. Select the object name from the Database window and click the Copy button on the toolbar.

4. Click the Paste button on the toolbar. In the Paste As dialog box, give the object a name.

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").

Database Object: Delete

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.

Steps

1. Press F11 to go to the Database window.

2. Select the object name from the Database window and press the Delete key on the keyboard.

3. At the warning prompt, confirm that you want to delete the object.

Database Object: Description

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.

Steps

1. Press F11 to go to the Database window.

2. Right-click the object name from the Database window and choose Properties.

3. Enter text in the Description box; choose OK.

Database Object: Properties

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.")

Steps

1. Press F11 to go to the Database window.

2. Right-click the object name from the Database window and choose Properties.

3. Check or uncheck the Hidden or Replicable check boxes; choose OK.

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.

Database Object: Rename

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 guide).

Steps

1. Press F11 to go to the Database window.

2. Right-click the object name from the Database window and choose Rename.

3. Enter the new name in the box surrounding the name.

Database Properties

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 guide), 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.

Steps

1. Choose File, Database Properties.

2. Click the Summary or Custom tabs and enter any information you want.

3. Click the General, Statistics, or Contents tabs to see more information about your database; choose OK when finished.

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.

Database: Compact

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.

Steps

1. Close your current database and have any connected users close their session to the database you intend to compact.

2. Choose Tools, Database Utilities, Compact Database.

3. Select the name of the database you want to compact in the Database To Compact From dialog box; then choose Compact.

4. Enter the name, drive, and folder for the compacted database in the Compact Database Into dialog box; then choose Save.

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.

Database: Convert

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.

Steps

1. Close any open database.

2. Click the Open Database button on the toolbar. Choose the location and name of the older version file and choose Open.

3. In the Convert/Open Database dialog box, choose Convert Database.

4. In the Convert Database Into dialog box, type the name of the file in the File Name text box; choose Save.

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.

Database: Default Folder Set

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.

Steps

1. Choose Tools, Options, and click the General tab.

2. In the Default Database Folder text box, type the name of the folder where you store your databases; choose OK.

Database: Repair Closed 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.

Steps

1. Close your current database.

2. Choose Tools, Database Utilities, Repair Database.

3. Select the database in the Repair Database dialog box.

4. Choose the Repair button.

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.

Database: Repair Open Database

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.

Steps

1. Click the Open button and check the Exclusive box, select the database file name and choose Open.

2. Select Tools, Database Utilities, Repair Database.

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.

Excel: Convert Access Object to Excel Worksheet

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.

Steps

1. Select the object (to be converted) in the Database window.

2. On the Office Links button, choose Analyze It with MS Excel.

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.

Export and Import: Installing Additional Drivers Through Setup

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.")

Steps

1. First, close any open applications and insert the Office or Access CD-ROM.

2. On the Windows Desktop, double-click My Computer and double-click the CD-ROM drive. Double-click the Setup icon.

3. On the setup dialog box, choose Add/Remove, select the Data Access choice in the Options list, and click the Change option button. Choose Database Drivers in the Options list and click the Change Option button.

4. Pick from the list of Options and then choose the OK, Continue, and Yes buttons until you finish the setup.

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.

Steps

1. First, close any open applications and insert the Office or Access CD-ROM.

2. On the Windows Desktop, double-click My Computer and double-click the CD-ROM drive. Double-click the ValuPack folder and then the Dataacc folder.

3. Inside the Dataacc folder is the Dataacc program. Double-click the program icon and choose Yes to install the Microsoft Data Access Pack.

4. After the Data Access Pack is installed, click the Add/Remove button, choose Data Access Drivers and the Chang e option button.

5. Pick from the list of Options and then choose the OK, Continue, and Yes buttons until you finish the setup.

Export Data: Access to Access

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.")

Steps

1. Click the object you want to export in any tab of the Database window.

2. Choose File, Save As/Export.

3. Click the To an External File or Database option button; then choose OK.

4. Select the Access database you want to export to in the file dialog box; then choose Export.

5. If you chose a table in Step 1, indicate in the Export dialog box whether you want to export just the table Definition Only, or the Definition and Data by selecting the appropriate option button; then choose OK.

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.

Export Data: Access to Another File Type

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.")

Steps

1. Click the table or query you want to export in the Database window.

2. Choose File, Save As/Export.

3. Choose the To an External File or Database option button; choose OK.

4. Select the location of the file in the Save In list box. In the Save As Type list box, select the format you want to save the table or query in.

5. Enter a name in the File Name text box, then choose Export.

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.")

Export Data: Access Object to HTML

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.

Steps

1. Choose File, Save As HTML. The Publish to the Web Wizard opens; choose the Next button.

2. Choose which objects (on the Tables, Queries, Forms, Reports, or All Objects tabs); choose the Next button.

3. If you have a default template that contains background patterns or other styles for the Web pages, enter it on the third step of the Publish to the Web Wizard; choose the Next button.

4. Choose whether you want to create a Static or Dynamic HTML file; choose the Next button. If you created a dynamic Web page, Access creates the HTML file and links it to the data source you specified. If you created a static Web page, you can copy to file to your intranet/Internet site.

5. Specify the computer or data source used by the Web server, and a username or password if that data source requires it. If you created an ASP file, enter the server URL of the location of the ASP file.

If you chose Static HTML, identify where you want to store the file.

6. On the last two pages of the Wizard, indicate if you want to create a home page for the objects and if you want to save the settings.

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.

Export Data: Text Files

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.

Steps

1. Choose Delimited on the Export Text Wizard.

2. Choose which delimiter (Tab, Semicolon, Space, Other) you want between fields, check if you want to Include Field Names on First Row, and define the Text _Qualifier (the default is quotes).

3. The last step allows you to change the file name and path; choose Finish.

Export Data: Word

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 guide.)

Steps

1. Select the object in the Database window.

2. On the Office Links button on the toolbar, choose Publish It with MS Word.

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.

Import Data: 1st Steps

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 guide.)

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.")

Steps

1. Choose File, Get External Data, Import.

2. In the Import dialog box, choose folder from Look In drop-down box, and the data type from the Files of Type drop down.

3. Select file name from list and choose the Import command button.

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.")

Import Data: Access

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.

Steps

1. The Import Objects dialog box has tabs for tables, queries, forms, reports, macros, and modules. Click the objects you wish to import from each tab.

2. If desired, choose the Options button and choose whether to import relationships, design features (Definition), the data in tables, and whether to import queries as queries or as tables; choose OK.

Access adds each of the objects into their appropriate places in the Database window.

Import Data: Checking Data Integrity

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.

Steps

1. Check that field names conform to Access naming rules. For example, Informix allows periods in field names. You will have to remove the periods before importing to Access. Field names also cannot have an exclamation point (!), accent grave (`), or square brackets ([]).

2. Make all data in a column the same data type. If you have comments in a number field (such as unknown), you need to take them out. Alternatively, import the field as text. If you need to make calculations on the mixed data type field, separate it into two fields. An update query might help.

3. If the import does not work, you will get an error message and an additional table, <file name>Import Errors. You can use this table to troubleshoot what you need to do to fix your original data source.

4. After you import and change data properties, you might receive error messages as well. You might need to use some select queries, or check for duplicates or unmatched records.

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 guide.

Import Data: HTML

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.

Steps

1. To import data, choose File, Get External Data, Import.

2. In the Import dialog box, select HTML Documents (*html;*.asp) from the Files of Type list box.

3. Double-click the file of interest using the Look In list box. The Import HTML Wizard runs.

4. On the first steps of the wizard, choose whether the first row has headings, whether you want to import into a new table or existing table, what the field names and data types should be, and whether you want Access to add a primary key. Choose Next after you make the choices on each screen.

5. On the last step of the wizard, type a name for the table and choose Finish.

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.

Import Data: Spreadsheet

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: 1^st 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.

Steps

1. After choosing the spreadsheet file, Access brings up the Import Spreadsheet Wizard. Choose to list worksheets or ranges and then select the appropriate part of the file; choose Next.

2. Identify whether the first row of the data source contains field names; choose Next.

3. Choose to store data in a New Table or choose drop-down arrow for In an Existing Table; choose Next.

4. Click in each field, type the Field Name, the Data Type, whether the field should be Indexed, or Skip the field; choose Next.

5. Decide whether you want Access to add a primary key or if you want to choose your own; choose Next.

6. On the final step of the Import Spreadsheet Wizard, give the table a name and choose Finish.

Import Data: Text

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: 1^st Steps") to choose a Text file.

Steps

1. After choosing the text file, Access brings up the Import Text Wizard. Choose whether your file is delimited or fixed width; choose Next.

2. Show where columns break, whether first row contains field names, choose characters that delimit fields, and identify text; choose Next.

3. Choose to store data in a New Table or choose drop-down arrow for In an Existing Table; choose Next.

4. Click in each field, type the Field Name, the Date Type, whether the field should be Indexed, or Skip the field; choose Next.

5. Decide whether you want Access to add a primary key or if you want to choose your own; choose Next.

6. On the final step of the Import Text Wizard, give the table a name and choose Finish.

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.

Import Data: Word

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 guide.)

Steps

1. In Access, design the table structure first by creating fields in the same order as the columns in the Word document. Be sure the data types match the data in the Word columns.

2. Launch Word and open the file with the table or tabbed information. Select the data. Do not include any column headers in the selection.

3. Choose the Copy button on the toolbar.

4. Return to Access, go to the Database window and double-click the table you designed in Step 1.

5. Choose Edit, Paste Append to bring in the data from Word.

Links to External Data: 1st Steps

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.

Steps

1. Choose File, Get External Data, Link Table.

2. In the Link dialog box, select the appropriate file format in the Files of Type drop-down list; then locate the file of interest using the Look In list box.

3. Select the table or spreadsheet, then choose the Link button.

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.

Links to External Data: dBASE and FoxPro

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: 1^st Steps." After these initial procedures, complete the following steps.

Steps

1. After you select the dBase or FoxPro file, Access opens the Select Index Files dialog box. Choose the FoxPro index (.CDX or .IDX) file or dBASE (.MDX or .NDX) file. If no index exists, choose the Cancel button.

2. Enter the index that identifies each record in the Select Unique Record Identifier dialog box; choose OK.

When you link to a dBASE table, Access' Database window shows the dB symbol, FoxPro shows a fox icon.

Links to External Data: Excel

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: 1^st Steps." After these initial procedures, complete the following steps.

Steps

1. After you select the Excel file, Access opens the Link Spreadsheet Wizard. Choose the worksheet or range that you want to link; choose Next.

2. Identify if the first row contains names for the fields; choose Next.

3. On the final step of the wizard, give the table a name and choose Finish.

The Tables tab of the Database window shows a linked Excel spreadsheet indicated by an Excel X icon.

Links to External Data: HTML File

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.

Steps

1. To link the data, choose File, Get External Data, Link.

2. In the Link dialog box, select HTML Documents (*html;*.asp) from the Files of Type list box.

3. Double-click the file of interest using the Look In list box. The Link HTML Wizard runs.

4. Choose whether the first row of the HTML file contains headers; choose Next.

5. If desired, click each column and type new field names in the Field Name text box, change Data Type in with the drop-down list, or choose if you want to Skip the column and not import it.

6. On the last step of the wizard, give the table name and choose Finish; choose Next.

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.

Links to External Data: Text

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: 1^st 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.

Steps

1. After choosing the text file, Access brings up the Link Text Wizard. Choose whether your file is delimited or fixed width; choose Next.

2. Show where columns break, whether first row contains field names, choose characters that delimit fields, and identify text; choose Next.

3. Click in each field, type the Field Name, the Date Type, whether the field should be Indexed, or Skip the field; choose Next.

4. On the final step of the Link Text Wizard, give the table a name and choose Finish.

The Tables tab of the Database window shows a linked text file indicated by a small noteguide 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.

Links to External Data: Removing

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.

Steps

1. Select the linked table in the Database window.

2. Press the Delete key on your keyboard.

3. When prompted, choose Yes to confirm removing the link.

Links to External Data: Updating

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.

Steps

1. Choose Tools, Add-Ins, Linked Table Manager.

2. In the Linked Table Manager dialog box, choose which files you want to update. Click the Select All button to choose all files.

3. For each file you select in Step 2, Access will prompt you for a location. Choose the folder and double-click the file name. When finished, choose OK.

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.

MDE File: Removing Access to Programming

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).

Steps

1. Choose Tools, Database Utilities, Make MDE File.

2. In the Save MDE As File dialog box, choose a location and name for the MDE file; choose Save.

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.

Replication: Create Replica

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.

Steps

1. Choose Tools, Rep lication, Create Replica.

2. Access asks if you want to close the database to create the replica. Choose Yes.

3. Access asks if you want to make a copy of the database. Because something may go wrong during the process, choose Yes.

4. In the Location of New Replica dialog box, choose the folder and file name for your replica; choose OK.

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.

Replication: Recover Design Master

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.")

Steps

1. Open a Replica database.

2. Choose Tools, Replication, Recover Design Master.

Replication: Resolve Conflicts

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.

Steps

1. If necessary, open the Replica database.

2. Choose Tools, Rep lication, Resolve Conflicts.

3. If there are any conflicts, Access opens the Resolve Replication Conflicts dialog box. Each table with a conflict is listed in this box. Click a table and then the Resolve Conflicts button.

4. The dialog box opens with two columns. The first column shows the database you have on-screen. The second shows the conflict with the Replica or Design Master. To keep the data from the open database, choose Keep Existing Record. To use data from the other database, choose Overwrite with Conflict Record.

5. When you have resolved the conflicts, choose Close. You should resolve conflicts in all tables, but you can close this dialog box and repeat this task at a later time.

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.

Replication: Synchronize

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.

Steps

1. Choose Tools, Rep lication, Synchronize Now.

2. The name of the Design Master or Replica should appear in the Synchronize Database dialog box. If you have more than one replica, use the drop-down box to choose another; choose OK.

3. Access asks if you want to close and reopen the database to see the changes; choose Yes.

4.If you have any conflicts between the two databases, Access will let you know.

If you are in the Replica, you can choose to resolve the conflicts now or later. (See also "Replication: Resolving Conflicts.")

Replication: Using Briefcase

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.

Steps

1. Double-click the Briefcase icon on your desktop.

2. Click the replica icon of the database.

3.Choose Briefcase, Update Selection.

4. Choose the Update button to begin synchronization.

Save File

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.

Steps

1. To save any changes, click the save button on the toolbar.

If the save button is dimmed, there is nothing to save.

Security: Create Secure Workgroup

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.

Steps

1. Exit Access. Find and double-click the file WRKGADM.EXE. In Windows 95, it is in the Windows\System folder. In Windows NT, it is in WinNT\System32 folder.

2. In the Workgroup Administrator dialog box, choose Create. Type your name and organization and any combination of up to 20 characters in the Workgroup ID text box; choose OK.

3. In the Workgroup Information File dialog box, change the name of the file. Choose Exit when done.

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.

Security: Database Encryption/Decryption

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 guide.)

Steps

1. Launch Access, but don't open a database in it. Make sure no other users have the database open.

2.Choose Tools, Security, Encrypt/Decrypt Database.

3. Select the database you want to decrypt.

4. Or, select the database you want to encrypt and enter a name and location for that database.

When you supply the same name and location for the database you are encrypting, Access replaces the original database with the encrypted version.

5. Choose OK.

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.

Security: Database Password

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.

Steps

1. Close the database and end any other user sessions in a multiuser Access database.

2. Create a backup copy of the database. Store the file in a secure location.

3. Choose File, Open Database (Ctrl+O), or click the Open button on the toolbar; click the Exclusive check box; then choose the Open button.

4. Choose Tools, Security, Set Database Password.

5. Enter your password in the Password text box, then enter it again in the Verify text box. Choose the OK button.

If the two passwords match, Access enters the password for overall database file access and requires it the next time you open the 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.")

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.

Steps

1. Open the database, then choose Tools, Security, User and Group Permissions.

2. Click users or groups on the User/Group Name list on the Permission tab to select to whom you want to apply an access privilege or restriction.

3. Click the type of object in the Object Type drop-down list box, then click the name of the specific object in the Object Name box.

4. In the Permissions section, click on or off the permissions you desire; then choose Apply. Depending on the object type, the permissions include the following: Open/Run, Read Design, Modify Design, Administer, Read Data, Update Data, Insert Data, Delete Data.

5. When you are done adding or removing permissions, choose OK.

Security: User-Level

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.")

Steps

1. Choose Tools, Security, User and Group Accounts.

2. Select the Admin user account on the Users tab. Then click the Change Logon Password tab.

3. Click the New Password text box, enter a password, then enter that same password in the Verify text box. Choose OK.

4. Select the User-Level Security Wizard from the Security submenu of the Tools menu.
The wizard creates a new database, exports the objects from your current database to it, revokes all permissions for access to the objects, and then encrypts the new database file. The Admin group has access, but the Users group has no access to the database.

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.")