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

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.