Query Hangs If Adding 256-Field Table from Excel Data Source

The information in this article applies to:

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Query, version 2.0

SYMPTOMS

In Microsoft Query version 2.0 (32-bit for Windows 95), when you attempt to add a table containing 256 fields from a Microsoft Excel data source (that is, a Microsoft Excel spreadsheet that has been defined as a data source in Control Panel using the Microsoft Excel ODBC 3.0 driver) to the Query Table window, Microsoft Query stops responding (hangs) while it attempts to add the table to the query.

CAUSE

This problem occurs because the Microsoft Excel spreadsheet driver has a limit of 255 fields. In Microsoft Query version 1.0 (16-bit for Windows 3.x), a table containing 256 fields can be added to the Query Table window, but if you attempt to add all 256 fields to the data pane from the table, you receive an error message.

For additional information about this problem in Microsoft Query version 1.0, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q123003
   TITLE     : MSQUERY: Error Adding 256 Fields Using Excel Data Source

RESOLUTION

To avoid this problem, convert the Microsoft Excel data source to a dBASE III or dBASE IV file by using the following steps:

1. Open the Microsoft Excel file in Microsoft Excel 5.0 or 7.0.

2. Highlight the range of data (all 256 columns and as many rows as you want to include). On the Insert menu, point to Name, and then click Define.

3. Name the highlighted range Database.

4. On the File menu, click Save As. In the Save As Type dialog box, click either of the following

         DBF 4 (dBASE IV) (.dbf)
   

         -or-
   

         DBF 3 (dBASE III)(.dbf)
   

   and then click Save. The file is saved with a .dbf file extension, which indicates a dBASE file.

5. In Windows 95, go to Control Panel, and double-click the 32bit ODBC icon.

6. In the Data Sources dialog box, click Add, and then click "Microsoft dBase Driver (*.dbf)" from the Installed ODBC Drivers list. Click OK.

7. In the ODBC dBase Setup dialog box, enter the new data source name and click dBase III or dBase IV from the version list. Click Select Directory and then click the directory where you saved the dBASE file. Click OK twice.

8. Make sure that the newly-defined data source is listed in the Data Sources dialog box, and then click Close.

9. Return to Query 2.0 and open a new query using the new dBASE data source.

STATUS

Microsoft has confirmed this to be a problem in ODBC version 3.0 for Windows 95 and Windows NT. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

"Microsoft Query User's Guide," shipped with Microsoft Excel version 5.0