Microsoft KB Archive/150245

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

Article ID: 150245

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Query 2.0

-



This article was previously published under Q150245





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:

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

 Open the Microsoft Excel file in Microsoft Excel 5.0 or 7.0. 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. Name the highlighted range Database. 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)</li></ul>

and then click Save. The file is saved with a .dbf file extension, which indicates a dBASE file.</li> In Windows 95, go to Control Panel, and double-click the 32bit ODBC icon.</li> In the Data Sources dialog box, click Add, and then click "Microsoft dBase Driver (*.dbf)" from the Installed ODBC Drivers list. Click OK.</li> 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.</li> Make sure that the newly-defined data source is listed in the Data Sources dialog box, and then click Close.</li> Return to Query 2.0 and open a new query using the new dBASE data source.</li></ol>

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.

<div class="status_section">

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.

<div class="references_section">