Change number of Columns from a max of 255 to 1000

[Guest]

Hi

How do i increase the columns in MSAccess from 255 to 1000 when runing a
query off two tables i need to merge? There are 525 columns I need to combine
into one table. I keep on getting an err 'Too many fields defined" (Error
3190)

Thanks
Pete

 

[Roger Carlson]

You don't. 255 is the maximum number of columns in a table or query.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Joseph Meehan]

Hi

How do i increase the columns in MSAccess from 255 to 1000 when
runing a query off two tables i need to merge? There are 525 columns
I need to combine into one table. I keep on getting an err 'Too many
fields defined" (Error 3190)

Thanks
Pete

You don't. You might be able to use a memo field that allows much more
text, but it also has some limitations that text fields do not. You could
also might be able to define a "field" in VBA and use that. It sort of
depends on what you want to do.

If you are talking about needing more fields (I hate the world column as
it seems to have more meanings) then the answer is no. However the
question is why do you need all these fields. 99.87% of the time if someone
thinks they need more than 200 fields they really don't, but they do need to
redesign their table design to normalize the data, likely because they have
been committing the #1 sin of database that is they committed spreadsheet.

Databases are not spreadsheets and the work with _related_ tables.

Could you give us an idea of where all these required fields are coming
from.

It is sort of like trying to list all the children of Mr. or Mrs Jones
by adding fields. That is not correct. You add a second table to list
children and the records in that table are then related to the parent(s) of
the "Parent" table.

 

[Guest]

Move all your stuff out to Excel 2007 as it can handle 1600 columns. Sounds
like you are using Access as a spreadsheet anyway.

 

[Guest]

255 is the maximin number of columns in a table. If you need more than that
your databse structure not normalized. Break your tables up linking on a
unique key field.
For a complete list of Access specifications go to access help and type
"specicfications".