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

ID: Q150245

7.00 WINDOWS kbtool kbprb

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.

NOTE: Because there are several versions of Windows, the following steps may be different on your computer. If they are, please consult your product documentation to complete these 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.

The dBASE data source should allow you to add the table to the table pane, and to add all 256 fields to the data pane.

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

KBCategory: kbtool kbprb KBSubcategory: xlquery

Additional reference words: 7.0 freeze

Keywords          : xlquery 
Version           : 7.00
Platform          : WINDOWS

Last Reviewed: May 22, 1998