Maximum number of fields in table

[Evan Robitaille]

I have run into a problem with an ODBC table connection. The table I am connected to exceeds the number of fields allowed in an Access table. I am using Access 2003. When I looked on Microsoft help site I think the max number of fields in a recordset is 242 so I am unable to see all of the fields in the original table I am linking to. Does anyone know if this limitation has changed in the newest version of Access? I checked the Filemaker www site and that program does not have the same limitations. Thanks in advance for any help!

 

[Rickster57]

fields

I would be surprised if the limit has changed. Either way, that is a LARGE number of fields in a table.

 

[Ron_dK]

The table I am connected to exceeds the number of fields allowed in an Access table.
.....the max number of fields in a recordset is 242.....

As far as I know the maximum number of fields is 255. Having said that I really wonder why one needs a table with such high number of fields. This goes against all rules for 1st, 2nd and 3rd normalization. Guess you'll have to review your Db design.

Hth

 

[Moniker]

You can always split the imported table (the one with the ridiculous number of fields) into multiple tables and create a unique ID on each to match the records. For example, if you're importing a table with 300 fields, you would import the first 150 fields and a primary key in to table 1, and then you would import the other 150 fields using the same primary key in to table 2.

To reiterate what others have already said though, that many fields in a table is breaking pretty much every rule of normalization. The design is all wrong and is just asking for errors and maintenance nightmares.

~Moniker

 

[The_Doc_Man]

If this import came from a spreadsheet or an externally generated text file, you are asking Access to monolithically convert something it does not want to convert - namely, a very "wide" flat file.

If I were given this goal by my boss at work, my first step after detecting the "wide" tables would be to discuss the "real" goals of the DB so that I ould know which way to proceed. Also, I would want to somehow survey the data to decide whether I was facing a lot of duplication in non-key columns. (A sure sign that some table splitting would be in order.

I work with U.S. Government databases and I very rarely see more than about 100 fields on the worst tables we have. I am sure that you can find a way to minimize the table's "width" by doing a partitioned import. (That is, import it a few fields at a time.) Or if it is a text file, try importing it by reading it as a file in VBA. Perform recordset operations to do inserts into multiple tables. Use the Importer to import a part of the file at once. Go back and import yet another part, etc. until you have it all.

Normally, I would say that a one-to-one relationship is rare. However, if it is the only way to get it all in for reduction processing, then I say go for it that way. Import several different pieces based on the same prime key.