Microsoft KB Archive/244649

= ACC2000: Cannot Open Access Project Table That Has More Than 255 Columns =

Article ID: 244649

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q244649



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
When you open a table that has more than 255 columns in an Access project, you receive the following error messages, and then the table does not open:

Too many columns defined in a rowset

-and-

Microsoft Access can't open the table in datasheet view.

Views and stored procedures that return more than 255 columns exhibit the same behavior.



CAUSE
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



RESOLUTION
Create a SQL Server view that displays fewer than 255 fields from the original table, and then use the view to browse and update your data.



Steps to Reproduce Behavior
  In an Access database, create and run the following procedure. It creates a script named MyScript.sql that you use to create a table with 256 columns on your SQL Server or MSDE computer. Sub CreateScript Dim intCounter As Integer

Open "c:\MyScript.sql" For Append As #1 Print #1, "USE PUBS" Print #1, "GO" Print #1, "CREATE TABLE MoreThan255" Print #1, "(" Print #1, "Field1 int IDENTITY (1,1) PRIMARY KEY NOT NULL,"

For intCounter = 2 To 255 Print #1, "Field" & LTrim(Str(intCounter)) & " varchar(3)," Next

Print #1, "Field256 varchar(3)" Print #1, ")" Print #1, "GO"  Print #1, "INSERT MoreThan255 (Field2) VALUES ('xyz')"

Close #1 End Sub NOTE: Run this procedure on the same computer on which you complete steps 2 through 4.  At a command prompt, type the following command, replacing  with the name of your server and   with the password for the sa logon. When you have typed the entire command line, press ENTER.

OSQL /S  /U sa /P   /n /i c:\myscript.sql

When you see the following output, a 256-column table containing one record of data has been created in the pubs sample database on your server.

(1 row affected)

NOTE: If your server does not manage a copy of the pubs sample database, manually edit the MyScript.sql script by changing the Print #1, "USE PUBS" line to include a database that does exist on the server. Open an Access project connected to the pubs sample database. Try to open the MoreThan255 table and note the error messages.

<div class="references_section">