Problems Using .DBF Files in Microsoft Query

ID: Q120504

The information in this article applies to:

Microsoft Query for the Macintosh, version 1.0
Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Query, when you use a .DBF file created in Microsoft Excel, one of the following symptoms occurs:

You do not see any of the Microsoft Excel files in the Add Table dialog box.

You receive the following error message when you attempt to add the table to a query

Can't access table <path\file name>

where <path\file name> is the full path and file name of the table you are adding.

For additional information, please see the following article in the Microsoft Knowledge Base:

Q107835 Query: Microsoft Query Error Message "Cannot Access Table"

CAUSE

The first symptom occurs if the FoxPro ODBC driver does not have custom file types entered.

The second symptom occurs if the table file name does not end with a .DBF extension.

WORKAROUNDS

To configure a data source to show and use .DBF files created in Microsoft Excel, do the following:

From the Apple menu, choose Control Panels. Double-click the ODBC Setup icon.

Choose the Setup Data Sources button.

Choose the Add button to add a new data source, or select an existing FoxPro data source, and choose the Modify button.

If you are adding a new data source, select "Q+E/Microsoft FoxPro Driver," and then choose OK.

After you type a Data Source Name, choose the Mac File Info button, and enter the following information: File Creator: XCEL Data Base File Type: BINA
NOTE: You must use uppercase letters for the names you type.

You can leave the other items as they are, because Microsoft Excel doesn't create index files or memo files.

When you use Microsoft Query and select the data source that you just created, only Microsoft Excel files are displayed, and you can select any dBASE file that has a .DBF extension.

To avoid receiving the error message above when you save a file in DBF file format from Microsoft Excel, do the following:

Activate the worksheet in the workbook that contains the list that you want to save.

From the File menu, choose Save As. From the Save File As Type list, select DBF4 (DBASE IV).

Enter the desired file name, and be sure to use the .DBF file name extension. For example, name your file DATABASE.DBF.

MORE INFORMATION

For additional information concerning the use of dBASE files on the Macintosh, please see the following articles in the Microsoft Knowledge Base:

Q140701 "Microsoft Query May Open the Wrong DBF File"

Q107835 "Microsoft Query Error Message "Cannot Access Table"

Additional query words: 1.00

Keywords : kbtool xlquery
Version : MACINTOSH: 5.0
Platform : MACINTOSH
Issue type : kbprb