Some Access 2007 questions

[Michael Feldman]

Hi.

I know that in earlier versions of Access, there was a limit of 255 fields
per table. Has this restriction been lifted in Access 2007? If so, what
is the new maximum limit.

Also, can you use an array on fieldnames in Access 2007? In other words,
right now, we are using field names such as:

"clYear_0", "clYear_1" and so on to store an array called clYear().

Could an array be stored in a less clumsy fashion?

 

[Tony Toews [MVP]]

I know that in earlier versions of Access, there was a limit of 255 fields
per table. Has this restriction been lifted in Access 2007? If so, what
is the new maximum limit.

No. And you should never be anywhere close to that 255 limit anyhow.

Also, can you use an array on fieldnames in Access 2007? In other words,
right now, we are using field names such as:

"clYear_0", "clYear_1" and so on to store an array called clYear().

Could an array be stored in a less clumsy fashion?

Yes, the year should be part of the record key so you have each years data in
different records. That is the data is vertical rather than horizontal.

However John Vinson does a much better job than I do of explaining these concepts.
So over to you John.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Pat Hartman \(MVP\)]

1. The field limit is still 255
2. You have created a spreadsheet and called it a table and that is why you
are having issues with the column limit. Instead of storing the year values
in additional columns (every year you have to add a new column to the table
and change all your queries, ex.), you should be storing them as rows. Do
some reading on normalization to help you restructure your spreadsheet into
real relational tables.
3. Repeating groups (which is what you have) violate first normal form so NO
relational database will ever support them. You won't need arrays if you
normalize your table. Queries with criteria that specify a year range will
bring back all the records you want and you can sum them, average them, etc.
without any code or looping through arrays.

 

[Michael Feldman]

Thanks for the feedback I'll research your suggestions.