MSQuery: "Out of Memory" Error with More Than 4,000 Tables

The information in this article applies to:

• Microsoft Query, versions 1.0, 2.0
• Microsoft Excel for Windows, version 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0, 7.0a

SYMPTOMS

In Microsoft Query, when you access a database containing more than 4,000 tables, you may receive the following error message:

   Out of Memory

CAUSE

This error occurs when Microsoft Query attempts to update the Tables list in the Add Tables dialog box.

WORKAROUND

To work around this problem, try eliminating the number of tables displayed in the Add Tables dialog box by using one of the following methods:

Method 1: Change the table options in MSQuery

The Add Tables dialog box displays a list of tables for the current data source. This list consists of tables, view, synonyms, and system tables. To specify which table types to display, click the Options button in the Add Tables dialog box. The Table Options dialog box is displayed, and you can then pick the table types with which you want to work. By doing this, you can reduce the number of tables that need to be added to the list. To change the table options, follow these steps:

1. Start Microsoft Query.

2. On the File menu, click New Query, and select a data source based on one of the following ODBC drivers:

         Btrieve
         Excel
         dBASE
         FoxPro
         Paradox
         Text
   

   If you do not have any of the drivers listed above on your computer, you can also choose other third-party ODBC drivers.

3. In the Add Tables dialog box, click Options.

4. In the Options dialog box, clear the check boxes for the following settings:

            Views
            Synonyms
            System Tables
   
      NOTE: This step assumes that you only want to see tables in the dialog
      box. Do not disable all the options (that is, leave the Tables option
      enabled) or you will end up with an empty list.
   
   

5. In the Options dialog box, click OK.

6. In the Add Tables dialog box, click Cancel.

7. On the File menu, click Close Query.

8. On the File menu, click New Query.

9. Choose the data source you originally intended to use.

   If the problem persists, it may not be possible to use Microsoft Query with your database unless modifications are made to the source database so that fewer tables exist in a single database. If this is the case, you should try Method 2.

Method 2: Split up the database

This method involves dividing up your tables into two or more separate databases so that there are less than 4,000 tables in each database. Consult your database documentation for more details on how to perform this operation.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.