Microsoft KB Archive/248270

= ACC2000: "Too Many Fields" Error When Tables in a Query Produce More Than 255 Columns =

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.



Steps to Reproduce the Behavior
 Create a new database. In the Database window, click Module on the Insert menu. On the Tools menu, click References, and then create a reference to '''Microsoft ADO Ext. 2.5 for DDL and Security'''.  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  Type subMakeTables in the Immediate window, and then press ENTER.</li> Click OK in the message box that informs you that the tables were created successfully, and then close the Visual Basic Editor.</li> In the Database window, click Queries under Objects, click New, click Design View, and then click OK.</li> Select both tables in the Show Table dialog box, click Add, and then click Close.</li> Create a join between Field1 of Table1 and Field1 of Table2.</li> Add all the fields from Table1 to the query design grid.</li> Add just the first 55 fields from Table2 to the query design grid. This produces 255 columns within the query.</li> On the Query menu, click Run. Note that the query runs without error.</li> On the View menu, click Design View. Add Field56 from Table2 to the query design grid. This produces 256 columns in the query.</li> On the Query menu, click Run. Note that you receive the error message mentioned in the "Symptoms" section earlier in this article.</li></ol>

<div class="references_section">