Limitation of 255 fields

  • Thread starter Thread starter SANJAY SHAH \(MICROBRAIN Computers Pvt. Ltd.\)
  • Start date Start date
S

SANJAY SHAH \(MICROBRAIN Computers Pvt. Ltd.\)

Dear Sir,

In Access 2000, it is having limitation of 255 fields in Table & in
Recordset. But In database like Clipper 5 it was having facility to 1024
fields.

If I want more than 255 fields in one recordset generated using query then
how I can do using Access 2000.

Sanjay Shah
 
The general concensus around here is that 25-30 fields should be enough for
almost all situations. Access tables and queries have the limit which can't
be breached.

Perhaps if you described your table structure, someone might be able to
assist you.
 
Being a relational database, you should have your data stored in multiple
tables with around twenty or thirty fieds per table. Each table can be
related to the others using like fields. Making one big table is not much
better than just putting it all in a spreadsheet.

For example, in an employee database, you might have one table with
EmployeeNumber and the name and address information. You might have another
table with EmployeeNumber and the employee's training completions. You
might have another table with EmployeeNumber and a list of awards and
honors. Another table might contain EmployeeNumber and vvacation
information. Another would have EmployeeNumber and salary information.

By keeping a like field in each table, you can relate them all back to the
basic data table.

Each table does not need the same key (in this example EmployeeNumber). You
might have another table that contains all the valid employee titles and pay
grades. These would link to the tables above based on fields in them that
pointed to the data desired.

Hope that helps,

Rick B
 
If I want more than 255 fields in one recordset generated using query then
how I can do using Access 2000.
Click to expand...

You cannot. Period.

Clipper is not fully relational. Access isn't either, of course, but
it's a LOT closer. IMHO no properly designed relational table would
EVER need anywhere near 1000 fields, or even 255. If you're trying to
import the data from Clipper, you should be able to do so (with
considerable tedium) by chopping it into 255 field chunks and
importing into a properly normalized table structure; to export you
may need to write VBA code using ODBC links to the Clipper tables.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Dear Sir,

For Storing purpose I had kept so many tables & all the tables at linked
together.
But while reporting in one recordset if I want all the fields from all the
related table then what to do.

Suppose my tables as follows.
InvoiceRegister
PartyMaster
InvoiceProduct
ItemMaster
BatchMaster
AccountMaster
GatepassRegister

If I want one recordset in which it should have fields from all the tables
then what to do if it is more than 255 fields.

Because we can't relate two recordset for reporting purpose.

Sanjay Shah
 
I can't conceive of writing a report that contains more than 255
distinct fields. Note that you don't normally need to include the
linking fields of what may be a very complex relational structure as
columns in the source query of the Report. Alternatively, if your
report is essentially an ordered dump of all your data, divide it into
a family of Reports.

Dear Sir,

For Storing purpose I had kept so many tables & all the tables at linked
together.
But while reporting in one recordset if I want all the fields from all the
related table then what to do.

Suppose my tables as follows.
InvoiceRegister
PartyMaster
InvoiceProduct
ItemMaster
BatchMaster
AccountMaster
GatepassRegister

If I want one recordset in which it should have fields from all the tables
then what to do if it is more than 255 fields.

Because we can't relate two recordset for reporting purpose.

Sanjay Shah
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Dear Sir,

For Storing purpose I had kept so many tables & all the tables at linked
together.
But while reporting in one recordset if I want all the fields from all the
related table then what to do.
Click to expand...

Is it really necessary to munge all your fields from all your tables
into an enormously wide flat recordset, in order to do reporting?
Instead, consider using a Report with Subreports.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Dear Peter,

Actually I have my own report writer. In which I allowed user to Select
Field for Report from recordset. If I can't get all required fields from
report then how I can manage my report writer.

Sanjay Shah
 
You can use the Me.Print method to print almost any value you want on your
report.

I'm not sure that you have justified a need for more than 255 fields.
 
SANJAY said:
Actually I have my own report writer. In which I allowed user to Select
Field for Report from recordset. If I can't get all required fields from
report then how I can manage my report writer.
Click to expand...


As part of your "report writer", construct the report's
record source sql statement with just the required fields
and tell the users they can not select more than 255 fields.
 
SANJAY SHAH (MICROBRAIN Computers Pvt. Ltd.) said:
Dear Sir,

In Access 2000, it is having limitation of 255 fields in Table & in
Recordset. But In database like Clipper 5 it was having facility to 1024
fields.

If I want more than 255 fields in one recordset generated using query then
how I can do using Access 2000.

Sanjay Shah
Click to expand...

Create tables with a one to one relationship for the overflow. I had a
survey with 300 questions on it and
that's what I did. One table had a user ID and the first 150 questions.
The second table had the user ID
and the last 150 questions and the tables were linked with a one to one
relationship. It worked OK.

Tom Lake
 
This would not work to join all fields from both tables into a query since
queries are limited to 255 fields. It would however work with subsets of
fields.

I would not have wanted to create the tables, forms, reports, queries,...
for tables with that many fields.
 
You would probably have been better off with a more relational
structure!

If you have a survey with 300 questions, you should probably have one
Table containing the question text, question type, and a primary key
field for each question, and a second table linked to it with the
individual answers to each question for each instance of the
questionnaire. This has all sorts of advantages in terms of reuse of
slightly different versions of the questionnaire and data analysis
over the "single flat table" approach.


Create tables with a one to one relationship for the overflow. I had a
survey with 300 questions on it and
that's what I did. One table had a user ID and the first 150 questions.
The second table had the user ID
and the last 150 questions and the tables were linked with a one to one
relationship. It worked OK.

Tom Lake
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top