In This Chapter
Using Database Features in Microsoft Access 97
Converting Your Data to Microsoft Access
Switching from dBASE
Switching from FoxPro
Switching from Paradox
Running Multiple Data Access Applications
Using Built­in Drivers and ODBC Drivers

This chapter tells you what to expect when you or your workgroup switches to Microsoft Access 97 from another database management system.

See Also

• For a summary of new and improved features in Access 97, see Chapter 2, "What's New in Microsoft Office."
• For information about installing Access or other Microsoft Office applications, see Chapter 4, "Installing Microsoft Office."
• For information about upgrading to Access 97 from a previous version of Access, see Chapter 11, "Upgrading from Previous Versions of Microsoft Access."

Most users in your organization are probably already experienced with database management systems. They understand the basic concepts, the steps used to create tables, and how to create databases for different purposes. For users who are converting their files to Access, this section introduces Access and some of its basic features — such as single­file architecture, database objects, and documentation.

Single File Architecture

Most database management systems, including dBASE and Paradox, store individual database components, such as database files (tables), reports, and forms, in separate MS­DOS­based files. A complete application may consist of hundreds of individual files.

An Access database, by contrast, is a collection of objects (tables, queries, forms, reports, macros, and modules) stored in a single file with the .mdb file name extension. When you open an Access database, you also open all the tools that help you use information stored in the database.

Note   Although an Access database can contain all your objects, you can also work with objects that are stored in separate databases. For example, you can store tables in one database and forms and reports in another database. For more information, see "Splitting a Database into a Front-end/Back-end Application" in Chapter 29, "Workgroup Features in Microsoft Access."

Like other common database management systems, such as dBASE and Paradox, Access is a relational database management system. To efficiently and accurately provide you with information, a relational database needs facts about different subjects stored in separate tables.

For example, you may have one table that stores only facts about employees, and another that stores only facts about orders. When you define relationships between these tables, Access uses the relationships to find associated information stored in your database. Using this example, you might define a relationship between the Employees and Orders tables, so that you can associate an employee with each order. For more information about relationships, see "Defining Relationships in Microsoft Access" later in this chapter.

Database Objects

An Access database is made up of different types of objects: tables, queries, forms, reports, macros, and modules.

Tables

Tables are the basis of any database application. A table is a collection of related data, or records. Each table column represents a field, and each row represents a record. Access tables are not stored in separate files but are all included in the Access MDB file. You need at least one table in a database. You define relationships between tables in the Relationships window — there is no need for separate index files, such as the NDX files in dBASE.

Queries

A query is a question about the data stored in tables. You use queries to select or to perform an action on a subset of data. For example, you could design a query that returns only customers from New York. A query can bring together data from multiple tables — it returns a set of records that is actually a view into the underlying tables. These sets of records can be updatable or read­only. If updatable, the resulting data can be added to, changed, or deleted as with any other table.

For example, you can run a query with a linked table from dBASE, several Access tables, and a linked table from Paradox, and then update the results. Access makes your changes in all the underlying tables in the query. For information about linked tables, see "Importing and Linking External Tables" later in this chapter.

You can incorporate queries in your application as a substitute for programming. For example, you can use queries to perform any of the following data update tasks:

• Update a field in a specified subset of records to a new value.

  You can specify the value when you design the query, provide the value by using a field on a form, or use a field value from a table or query. This kind of query is called an update query.

• Delete a specified subset of records.

  You can determine the scope of records to be deleted by specifying criteria when you design the query, by providing the value of a field on a form, or by using a field value from a table or query. This kind of query is called a delete query.

• Add records to a table.

  This kind of query is called an append query.

• Create a new table based on another table or a set of related tables.

  This kind of query is called a make­table query.

Forms

Access forms are generally based on data from tables or queries. By using forms, you can view, enter, change, and print data. In an Access form, you can display data, pictures, graphs, and documents that you are tracking. Using forms to go from one table (for example, Customers) to another (for example, Orders) can be as easy as clicking a button.

You can create forms on your own, or you can have Access do it for you by using one of the Form Wizards. The Form Wizards speed up the process of creating forms by doing all the basic work for you. When you use a Form Wizard, Access prompts you for information and creates a form based on your answers. You can also create a new default form with one click of your mouse by using the AutoForm Wizard. After you have created a form, you can view it in Form view or Datasheet view. Form view displays all the values for one record; Datasheet view displays multiple records in a tabular format.

Reports

You can use reports to display information from tables and queries in a way that may be more meaningful and readable than just looking at a series of records in a table. Making a quick report is easy in Access. You can create most reports by using the Report Wizard. The Report Wizard can automatically create logical groups of data (for example, orders grouped by customer number); totals (for example, the total number of orders for all customers); and subtotals (for example, the total number of orders for each customer). You can add text, data, pictures, lines, boxes, and graphs to your reports.

Macros

You can use macros to automate repetitive tasks and to extend the capabilities of a database. A macro automatically carries out a command or series of commands, called actions, for you. To create a macro, you can select from a list of actions. The macro carries out the actions in the order they are listed, by using the objects or data you have specified for the action arguments. This is an easy way to do point­and­click programming. You can link a macro to a button or to an event such as the closing of a form or report. You may find that you can meet all of your application's programming needs by using macros.

Modules

You use modules for advanced programming tasks, such as implementing error handling or creating your own functions. For example, you may want to create a user­defined function that calculates market projections or that masks error messages. Microsoft Visual Basic for Applications code (procedures, functions, public variables, and so on) resides in modules. Use modules when the actions you want your application to perform cannot be carried out by a simple query or macro.

Database Window

You use the Database window to view all of the database objects in MDB files — it is your control center for working with a database. You can use the tabs on the top of the Database window to work with a specific type of Access object. The following illustration shows the Access Database window.

In Access, you can give tables and other objects longer, more descriptive names than those permitted by some other database management systems. For example, you can name a table Order Details instead of ORDERDET, or you can name a field within a table Quarterly Results instead of abbreviating it as required in dBASE and many MS­DOS­based database programs.

If you plan to use Visual Basic to work with data, however, it is easier to refer to the names of objects in the database if their names are relatively short and have no spaces in them. Longer names require more typing, and names with spaces require you to surround them with brackets — for example, dbs.TableDefs![Quarterly Results]. As an alternative to longer object names, you can enter a description for an object by right­clicking the object in the Database window and then clicking Properties.

By using the View menu, you can display large or small icons beside the object names in the Database window. You can also list objects by name and details. The details are the properties of the object: object description, date modified, date created, and object type. Each detail appears in a separate column that you can resize to hide or show more of the information. You can also sort objects to display them in a different order.

The Database window supports a number of drag­and­drop features. You can:

• Drag a table, query, form, report, or macro to the Windows desktop to create a shortcut for opening that object without starting Access first.
• Drag tables and queries from the Database window to Microsoft Excel or Microsoft Word to copy that data onto a worksheet or into a document.
• Drag Excel worksheet cells to the Database window to create new tables.

You can also copy and paste data between applications by using the Copy and Paste commands (Edit menu).

Creating New Objects

To create a new database object, click the appropriate tab in the Database window (Tables, Queries, Forms, and so on), and then click the New button on the right side of the Database window. For example, to create a new query, click the Queries tab and then click New. Alternatively, you can click the New Object button on the Database toolbar and then select the type of object you want to create.

When creating new tables, queries, forms, and reports, you can use a wizard or you can create a blank object. Wizards are useful for creating anything from simple to complex objects and for learning about Access. As you become more familiar with Access and want to customize tables or other objects, you may want to start from scratch or use a wizard, and then make modifications in Design view.

Working with Existing Objects

To work with an existing object, click the appropriate tab in the Database window, click the object, and then choose how you want to work with that object by using the buttons on the right side of the Database window or a button on the toolbar. The following sections describe the most common actions you can perform on database objects.

Working with Data in Tables, Queries, and Forms

To work with the data in a table, query, or form, double­click the name of the object in the Database window, or select the object in the Database window and click Open. You can then view, enter, or modify the data. A table or query always appears as a datasheet, which displays data in rows and columns much like a spreadsheet. A form opens by default in Form view, but you can switch to Datasheet view if you prefer.

Previewing and Printing Reports

Running Macros

Macros are usually linked to a button, linked to a custom menu option, or triggered by an event in a form or report; however, you can also run a macro directly from the Database window. To do this, double­click the name of the macro in the Database window, or select the macro in the Database window and click Run.

Changing the Design of an Existing Object

To open an object in Design view, select the object name in the Database window and click Design. In Design view, you can make changes to the design of tables, queries, and forms. For example, you can change colors and add sound clips, command buttons, subforms, drop­down list boxes, and so on. You can change the design of reports by adding pictures, changing fonts, sizing column widths, and so on. You can also change macros or modules.

Switching Between Views of Objects

To switch between views of objects, click the arrow to the right of the View button on the toolbar, and then click the view you want. The following illustration shows the View button and the available views.

Using the Object­Specific Menus and Toolbars

The commands available on the menu bar and toolbar vary depending on the database object you are working with and the view in which it is displayed. In Access, you can have many objects open at once. The menu bar and toolbar commands that are available correspond to the object that is currently selected. A different set of commands is available when the Database window is selected.

More About Microsoft Access

You can learn more about how to use Access by working with the sample applications and by reading the documentation included with Access.

Microsoft Access Sample Applications

One of the easiest ways to produce a new database object is to copy one that resembles what you want. The sample databases included with Access — which you can find in the Samples folder in the Program Files\Microsoft Office\Office folder — provide examples of typical business databases.

As you browse through the objects in these applications, you can copy any of them by selecting the name in the Database window and clicking Copy (Edit menu). Then close the sample database, open your own application, click the appropriate tab in the Database window, and click Paste (Edit menu). If you copy a table, you can modify the field definitions, remove its data, and type or paste your own data into it. If you copy a form or report, you can change the RecordSource property to point to the table or query you want to use and change the field references.

Microsoft Access Documentation

The documentation provided with Access is extensive — you can get assistance while you work from any of the following sources:

• Office Assistant

  Click the Microsoft Access Help command (Help menu) to display the Office Assistant. Type a search request, and the Assistant returns a list of the Help topics that are most likely to answer your question.

• Contents and Index

  Click the Contents and Index command (Help menu), and then search for a topic by browsing the table of contents on the Contents tab or by entering keywords on the Index tab. You can also click the Find tab to use a full­text search and look for specific words or phrases.

• Context­Sensitive Help

  Click a text box on a property sheet, a Visual Basic keyword in the Module window, or a keyword in the Object Browser, and then press F1 to display context­sensitive Help.

• ScreenTips

  Click the What's This? command (Help menu), and then click a menu command, toolbar button, screen region, or dialog box option to display information about that screen element. In addition, you can position your mouse pointer over a toolbar button without clicking it to display a ToolTip that tells you what the button does.

• Direct access to the World Wide Web

  Click the Microsoft on the Web command (Help menu) to connect to Access Web sites directly from Access. The Access Web sites are a valuable source of information — among other things, they contain technical articles, answers to frequently asked questions, additional sample applications, troubleshooting tips, and free add­ins and tools.

• Building Applications with Microsoft Access 97

  Building Applications with Microsoft Access 97 is the developer's guide to creating, managing, and distributing database solutions. A printed version of the book is included with Access 97 and Microsoft Office 97 Developer Edition.

  Tip   An online version of Building Applications with Microsoft Access 97 is available in the Office 97 Value Pack. For more information about the Office 97 Value Pack and how to use its contents, see the online Help file Valupack.hlp in the ValuPack folder on the Office CD. If you have Web access, you can also point to Microsoft on the Web (Help menu) and click Free Stuff.

When you convert to Access from another database management system, the first step is to bring external data into Access by either importing or linking it. Once you have imported or linked the tables that contain the data, you can specify a primary key for each of the tables, create any indexes you want, and define the relationships between the tables. Then you can proceed with creating the other database objects that make up your Access application.

The transition to Access cannot always be accomplished in one step. Your organization may be so large that you need to convert in phases. This chapter, therefore, describes both the complete transition of organizations to Access, and the sharing of database resources between those who have converted to Access and those still using another database management system.

This chapter is a starting point in the transition process and offers guidance and information specific to the transition. Many references to other sources are included that can provide further assistance. Foremost among these is the documentation that accompanies Access.

Using External Data in Microsoft Access

When you convert to Access, you typically want to use existing data from external data sources such as database files, spreadsheets, text files, and Hypertext Markup Language (HTML) files. To use external data in your Access application, you can either import or link the data. To successfully import and link data in spreadsheets, text files, or HTML files, the data must be in tabular format.

Access can import or link data from the following formats and applications:

• dBASE III, dBASE III+, dBASE IV, and dBASE version 5.0
• Microsoft FoxPro versions 2.x and 3.0 (import only for 3.0)
• Paradox versions 3.x, 4.x, and 5.0
• Applications that supply 32­bit Open Database Connectivity (ODBC) drivers

  For example, SQL databases such as Microsoft SQL Server, Sybase SQL Server, and Oracle Server. (The ODBC driver for Microsoft SQL is available when you install Access.)

• Databases that use the Microsoft Jet database engine, such as those created with Visual Basic, Microsoft Visual C++, or Excel

  This includes databases created in previous versions of Access.

• Delimited and fixed­width text files
• HTML version 1.0 lists; HTML versions 2.0 and 3.x lists and tables
• Lotus 1­2­3 WKS, WK1, WK3, and WK4 formats
• Excel versions 3.0, 4.0, 5.0, 95, and 97
• Microsoft Exchange and Microsoft Outlook data (collectively referred to as Microsoft Windows Messaging Service data). Linked data in this format is read­only.

For more information about importing and linking Microsoft Windows Messaging Service data, see "Using External Data with Microsoft Access" in Chapter 27, "Sharing Information with Microsoft Office Applications."

http://www.microsoft.com/AccessDev/FreeStuff/ExchWiz.htm

To gain access to data from external data sources, the Microsoft Jet database engine, which is used by Access, uses one of several installable ISAM or ODBC drivers. Before you can gain access to external data, make sure that you have installed the appropriate drivers. For information about how to install drivers, see "Using Built-in Drivers and ODBC Drivers" later in this chapter.

Additionally, for each external data source that you want to connect to using ODBC, you must set up an ODBC data source by using the ODBC Data Source Administrator. To start the ODBC Data Source Administrator, double­click the 32­bit ODBC icon in Control Panel.

Importing and Linking External Tables

If you have data in separate data sources, the first step in converting to Access is to make the data available to your Access application. You can do this by importing or linking tables. If you want to leave the data in its current format, you can link the tables to an Access database. This way, other users can still use the tables in the original application. Conversely, if you plan to use the data only in Access, you can import the tables.

If you leave the data in another database or data format and create linked tables, you can use this data just as you use tables stored in an Access database. For example, you can edit the data in linked tables, and you can create queries, forms, and reports that use the data. You can even combine data in the linked tables with the data in your Access tables. For example, you can design a query based on both Access tables and linked tables. Even though some of the data resides on a different computer, Access combines the data to answer the query.

After you link a table from another database, the table icon Access displays in the Database window indicates that the data in the table is outside of your database — there is a different icon for tables from each type of data source. Access stores all the information you supply when linking the table so it can find the external data whenever you need it. When you open a linked table, Access opens the appropriate database file or connection and displays the data. If you delete the icon in the Database window, you delete the link to the table, not the external table itself.

If you plan to use your data only in Access, you should import the table. Access generally works faster with its own tables, and you can customize Access tables to meet your needs.

Whether you plan to import or link tables, start by clicking the New Database command (File menu) to create your Access database. After you have created the new database, use the Import or Link Tables command (File menu, Get External Data submenu) to bring your existing data into the new Access database.

The following tips can help you get the most from tables you import or link:

• Use long table names

  Access table names are not limited to MS­DOS file­naming rules. Table names can include spaces as well as uppercase and lowercase letters. For example, after you import or link a table called SLSDAT96, you can use the Rename command (Edit menu) to rename the table Sales Data for 1996.

• Adjust column widths

  The first time you open a table in Datasheet view, all the columns have the same width. You can adjust the width of the columns with the mouse and then save the column­width settings by clicking the Save command (File menu). The next time you open the table, the column layout appears the way you left it.

• Set field properties

  By setting field properties for a table in Design view, you can make data easier to work with. For example, you can specify display formats or validation rules you want Access to use in forms and datasheets.

• Change the table structure and set table properties

  When you import your data, you can enhance your tables by opening each one in Design view and changing the structure or properties of the table. It is a good idea to make these changes right away, before you create queries, forms, and reports based on the tables.

• Optimize your application

  For information about optimizing Access databases, see "Optimizing Microsoft Access" in Chapter 7, "Customizing and Optimizing Microsoft Office."

In Access, you can take advantage of the following features with tables you import, but not with tables you link:

• Longer field names

  In Access, field names can be up to 64 characters long and can contain spaces. This gives you much more flexibility than dBASE, in which field names are limited to 10 characters. For example, after you import a table from dBASE, you can change the CUSTADDR field name to Customer Address.

• Primary keys

  You can set a primary key that uniquely identifies each record in the table. If you do not have an appropriate primary key in your table, you can add a field with the AutoNumber data type and the NewValues property set to Increment (this is the default), which automatically assigns a unique sequential number for each record you add.

• Indexes

  To speed up operations in fields that you anticipate searching or sorting often, you can create indexes. Set the Indexed property of the field to Yes.

• Relationships

  If you import related tables, you can define relationships between them that Access uses to relate data in queries, forms, and reports. If you want, Access can automatically enforce referential integrity when adding or deleting records in related tables. Referential integrity ensures that relationships between records are valid, and that you do not accidentally delete related data.

• More data types

  Access provides data types that are not available in some applications but that may be appropriate for your data. For example, the Currency data type is ideal for storing monetary values. After you import a table, you may want to change the data type of such fields from Number to Currency. The AutoNumber data type is a good choice for creating primary keys, indexing, and creating unique relationships if the table does not already contain a unique value for each record.

Deciding Whether to Import or Link an External Table

Whether you import or link a table depends on whether you will continue to use the data with the program that originally produced it. For example, suppose you have a dBASE file that contains a list of products your company sells. You no longer use dBASE, and you would like to have this data handy on your computer. In this case, it makes sense to import the dBASE file into an Access table. To improve the appearance and performance of the table, you can set a primary key and perhaps change field names or set other field properties.

In comparison, suppose your company keeps its sales figures in a Microsoft SQL Server database on a network. Because you may use software other than Access to view or edit this data, you do not want to move it to an Access database. In this case, it makes sense to link the Sales table and use Access as a front end to your SQL Server database. After you link the table, you can update the data from Access and print weekly sales reports.

If your organization and applications are gradually switching to Access, first link tables in their current data format, so you can maintain one master set of data during the transition, and then import the data after all users and all applications have switched to Access.

Even if all users are using Access, you may want to maintain your data in a separate database as linked tables and keep your forms, reports, and other objects in their own database. For more information, see "Splitting a Database into a Front-end/Back-end Application" in Chapter 29, "Workgroup Features in Microsoft Access."

Tips for Importing and Linking Data

When you import or link a table from another database application, keep in mind the following:

• If you import or link a table from a Paradox or an SQL database, you may need to supply a password.

  This password is different from an Access user password; it is the password set in the other application.

• If you link a table that requires a password, Access stores this password in your database so you can open the table later (this is optional for SQL tables).

  For this reason, you may want to add some form of security to your database to help control who can open it. The simplest form of security is requiring a password to open a database, which you can set by using the Set Database Password command (Tools menu, Security submenu). For more information about database passwords, see "Helping Protect File Open with a Database Password" in Chapter 29, "Workgroup Features in Microsoft Access."

• To link an external table on a network, you must connect to the network and you must have access to the database file.

  If you want Access to automatically connect to the appropriate file server each time you open a linked table, specify the fully qualified network path for the file. For example, if you use a Microsoft Windows NT Server network, you can enter the path to connect to a dBASE file by using the following format: \\server\share\datadir\myfile.dbf

  This format is called the universal naming convention (UNC) path name and is supported by many networks. When using this format, you do not need to map a drive letter to a file; this prevents problems if the drive letter is changed or the database is moved to a different computer with differently mapped drives.

• After you import or link an external table, it is a good idea to set table properties in table Design view.

  If you import a table, it is especially important to set a primary key.

Developing Your Application

After you import or link your data, you can build the rest of your Access application. First, you need to refine the structure of your tables by specifying a primary key for each table, creating indexes, defining relationships between your tables, entering validation rules, and establishing referential integrity. Then you can create queries, forms, and reports that you use to display and work with your data. To automate tasks and perform complex operations, write macros and Visual Basic code.

Defining Relationships in Microsoft Access

You create and edit relationships between tables in Access by using the Relationships window. To display the Relationships window, click the Relationships command (Tools menu). When you define a relationship in Access, you choose the fields you want to use to define the relationship between two tables by dragging a field from one table in the Relationships window to the appropriate field in the other table. Access displays the kind of relationship it will create. If only one of the related fields is a primary key or unique index, Access creates a one­to­many relationship; if both related fields are primary keys or unique indexes, Access creates a one­to­one relationship.

When you define a relationship, you can specify whether to enforce referential integrity and whether to allow cascading updates and cascading deletes. Enforcing referential integrity prevents orphan records (a master record is deleted when related detail records still exist in other tables) in your database. For example, if you select the Enforce Referential Integrity check box in the Relationships dialog box, no one can enter an order for a customer that is not in the Customers table or delete a customer if orders exist for that customer. If you select the Cascade Update Related Fields or the Cascade Delete Related Records check box, records that have been edited or deleted in the primary table are automatically updated in related tables. You can control how these relationships are maintained through the Relationships window. The following illustration shows the Relationships window.

To specify the fields used to create the relationship and the degree of integrity it maintains, double­click the relationship line between the tables to open the Relationships dialog box. Access saves these relationships and automatically shows them in any query that you build. To delete a relationship, click the relationship line to select it, and then press DELETE.

Defining Table­Level Validation and Referential Integrity

Many other database management systems require you to write validation code for each form and for any other place in your application where data is updated. Some database management systems may also require you to write additional code to maintain referential integrity.

In Access, you can define validation rules when you create tables, and referential integrity when you define relationships. These rules then apply to all of your forms and queries. In many cases, table­level validation and referential integrity meet all of your application's validation requirements. If your application requires more sophisticated validation rules, you can use form­level validation and write user­defined functions or procedures in Visual Basic code.

Creating Queries, Forms, and Reports

You cannot import or convert queries, forms, and reports from your old database management system — you must create new ones in Access. Creating database objects in Access is easy; you can use wizards to create basic database objects and then customize the objects to meet your needs. For more information, see "Creating New Objects" earlier in this chapter.

Programming in Microsoft Access

In many cases, tasks that require programming in most database management systems do not require programming in Access. If you need to program in Access (for example, to implement error handling or to create your own functions), you use Visual Basic for Applications code.

Visual Basic for Applications is similar to the Microsoft Visual Basic programming language. You write Visual Basic code in blocks called either procedures or functions. Procedures and functions contain a series of Visual Basic for Applications statements that perform an operation. You store your Visual Basic code in modules. Access has standard modules, which contain procedures that can be called by procedures in other modules in the same database or in referenced databases. Access also has form and report modules, which contain code that is associated with the form or report. For more information about using Visual Basic with Access, see Building Applications with Microsoft Access 97.

Setting Keyboard Options

Once you have switched to Access, you may notice that the navigation keys work differently in Access than in some other database applications. For example, when you are viewing and editing data in a form or datasheet, the arrow keys may behave differently than they did in your former database management system.

For example, in dBASE, pressing the RIGHT ARROW or LEFT ARROW key moves the insertion point one character to the right or left within a field. However, in Access, the contents of a field are automatically selected by default, and pressing these keys moves to the next or previous field. If you click in the field or press F2 to activate the insertion point, pressing the RIGHT ARROW or LEFT ARROW key moves the insertion point one character to the right or left within a field.

If you want the navigation keys to work as they do in the application you are used to, you can set keyboard options on the Keyboard tab of the Options dialog box (Tools menu). The following table describes the default keyboard options in Access that you may want to change.