Microsoft KB Archive/248270: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
(One intermediate revision by the same user not shown) | |||
Line 12: | Line 12: | ||
<div id="TitleRow"> | <div id="TitleRow"> | ||
= <span id="KB248270"></span>ACC2000: | = <span id="KB248270"></span>ACC2000: "Too Many Fields" Error When Tables in a Query Produce More Than 255 Columns = | ||
Line 105: | Line 105: | ||
'Create the first Table. | 'Create the first Table. | ||
With Tbl1 | With Tbl1 | ||
.Name = | .Name = "Table1" | ||
'Create fields and append them to the new table object. | 'Create fields and append them to the new table object. | ||
For i = 1 To 200 | For i = 1 To 200 | ||
.Columns.Append | .Columns.Append "Field" & CStr(i), adWChar, 10 | ||
Next i | Next i | ||
End With | End With | ||
Line 117: | Line 117: | ||
'Create the second Table. | 'Create the second Table. | ||
With Tbl2 | With Tbl2 | ||
.Name = | .Name = "Table2" | ||
'Create fields and append them to the new table object. | 'Create fields and append them to the new table object. | ||
For i = 1 To 56 | For i = 1 To 56 | ||
.Columns.Append | .Columns.Append "Field" & CStr(i), adWChar, 10 | ||
Next i | Next i | ||
End With | End With | ||
Line 131: | Line 131: | ||
Set Cat = Nothing | Set Cat = Nothing | ||
MsgBox | MsgBox "Tables created successfully." | ||
End Sub | End Sub | ||
</pre></li> | </pre></li> | ||
Line 143: | Line 143: | ||
<li>On the '''Query''' menu, click '''Run'''. Note that the query runs without error.</li> | <li>On the '''Query''' menu, click '''Run'''. Note that the query runs without error.</li> | ||
<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> | <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> | ||
<li>On the '''Query''' menu, click '''Run'''. Note that you receive the error message mentioned in the | <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> | ||
Line 154: | Line 154: | ||
<div class="indent"> | <div class="indent"> | ||
[[../199076|199076]] ACC2000: | [[../199076|199076]] ACC2000: "Too Many Fields Defined" Error Message in Update Query | ||
Latest revision as of 13:50, 21 July 2020
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:
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
- 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.
- Click OK in the message box that informs you that the tables were created successfully, and then close the Visual Basic Editor.
- In the Database window, click Queries under Objects, click New, click Design View, and then click OK.
- Select both tables in the Show Table dialog box, click Add, and then click Close.
- Create a join between Field1 of Table1 and Field1 of Table2.
- Add all the fields from Table1 to the query design grid.
- Add just the first 55 fields from Table2 to the query design grid. This produces 255 columns within the query.
- On the Query menu, click Run. Note that the query runs without error.
- On the View menu, click Design View. Add Field56 from Table2 to the query design grid. This produces 256 columns in the query.
- 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