Chapter 17 - Switching to MS Access

Article
02/20/2014

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

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

Note Access runs on Windows 95 and Windows NT Workstation version 3.51 or later only.

Using Database Features in MS Access 97

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.

Which Database Object Should I Use For a Task?

Access provides several ways to get information from a database. The type of database object you use depends on the task you want to perform. For example:

To view all of the products that a particular vendor supplies, or that are out of stock or on order, use a query.
To view all of the information about a particular product, customer, or supplier at once, use a form.
To organize and print product sales for a formal presentation, use a report.
To automate tasks, such as using a button to open a form or to carry out a menu command or series of menu commands each time a database opens, use a macro. You can also create buttons with the Command Button Wizard.
To write specialized routines that give your application greater functionality, use a module that contains Visual Basic code.

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.

Cc749832.01701(en-us,TechNet.10).gif

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

To preview a report, double-click the name of the report in the Database window, or select the report in the Database window and click Preview. After the report is displayed, you can print it by clicking the Print button on the toolbar. To print a report without opening it, select the report in the Database window, and then click the Print button on the toolbar.

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.

Cc749832.01702(en-us,TechNet.10).gif

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

Converting Your Data to MS Access

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

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

Cc749832.01703(en-us,TechNet.10).gif

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.

In this option group	Selecting this option	Results in this action in Access
Move After Enter	Don't Move	Pressing ENTER selects the current field but does not move to another field.
	Next Field (default)	Pressing ENTER moves to the next field.
	Next Record	Pressing ENTER moves to the same field in the next record.
Arrow Key Behavior	Next Field (default)	Pressing the RIGHT ARROW or LEFT ARROW key moves the selection to the next or previous field. By default, Access selects the entire field when you use the arrow keys. To prevent this, select another option under Behavior Entering Field.
	Next Character	Pressing the RIGHT ARROW or LEFT ARROW key always moves to the next or previous character, just as in dBASE.
Behavior Entering Field	Select Entire Field (default)	Moving from field to field with the ENTER, TAB, and arrow keys selects the entire contents of a field. To activate the insertion point and move it within a field, click the field or press F2.
	Go To Start Of Field	Moving from field to field with the ENTER, TAB, and arrow keys places the insertion point at the start of the field.
	Go To End Of Field	Moving from field to field with the ENTER, TAB, and arrow keys places the insertion point at the end of the field.
Cursor Stops at First/Last Field	Cleared (default)	Pressing the RIGHT ARROW or LEFT ARROW key moves past the first or last field.
	Selected	Pressing the RIGHT ARROW or LEFT ARROW key moves no farther than the first or last field.

Switching from dBASE

To switch from dBASE to Access, you can either link or import data from dBASE DBF files. You can then modify the structure of the tables and create the other database objects that you need. This section discusses the similarities and differences between dBASE and Access, including terminology and data type conversion.

Note A primary difference between Access and MS-DOS-based applications is the use of the Windows graphical user interface. If you are currently using an MS-DOS-based version of dBASE and are new to Windows, study your Windows documentation to learn basic techniques.

MS Access 97 Terms for dBASE Users

The following table matches Access terms with the equivalent term or command in dBASE. The Access term is not necessarily an exact equivalent of the dBASE term, but rather a term you can look up in Access online Help for information. You do not need to understand all of these terms before you start using Access.

This dBASE term	Corresponds to this Access term
APPEND command	Data Entry command (Records menu)
BROWSE command	Datasheet view
Catalog	Database
Character data type	Text data type
Database file	Table
Index	Index
LOCATE and SEEK commands	Find command (Edit menu)
MODIFY STRUCTURE command	Table Design view
Multiple-file screen	Subform
Pick list	List box, combo box
PICTURE/VALID clause	Validation rule
Program file	Module
Query, view	Query
Screen	Form
SET EXCLUSIVE mode	Exclusive/Shared access
SET FORMAT TO; EDIT	Open a form
Unique index	Primary key

Using dBASE Files in MS Access

You can use data from DBF files in dBASE III, dBASE III+, dBASE IV, or dBASE 5.0 format. If you need to leave your data in dBASE DBF format so that dBASE users can continue to use it, you can link the data to your Access database. By using Access, you can view and edit the data in linked tables even if others are using it in dBASE. You can also create queries, forms, and reports that use the data. You can even combine data from linked tables with the data from any other tables in your Access application.

If you have no need to maintain your dBASE data in its original format, you can import your DBF files into Access. Unlike linking the dBASE file, importing the data creates a copy of the data in Access. Importing a table gives you greater flexibility and control over the data, but excludes users who are not running Access from updating the data.

Importing or Linking dBASE Files

If you link a dBASE file, you can also tell Access to use one or more dBASE index (NDX or MDX) files to improve performance. Access keeps track of the indexes in a special information (INF) file. When you use Access to update the data in your DBF file, Access also updates the index files to reflect your changes.

If you link a DBF file and associate an index (NDX or MDX) file, Access needs the index file to open the linked table. If you delete or move index files or the information (INF) file, you cannot open the linked table. If you use dBASE to update data, you must update the associated indexes within dBASE as well. Access cannot use a linked table unless the indexes you specify are current.

If your dBASE files are stored on a read-only drive or CD-ROM, Access cannot create an INF file in the same folder as your DBF files. To link tables on a read-only drive, you must specify the path to the folder where you want Access to create the INF file. To do so, you need to modify the Windows registry. In the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Xbase key, or in the Access-specific Jet\3.5 key, create the INFPath value and point it to a read/write location in which the INF file can be written.

Note You can also use a user profile to set the INFPath value. For more information, see "Running Multiple Data Access Applications" later in this chapter.

To import or link a dBASE file

Open a database in Access, or switch to the Database window of the open database.
On the File menu, point to Get External Data, and then click Import or Link Tables.
In the Files of Type box, click dBASE III, dBASE IV, or dBASE 5. (The dBASE III option works for both dBASE III and III+ files.)
In the Look in box, switch to the appropriate folder and select the file you want, and then click Import or Link.

If you are importing the file, Access creates a new table named after the file you selected and imports the data from the dBASE file.

If you are linking the file, Access displays a dialog box in which you can associate dBASE index files.
For each dBASE index (NDX or MDX) file you want to link, select the file and click Select. When you finish associating indexes, click Close. If there are no indexes to associate, click Cancel to continue.

Access displays the Select Unique Record Identifier dialog box.
If an index you have chosen uniquely identifies each record in the table so that indexed fields do not contain any duplicate values, select the index and click OK.

Note If you do not select an index that provides a unique identifier, Access may not be able to update data in queries with joins to this table, because records cannot be uniquely identified to maintain referential integrity.

Access adds the table name with a linked table icon to the list on the Tables tab in the Database window. When you choose a linked table from the list, you open the dBASE database file that contains the table, and the data is available as if it were part of your Access database.
Repeat Steps 4 through 6 for each dBASE file you want to import or link, and then click Close.

After you have imported all the tables, specify a primary key (a unique identifier used to relate tables) and create indexes on all fields you want to search or sort. In addition, check to see whether you want to modify any data types, field properties, or table properties at this time. Then define the relationships between your tables.

For information about how to define relationships in Access, see "Defining Relationships in Microsoft Access" earlier in this chapter. For additional tips on using imported or linked tables, see "Importing and Linking External Tables" earlier in this chapter.

Converting dBASE Data Types

When you import data from a dBASE file, Access converts dBASE data types into the corresponding Access data types. The following table lists the data type conversions.

This dBASE data type	Is converted to this Access data type
Character	Text
Date	Date/Time
Logical	Yes/No
Memo	Memo
Numeric, Float	Number (FieldSize property set to Double)

Note the following differences between dBASE data types and Access data types:

Numeric data types

dBASE supports two numeric field types: Numeric and Float. In Access, you use the Number data type for most numeric fields and then set the FieldSize property to specify the range and kind of numeric values that can be entered into a Number field.
Additional field data types

Access offers two field data types not provided in dBASE: AutoNumber and Currency. The most common form of AutoNumber field automatically increments a value by one for each succeeding record to generate a unique identifier for each record. This data type is used for primary keys and for creating ID numbers. The Currency data type is tailored to the kind of rounding and calculation typical of currency transactions.
Fixed-length fields

As in dBASE, you must specify the length of Text fields. If a record exceeds the specified length, data is truncated. Therefore, set the length of your Text fields to the length of the longest anticipated entry. However, unlike dBASE, Access does not actually store all of the unused space characters for fixed-length fields, so that the size of your files is not a consideration.

Creating Queries

After you import or link the data, you can create queries in Access — you cannot import your dBASE queries. Access has query wizards to help you create many complex queries. Use the query wizards to learn how to build queries in Access. Then, as you build more advanced queries, use the wizards as a starting point for your own query designs.

To add a table or query to the query you are creating, you can drag a table from the Database window to the upper portion of the query design grid, or you can click the Show Table button on the Query Design toolbar. If relationships have been defined between tables, they are reflected in the query automatically. You can double-click the relationship line between two tables to change the relationship. The following illustration shows the query design grid.

Cc749832.01704(en-us,TechNet.10).gif

To add fields to the query, drag them from the table field list to the query design grid, or select them from the drop-down list in the Field row of the design grid. To indicate a sort order for fields, click Ascending or Descending in the drop-down list in the Sort row of the design grid. Enter criteria formulas in the Criteria row. Many of the functions and formulas are similar to their dBASE equivalents.

Creating Forms

Access forms are equivalent to dBASE screens. The dBASE Form Designer tools limit you to using only fields and text. As a result of this limitation, the traditional dBASE approach to adding features to forms is to set in a program function keys that call procedures related to the form. In dBASE, to make the user aware of these functions, you add text to the form that indicates which function keys to press to invoke certain procedures. You can also write code to create a more user-friendly dBASE form.

In Access, you do not need to write code to create user-friendly forms. You can use the Form Wizard to create attractive forms from your tables or queries. On the Forms tab in the Database window, click the New button, and then double-click Form Wizard. Because Access forms take advantage of the Windows graphical environment, it is easy to create controls such as command buttons, check boxes, and drop-down lists. The Office Assistant and the Form Wizard can guide you through adding features to your forms without programming. Access forms also use visual controls to work with built-in features, such as the record navigation buttons at the bottom of each form.

There are many things you can do to update the way your current dBASE form works when you convert to Access. Here are some tips for updating your forms to take advantage of Access features:

If you are using multiple-choice fields, such as Picture @M, on a dBASE form, consider substituting option buttons, toggle buttons, or a combo box on the Access version of the form.
If you have one or more logical fields on a dBASE form, consider replacing them with check box controls.
If your dBASE form is programmed to display detail records in a BROWSE window or screen, consider replacing the separate BROWSE interface with an integrated subform on the Access form, as shown in the following illustration.

If your browser does not support inline frames, click here to view on a separate page.
Most Access form controls have a SpecialEffect property that you can set to make controls on your forms look three-dimensional.
You can have more than one Access form open at a time.

For example, you may want to link your Inventory form to your Manufacturing Schedule form and display them simultaneously. If having more than one form open at once is confusing or causes a problem, you can set the Modal property of the form to Yes to force the user to close the current form before opening another.
The Access equivalent to the dBASE Picture Functions field and the Template field is either the Format property or the InputMask property.

There is also an Access field property equivalent for most of the dBASE field Edit Options. When you click a property on the property sheet, Access displays a brief description in the status bar at the bottom of the screen.

Creating Reports

Access uses a banded report designer similar to the one in dBASE, but with extensive graphical tools and data windows. The information in a report is divided into sections, or bands. Each section has a specific purpose and prints in a particular order. As with queries and forms, you can use a Report Wizard as a quick way to get started creating a report. If you use a Report Wizard, Access creates a complete report with an attractive and consistent look that you can apply to all of your reports. For most reports, the Report Wizards are all you need. If you want to customize a report, you can do so in report Design view.

Tip Like dBASE, Access supports calculated fields in reports. In addition, Access provides a special RunningSum property, which does not exist in dBASE.

Converting Catalogs

The Database window displays all of the objects in your database, much as the Control Center does in dBASE IV. In dBASE, a catalog gives you access to all the files that make up the database. However, in Access, all of the objects are contained in a single file with an MDB extension, with the exception of linked objects. For linked objects, the links are stored in the MDB file, but the objects themselves are stored in other files.

Exporting a Table to a dBASE File

If you need to save the data from a table or query that has been created in Access so that users who are still working in dBASE can use it, you can export that data.

To export an Access table or query to a dBASE file

Open an Access database, or switch to the Database window of the open database.
In the Database window, click the table or query you want to export.
On the File menu, click Save As/Export.
In the Save As dialog box, click To an External File or Database, and then click OK.
In the Save as type box, click the dBASE format you want.
In the File name box, type a file name for the database file you want to export.
In the Save in box, select the folder where you want to save the file, and then click Export.

If you export a table with table or field names that do not fit the requirements of the destination database, Access adjusts the names. For example, if you export data to a dBASE table, any field names longer than 10 characters are truncated.

MS Access Registry Settings for dBASE

Access uses the Microsoft Jet database engine to read and write data, whether it is using its own native tables or linking tables in other data formats. The settings Microsoft Jet uses to initialize the Xbase driver (the driver for both dBASE and FoxPro files) are located in the Windows registry in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Xbase key.

The Setup program automatically creates recommended registry settings. However, you can modify these settings in this key, in the Access-specific Jet 3.5 registry key, or by using a user profile. For more information, see "Running Multiple Data Access Applications" later in this chapter. For more information about how to modify Access registry settings, see Access online Help.

Switching from FoxPro

To switch from FoxPro to Access, you can either link or import the data from your FoxPro DBF files. You can then modify the structure of the tables and create the other database objects you need. This section discusses the similarities and differences between FoxPro and Access, including data type conversion.

Using FoxPro Files in MS Access

You can use data from DBF files in FoxPro 2.x format and from DBC files in FoxPro 3.0 format. If you need to leave your data in FoxPro 2.0, 2.5, 2.6 DBF format so that FoxPro users can continue to use it, you can link the data to your Access database. By using Access, you can view and edit the data in linked tables even if others are using it in FoxPro.

If you have no need to maintain your FoxPro 2.x DBF data in its original format or if you are using data from DBC files in FoxPro 3.0 format, you can import these files into Access. You cannot link DBC files in FoxPro 3.0 format. Importing a table gives you greater flexibility and control over the data, but excludes users who are not running Access from updating the data.

Importing or Linking FoxPro Files

If you link a FoxPro file, you can tell Access to use one or more FoxPro index (IDX or CDX) files to improve performance. Access keeps track of the indexes in a special information (INF) file. When you use Access to update the data in your DBF file, Access also updates the index files to reflect your changes.

If you link a DBF file and associate an index (IDX or CDX) file, Access needs the index file to open the linked table. If you delete or move index files or the information (INF) file, you cannot open the linked table.

If your FoxPro files are stored on a read-only drive or CD-ROM, Access cannot create an INF file in the same folder as your DBF or DBC files. To link tables on a read-only drive, you must specify the path to the folder where you want Access to create the INF file. To do so, you need to modify the Windows registry. In the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Xbase key, or in the Access-specific Jet\3.5 key, create the INFPath value and point it to a read/write location in which the INF file can be written.

Note You can also use a user profile to set the INFPath. For more information, see "Running Multiple Data Access Applications" later in this chapter.

To import or link a FoxPro file

Open a database in Access, or switch to the Database window of the open database.
On the File menu, point to Get External Data, and then click Import or Link Tables.
In the Files of Type box, click Microsoft FoxPro to import or link version 2.0, 2.5, or 2.6, or click Microsoft FoxPro 3.0 to import version 3.0.

Note FoxPro 3.0 is not available if you click Link Tables in Step 2.
In the Look in box, switch to the appropriate folder and select the file you want, and then click Import or Link.

If you are importing the file, Access creates a new table named after the file you selected and imports the data from the FoxPro file.

If you are linking the file, Access displays a dialog box in which you can associate FoxPro index files.
For each FoxPro index (IDX or CDX) file you want to link, select the file and click Select. When you finish associating indexes, click Close. If there are no indexes to associate, click Cancel to continue.
If an index you have chosen uniquely identifies each record in the table so that indexed fields do not contain any duplicate values, select the index and click OK.

Note If you do not select an index that provides a unique identifier, Access may not be able to update data in queries with joins to this table, because records cannot be uniquely identified to maintain referential integrity.

Access adds the table name with a linked table icon to the list on the Tables tab in the Database window. When you choose a linked table from the list, you open the FoxPro database file that contains the table, and the data is available as if it were part of your Access database.
Repeat Steps 4 through 6 for each FoxPro file you want to import or link, and then click Close.

After you have imported all the tables, specify a primary key (a unique identifier used to relate tables), and create indexes on all fields you want to search or sort. In addition, check to see whether you want to modify any data types, field properties, or table properties at this time. Then define the relationships between your tables.

For information about how to define relationships with Access, see "Defining Relationships in Microsoft Access" earlier in this chapter. For additional tips on using imported or linked tables, see "Importing and Linking External Tables" earlier in this chapter.

Converting FoxPro Data Types

When you import data from a FoxPro file, Access converts FoxPro data types into the corresponding Access data types. The following table lists the data type conversions.

This FoxPro data type	Is converted to this Access data type
Character	Text
Date	Date/Time
General	OLE Object
Logical	Yes/No
Memo	Memo
Numeric, Float	Number (FieldSize property set to Double)

For information about the differences between these FoxPro data types and their Access equivalents, see "Converting dBASE Data Types" earlier in this chapter.

Using FoxPro 2.5 Files in MS Access

FoxPro 2.5 does not distinguish between tables created with the MS-DOS version of FoxPro and those created with the Windows version. Data in tables created with FoxPro 2.5 for MS-DOS is stored in OEM format. Data in tables created with FoxPro 2.5 for Windows is stored in ANSI format. Access converts all FoxPro 2.5 data from an OEM code page to the ANSI 1252 code page when importing or linking FoxPro 2.5 data, and it converts the ANSI code page to an OEM code page when exporting to FoxPro 2.5 tables. The result is that extended characters (characters with ASCII and ANSI codes above 128) in tables created with FoxPro 2.5 for Windows are not converted properly.

If your FoxPro 2.5 for Windows tables contain extended characters, you may want to ensure that the tables are stored in OEM format (that is, created by FoxPro 2.5 for MS-DOS). Releases of FoxPro subsequent to 2.5 (2.5a, 2.5b, and so on) correctly identify the code page format of the data, so Access can correctly determine how to convert extended characters.

Exporting a Table to a FoxPro File

If you need to save the data from a table or query that has been created in Access so that users who are still working in FoxPro can use it, you can export that data.

To export an Access table or query to a FoxPro file

Open an Access database, or switch to the Database window of the open database.
In the Database window, click the table or query you want to export.
On the File menu, click Save As/Export.
In the Save As dialog box, click To an External File or Database, and then click OK.
In the Save as type box, click the FoxPro format you want.
In the File name box, type a file name for the database file you want to export.
In the Save in box, select the folder where you want to save the file, and then click Export.

If you export a table with table or field names that do not fit the requirements of the destination database, Access adjusts the names. For example, if you export data to a FoxPro table, any field names longer than 10 characters are truncated.

MS Access Registry Settings for FoxPro

Switching from Paradox

To switch from Paradox to Access, you can either link or import data from Paradox DB files. You can then modify the structure of the tables and create the other database objects you need. This section discusses the similarities and differences between Paradox and Access, including terminology and data type conversion.

Note A primary difference between Access and MS-DOS-based applications is the use of the Windows graphical user interface. If your are currently using an MS-DOS-based version of Paradox and are new to Windows, study your Windows documentation to learn basic techniques.

MS Access 97 Terms for Paradox Users

The following table matches Access terms with the equivalent term or command in Paradox. The Access term is not necessarily an exact equivalent of the Paradox term, but rather a term you can look up in Access online Help for more information. You do not need to understand all of these terms before you start using Access.

This Paradox term	Corresponds to this Access term
Alphanumeric data type	Text data type
Edit/Coedit mode	Exclusive/Shared access
Folder of related files	Database
Form	Form
Image PickForm	Open a form
Key field	Primary key
Lookup	List box, combo box
Modify DataEntry	Data Entry command (Records menu)
Modify Restructure command	Table Design view
Multiple-record section	Subform
Query	Query
Script	Module
Table	Table
Tools QuerySpeed	Index
ValChecks	Validation rule
View command	Datasheet view
Zoom command	Find command (Edit menu)

Using Paradox Tables in MS Access

You can use data from tables in Paradox or Paradox for Windows version 3.x, 4.x, or 5.0. format. You cannot use data from tables in Paradox 7 format; however, you can save tables from Paradox 7 to version 5.0 to use the data with Access. If you need to leave your data in Paradox format so that Paradox users can continue to use it, you can link the data to your Access database. By using Access, you can view and edit the data in linked tables even if others are using it in Paradox.

If you want to share Paradox tables with Paradox 7 users by linking tables from Access, Paradox 7 can save and use its tables in version 5.0 format. If you provide the correct password, Access can open encrypted Paradox tables.

If you have no need to maintain your Paradox data in its original format, you can import your Paradox tables into Access. Unlike linking the Paradox table, importing the data creates a copy of the data in Access. Importing a table gives you greater flexibility and control over the data, but excludes users who are not running Access from updating the data.

Importing or Linking Paradox Files

Paradox stores important information about a table's primary key in an index (PX) file. If you link a Paradox table that has a primary key, Access needs the PX file to open the linked table. If you delete or move this file, you cannot open the linked table.

If you link a Paradox table that does not have a primary key, you cannot update data in the table using Access. If you want to be able to update the table, define a primary key for the table in Paradox.

For additional considerations and potential problems when importing and linking Paradox files, see "Using Paradox Databases in Microsoft Access" later in this chapter.

To import or link a Paradox table

Open a database in Access, or switch to the Database window of the open database.
On the File menu, point to Get External Data, and then click Import or Link Tables.
In the Files of Type box, click Paradox.
In the Look in box, switch to the appropriate folder and select the file you want, and then click Import or Link.
If the Paradox table you select is encrypted, Access prompts you for the password. Type the password for the Paradox table, and click OK.

If you are importing the file, Access creates a new table named after the file you selected and imports the data from the DB file.

If you are linking the file, Access adds the table name with a linked table icon to the Tables tab of the Database window. When you choose a linked table from the list, you open the Paradox database file that contains the table, and the data is available as if it were part of your Access database.
Repeat Steps 4 and 5 for each Paradox file you want to import or link, and then click Close.

Converting Paradox Data Types

When you import data from a Paradox table, Access converts Paradox data types into the corresponding Access data types. The following table lists the data type conversions.

This Paradox data type	Corresponds to this Access data type
Alphanumeric	Text
Currency	Number (FieldSize property set to Double)
Date	Date/Time
Memo	Memo
Number	Number (FieldSize property set to Double)
OLE	OLE Object
Short number	Number (FieldSize property set to Integer)

Note Although you can import or link Paradox tables that contain OLE fields, the OLE objects cannot be opened in Access, due to differences in the way Access and Paradox store OLE object header information. If you add a record to a linked Paradox table with OLE fields, those fields are null (blank). Moreover, if you import or link a Paradox 4.x table that contains Memo fields with the Graphic, Binary, or Formatted data types, these fields are not included in the table, because Access cannot read fields of these formats.

Using Paradox Databases in MS Access

The following tips and instructions help you use Access 97 with Paradox databases.

Primary Keys in Paradox Tables

If you link a Paradox table that has a primary key, Access needs the associated index (PX) file in order to open the linked table. If you link a Paradox table with a Memo field, Access needs the associated memo (MB) file in order to open the linked table. If you delete or move these files, you cannot open the linked table.

If a multiple-field primary key is greater than 255 bytes, the table is opened read-only. If you link an empty Paradox table with no primary key, you can add records the first time you use the table. However, because Access cannot update Paradox tables without primary keys, after the table is closed and reopened, it is read-only.

Note You cannot update a Paradox table if its data (DB) or index (PX) file is set to read-only. To check this, use Windows Explorer to display the folder where the files are located, right-click the file, and then click Properties on the shortcut menu. If the Read-only check box on the General tab is selected, clear it and then click OK.

Paradox Tables on a Read-only Drive

You cannot link or import from Paradox tables on read-only drives. This is because Access cannot create the necessary lock files.

Encrypted Tables

Access does not support auxiliary passwords on Paradox tables. To use the table with Access, open the table in Paradox and remove the auxiliary password.

Potential Restructure Problem in Paradox for Windows

Problems occasionally occur when you use the Paradox for Windows table restructure routine. If you are having difficulty getting Access to open your Paradox 4.x table properly, consider restructuring it in Paradox for MS-DOS version 4.0 or 4.5, and then try again.

Paradox Referential Integrity

Access referential integrity does not work with linked Paradox tables. You cannot use linked Paradox tables to trigger cascading deletes or updates, or to prevent orphan records.

Collating Sequences

The collating sequence determines the sort order used when working with tables. When working with Paradox tables, the Microsoft Jet database engine supports the following collating sequences: ASCII, International, Norwegian-Danish, and Swedish-Finnish.

To import or link Paradox tables, the CollatingSequence setting in the Windows registry must match the collating sequence used when the Paradox table was built. The default is setting is ASCII. If necessary, you can change this value in the Windows registry in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft
\Jet\3.5\Engines\Paradox key, in the Access-specific Jet 3.5 registry key, or by using a user profile. For more information, see "Running Multiple Data Access Applications" later in this chapter.

Paradox Tables on a Network

If you are using Access to link Paradox data on a network while Paradox users are working with the data, you must be sure to define all three ParadoxUserName, ParadoxNetPath, and ParadoxNetStyle settings in the Windows registry. For more information about changing Microsoft Jet database engine settings in the Windows registry, see "Running Multiple Data Access Applications" later in this chapter.

The following sections describe how to define these settings and discuss some additional issues when sharing Paradox files on a network.

Specifying a User Name with the ParadoxUserName Setting

The ParadoxUserName setting in the Windows registry defines the name to be displayed by Paradox if a Paradox table is locked by Access and a user accessing the data from Paradox attempts to place an incompatible lock.

To share Paradox tables that use Access with other Paradox users, you must modify the Windows registry. In the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Paradox key, set the ParadoxUserName value to a user name. There cannot be more than one user registered with the same ParadoxUserName on the same network. If you specify a ParadoxUserName, you must also specify ParadoxNetPath and ParadoxNetStyle values, or you receive an error message when trying to gain access to external Paradox data.

Specifying Identical ParadoxNetPath Settings

In Access, for all users sharing the same Paradox database, a setting in the Windows registry must specify the full path to the folder that contains the Paradox.net file (for Paradox 3.x) or the Pdoxusrs.net file (for Paradox 4.x and 5.0). In the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Paradox key, set the ParadoxNetPath value to the full path.

In Paradox 4.x, you can modify this setting either by running Nupdate.exe or by using the -net command-line option. If you use the -net command-line option, be sure to include a backslash ( \ ) at the end of the path (Access and the Paradox Nupdate.exe program place the backslash at the end automatically). The -net command-line option was not available in Paradox prior to version 4.0. For more information, see the Paradox Network Administrator's Guide.

Specifying the Locking Style with the ParadoxNetStyle Setting

Use a setting in the Windows registry to specify the network locking style to use when accessing Paradox data. In the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Paradox key, set the ParadoxNetStyle to **3.**x for Paradox 3.x or **4.**x for Paradox 4.x and 5.0. You can use the Paradox 4.x locking style to gain access to Paradox 3.x, 4.x, and 5.0 tables. You cannot link Paradox 4.x tables while using the Paradox 3.x locking style.

If a Paradox version 3.5 user opens a table, you cannot open or import from this table by using the 4.x locking style. The table is inaccessible from Access until the Paradox 3.5 user closes it.

Linking or Opening Paradox Tables in a Private Folder

Access 97 cannot link or open Paradox tables in the private folder of an active Paradox session. While you have a linked Paradox table open in Access, records added by other users are not visible to you until you close and reopen the table. However, records added by you in Access are visible to Paradox users when their screen is refreshed (if they have Auto Refresh turned on).

Deleting Paradox LCK Files

Occasionally the Paradox LCK file is inadvertently left in a folder. This prevents either Access or Paradox from accessing files in that folder. To fix this, delete the LCK file.

Exporting a Table to a Paradox File

If you need to save the data from a table or query that has been created in Access so that users who are still working in Paradox can use it, you can export that data.

To export an Access table or query to a Paradox file

Open an Access database, or switch to the Database window of the open database.
In the Database window, click the table or query you want to export.
On the File menu, click Save As/Export.
Click To an External File or Database, and then click OK.
In the Save as type box, click the Paradox format you want.
In the File name box, type a file name for the database file you want to export.
In the Save in box, select the folder where you want to save the file, and then click Export.

If you export a table with table or field names that do not fit the requirements of the destination database, Access adjusts the names. For example, if you export data to a Paradox table, any field names longer than 10 characters are truncated.

If you attempt to export an Access table containing long field names to Paradox, and the first 25 characters are identical to an existing field name, Access generates a unique field name.

When Access exports a table with a field name containing a number sign ( # ), it is converted to a period. An opening or closing parenthesis, curly bracket, or quotation mark is converted to an underscore character ( _ ).

Exporting Access Memo fields to Paradox format results in extra carriage return characters.

When you use Visual Basic to create Paradox tables, text columns default to 255 characters if you do not specify a field width by using the FieldSize property. Wide columns such as these make it easy for the new table to exceed the 1,350-character maximum width of a Paradox table with a primary key. If you attempt to append a primary index to a Paradox table wider than 1,350 characters, you receive an error message.

MS Access Registry Settings for Paradox

Access 97 uses the Microsoft Jet 3.5 database engine to read and write data, whether it is using its own native tables or linking tables in other data formats. The settings Microsoft Jet version 3.5 uses to initialize the Paradox driver are located in the Windows registry in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines\Paradox key.

The Setup program automatically creates recommended registry settings. However, you can modify the settings in this key, in the Access-specific Jet 3.5 registry key, or by using a user profile. For more information, see the following section, "Running Multiple Data Access Applications." For more information about Access registry settings, see Access online Help.

Running Multiple Data Access Applications

Custom applications developed with Access 97, Excel 97, Microsoft Visual Basic version 5.0, and Microsoft Visual C++ version 5.0 can use the Microsoft Jet 3.5 database engine to perform their database-related operations. If you run one or more of these applications on the same computer, they all initialize the Microsoft Jet 3.5 database engine by using the Windows registry settings in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines key.

If you modify any of these settings, your changes apply to all applications using the Microsoft Jet 3.5 database engine on the same computer. There are three ways you can avoid this:

Change the settings in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Office \8.0\Access\Jet\3.5\Engines key.

Any settings defined in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Office \8.0\Access\Jet\3.5\Engines key override settings in the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.5\Engines key and apply only to Access 97.
If you have Microsoft Office 97 Developer Edition, use the Setup Wizard to create an installation program for a custom Access application that sets up a user profile.

A user profile is an alternative set of registry keys that can contain settings that override the standard Access and Microsoft Jet 3.5 settings. A user profile is specified by using the /profile command-line option when you start your application. User profiles are analogous to the *appname.*ini files used in previous versions of Access.
Create a user profile by using the Registry Editor, and then specify it by using the /profile command-line option when you start your application. For more information about user profiles, see Access online Help.

For more information about Access registry settings, see Access online Help.

Similar considerations apply to database applications developed with Access 95, Excel 95, Microsoft Visual Basic version 4.0, and Microsoft Visual C++ version 4.0, because they can share the same Microsoft Jet version 3.0 database engine. However, database engine settings in these applications do not affect applications that share use of the Microsoft Jet 3.5 database engine, and are read from a separate set of subkeys under the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Jet \3.0\Engines key.

These considerations do not apply to Access versions 1.x and 2.0 and Visual Basic version 3.0, which use INI files for initialization information. The following table shows where each of these previous version applications looks for its initialization information.

This application	Looks in this INI file
Access 1.x	Msaccess.ini
Access 1.x or 2.0 custom application	appname.ini
Access 2.0	Msacc20.ini
Visual Basic 3.0 .exe application	appname.ini
Visual Basic 3.0 at design time	Vb.ini
Visual Basic 3.0 at run time	Vb.ini

Using Built-in Drivers and ODBC Drivers

In Access, you can import, export, and link data from a number of different database formats and from spreadsheets and text files. To connect to a particular type of data, Access uses either a built-in driver or an ODBC driver. A driver is a dynamic-link library (DLL) used to connect a specific data source with another client application; in this case, Access.

Built-in Drivers

You can use built-in drivers to import, export, or link the following types of data:

dBASE III, dBASE III+, dBASE IV, and dBASE 5.0
FoxPro 2.x and 3.0 (import only for 3.0)
Paradox 3.x, 4.x, and 5.0
Access databases, and other databases that use the Microsoft Jet database engine, such as those created with Visual Basic, Visual C++, or Excel

This includes databases created in previous versions of Access.
Excel 3.0, 4.0, 5.0, 95, and 97
Lotus 1-2-3 WKS, WK1, WK3, and WK4 formats (import and link only for WKS and WK4)
Delimited and fixed-width text files
HTML 1.0 lists, and HTML 2.0 and 3.x lists and tables
Internet Database Connector/HTML Extension (IDC/HTX) files (export only)
Active Server Pages (ASP) files (export only)
Exchange and Outlook data (collectively referred to as Microsoft Windows Messaging Service data). Linking data in this format is read-only.
Microsoft Word for Windows mail merge data files (export only)

Products from other vendors and other Microsoft products also contain ODBC drivers, including drivers for the applications in the previous list. These drivers may have been installed on your computer. If you want to know whether these drivers have been tested and verified for use with Access, contact the driver vendor. Drivers for Paradox, Lotus 1-2-3, and Microsoft Messaging are available in the Office 97 Value Pack.

Note The CD-ROM version of Office 97 includes the Office 97 Value Pack, a collection of application extras such as clip art, maps, sounds, presentation enhancements, and utilities. For more information about the Value Pack and how to use its contents, see Valupack.hlp in the ValuPack folder on the Office CD. You can also point to Microsoft on the Web (Help menu) in any Office application, and then click Free Stuff.

ODBC Drivers

If you want to import, export, or link data from an ODBC data source, you need to install a 32-bit version ODBC driver, such as Microsoft SQL Server Driver. The Microsoft SQL Server Driver is an ODBC driver that has been tested and verified for use with Access. This driver is supplied with Office.

The Microsoft SQL Server Driver is not included when you choose a Typical installation during Setup. To install the driver, rerun Setup and click Add/Remove. Under the Data Access option, select Database Drivers, and then select the Microsoft SQL Server Driver check box.

After you install the Microsoft SQL Server Driver, use the ODBC Data Source Administrator to add or modify ODBC data sources.

To set up ODBC data sources by using the ODBC Data Source Administrator

In Control Panel, double-click the 32bit ODBC icon.
In the ODBC Data Source Administrator dialog box, click the appropriate DSN tab.

For information about using the User DSN, System DSN, and File DSN tabs, click Help.

For more information about ODBC and how it works, see "Using Microsoft Access with ODBC and Client/Server Operations" in Chapter 27, "Sharing Information with Microsoft Office Applications."