Microsoft KB Archive/248270

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:50, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Article ID: 248270

Article Last Modified on 6/28/2004



APPLIES TO

  • Microsoft Access 2000 Standard Edition



This article was previously published under Q248270

This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.


SYMPTOMS

When you join two or more tables in a query and add the fields that you want to the query design grid, you may receive the following error message when you run the query:

Too many fields defined.

CAUSE

The Microsoft Jet database engine limits the number of output fields that a query can have to 255 columns. Similar to an Access table, a query can have no more than 255 columns.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new database.
  2. In the Database window, click Module on the Insert menu.
  3. On the Tools menu, click References, and then create a reference to Microsoft ADO Ext. 2.5 for DDL and Security.
  4. Enter the following code in the module:

    Option Compare Database
    Option Explicit
    
    Sub subMakeTables()
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This code requires a reference to the following library:
        '   Microsoft ADO Ext. 2.x for DDL and Security.
        'where 2.x should be 2.1 or higher.
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim Cat As New ADOX.Catalog
        Dim Tbl1 As New ADOX.Table
        Dim Tbl2 As New ADOX.Table
        Dim i As Integer
        
        'Open the catalog.
        Cat.ActiveConnection = CurrentProject.Connection
    
        'Create the first Table.
        With Tbl1
            .Name = "Table1"
            'Create fields and append them to the new table object.
            For i = 1 To 200
                .Columns.Append "Field" & CStr(i), adWChar, 10
            Next i
        End With
    
        'Add the new table to the database.
        Cat.Tables.Append Tbl1
        
        'Create the second Table.
        With Tbl2
            .Name = "Table2"
            'Create fields and append them to the new table object.
            For i = 1 To 56
                .Columns.Append "Field" & CStr(i), adWChar, 10
            Next i
        End With
    
        'Add the new table to the database.
        Cat.Tables.Append Tbl2
    
        Set Tbl2 = Nothing
        Set Tbl1 = Nothing
        Set Cat = Nothing
        
        MsgBox "Tables created successfully."
    End Sub
                        
  5. Type subMakeTables in the Immediate window, and then press ENTER.
  6. Click OK in the message box that informs you that the tables were created successfully, and then close the Visual Basic Editor.
  7. In the Database window, click Queries under Objects, click New, click Design View, and then click OK.
  8. Select both tables in the Show Table dialog box, click Add, and then click Close.
  9. Create a join between Field1 of Table1 and Field1 of Table2.
  10. Add all the fields from Table1 to the query design grid.
  11. Add just the first 55 fields from Table2 to the query design grid. This produces 255 columns within the query.
  12. On the Query menu, click Run. Note that the query runs without error.
  13. On the View menu, click Design View. Add Field56 from Table2 to the query design grid. This produces 256 columns in the query.
  14. On the Query menu, click Run. Note that you receive the error message mentioned in the "Symptoms" section earlier in this article.


REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

199076 ACC2000: "Too Many Fields Defined" Error Message in Update Query



Additional query words: prb

Keywords: kberrmsg kbprb KB248270