ACC2000: Can't Open Access Project Table That Has More Than 255 Columns

• Microsoft Access 2000

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.

CAUSE

Microsoft has confirmed this to be a problem in the Microsoft products 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.

MORE INFORMATION

Steps to Reproduce Behavior

1. 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.



2. At a command prompt, type the following command, replacing ServerName with the name of your server and Password with the password for the sa logon. When you have typed the entire command line, press ENTER.

   OSQL /S ServerName /U sa /P Password /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.



3. Open an Access project connected to the pubs sample database.



4. Try to open the MoreThan255 table and note the error messages.

REFERENCES

For more information about OSQL, refer to SQLServer 7.0 Books Online available for download from the following Microsoft Web site:

http://support.microsoft.com/download/support/mslfiles/sqlbol.exe

Additional query words: pra

Keywords : kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug