Limit on Access where clause?

[Guest]

Is there a resource limit on how many items can be put in the Where clause of
a select command that retrieves rows to a OleDbDataAdapter dataset? Can it
be unlimited if I change some settings?

I bumped my head when I got to ...
SELECT col1 col2
FROM table
Where this0 AND that0 AND it0
OR this1 AND that1 AND it1
...
OR this97 AND that 97 AND it97

Thanks!

Bob

 

[Michel Posseth [MCP]]

The resource limit is the maximum size that the string datatype can hold
wich is on a windows XP system the same amount of characters as the integer
max size ofcourse if your computer is capable of mapping the needed memory
.

If you were asking me if this is good coding practice :-| well

In my opinion you would bether design the project structure in such a way
that query`s like that are not necesary
or if it is really necesary to retrieve data in a way where you must check
for multiple variabel values , you could go for a where in query ( this is
suported by Access )

example

SELECT * FROM Shops
WHERE City In ("Paris", "London", "Amsterdam");

SELECT * FROM Shops
WHERE City = "Paris" OR City = "London"
OR City = "Amsterdam";

HTH

Michel

 

[Guest]

I saw a thread on this group in the last week or so that indicated that
Access had a limit of 99 fields in the where clause.

 

[Michel Posseth [MCP]]

hmmm ,,,,,, funny


Microsoft Access query specifications Attribute Maximum
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 1024
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


however the oledb command object does not point to anny limitations in the
documentation other as the max string size , i also wonder what would
happen with the "WHERE IN " QUERY if you would go beyond the 99 limit

Michel

 

[Guest]

Hi, the original thread I saw was dated 11/8 and titled "DataTable on
MSAccess Table with 100+ fields". Seems that CommandBuilder and (I would
assume) TableAdapter both create where clauses matching every field on the
table to handle concurancy issues. The original thread simply stated that
the update failed. Guess this means that you will have to write your own
update sql for Access tables with greater then 99 fields.
--
Terry

hmmm ,,,,,, funny


Microsoft Access query specifications Attribute Maximum
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 1024
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


however the oledb command object does not point to anny limitations in the
documentation other as the max string size , i also wonder what would
happen with the "WHERE IN " QUERY if you would go beyond the 99 limit

Michel

 

[Imran Shaik]

You gotta be kidding me

When you do write that query when you do manage to that is be kind enough to
post it so we can have a look. LOL