Ch 3 -- File Management
Microsoft® Access 97 Quick Reference
- 3 -
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 book.)
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 book).
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 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.
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 FilesMicrosoft OfficeTemplatesAccess
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 book.)
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 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.")
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 book.
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;*.htm)
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: 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.
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: 1st
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 book.)
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: 1st 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: 1st 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;*.htm)
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: 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.
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 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.
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 WindowsSystem folder. In Windows NT, it is in WinNTSystem32 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 book.)
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.")
|
