What are the maximum allowed number of columns in a query in access 2003?
+1
A:
As a general rule, if you ever find yourself asking a question about the maximum hardcoded limit of a technology, it's time to step back and verify that you're taking the right approach. Perhaps a query against access that's pulling in hundreds or thousands of columns isn't the right approach.
Greg D
2008-10-09 18:12:43
That's a poor answer. Perhaps I have data that I am querying which is really wide, or I need to tie a couple of tables together which are all wide. Often in software you have to do things that may not be the right approach but gets to your end result.
Brettski
2008-10-09 18:24:39
I think it's a legitimate point to raise, particularly with applications like Access that don't scale very well.
Dave DuPlantis
2008-10-09 18:27:17
Access is being used as a quick report generator, something it's really good at.
Brettski
2008-10-09 18:33:41
Any table or query with number of columns approaching 255 is badly designed and that's the source of the problem, regardless of the database engine in use.
David-W-Fenton
2008-10-11 20:59:48
Sometimes you're forced to bring in data for conversions from other poorly designed systems in order to clean them up. Doesn't mean you are going to place it in production. If you do, it will either show up in poor performance or it just doesn't matter how many columns your table has.
Jeff O
2009-08-20 18:07:59
+3
A:
255 I believe. You can check by going to Help > Specifications > Query within Access.
Ben Hoffstein
2008-10-09 18:13:56
A:
From Access Help File
Thank you Ben
Number of enforced relationships: 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query: 32
Number of fields in a recordset: 255
Recordset size: 1 gigabyte
Sort limit: 255 characters in one or more fields
Number of levels of nested queries: 50
Number of characters in a cell in the query design grid: 1,024
Number of characters for a parameter in a parameter query: 255
Number of ANDs in a WHERE or HAVING clause: 99
Number of characters in an SQL statement: approximately 64,000
Brettski
2008-10-09 18:31:49