SQL 255 Limit workarround??

[Vacation's Over]

I am using Excel to query an Access mdb, The Ui allows user to choose a
number of parameters to the query. (field1 =, field2 < ....)

Problem: It would be easy for the resulting SQL generated by my VBA to
exceed 255 charater SQL limit.

What is the best way to segment a query / work around the SQL limit?

i can get the if/then and len(mySQL) to know when there is trouble but I am
not sure how to "futher Query" a recordset. Before I start I thought I'd ask??

Otherwise I was going to try by creating a new (temporary) table with first
SQL pass ,then use three SQL passes to refine (delete unwanted records) and
return the table. (in my case 3 passes should do it).

This just seems inefficient so ...any ideas???

 

[Tim Ferguson]

Problem: It would be easy for the resulting SQL generated by my VBA to
exceed 255 charater SQL limit.

What is the best way to segment a query / work around the SQL limit?

There is no such limit: or if there is, I think it's around the 32K.

You are using db.Execute to run the sql statement, aren't you, rather than
DoCmd.RunSQL?

Hope that helps


Tim F

 

[Vacation's Over]

Thanks

Not sure where i picked up that rumor, you made my Friday!

 

[George Nicholson]

Access Help says there is a 255 character limit on "any parameter in a
Parameter query", a 255 field limit for a Recordset and a Sort limit of "255
characters in one or more fields." Doesn't say there is a limit to the
length of a query as a whole.

HTH,

 

[Vacation's Over]

Thanks George-

Thought I remembered something about 255.

By the way was there an "easy" way to get your post from Access help?

Love the discussion group and hate to post when RYDM is the answer.

 

['69 Camaro]

Hi.

By the way was there an "easy" way to get your post from Access help?

Yes. Type "specifications" in Access Help to view the various limits on
queries, tables, forms, recordsets, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[George Nicholson]

FWIW, typing "Limitations" gets you to the same place: the "Access
Specifications" entry.

Depending on why you are looking in Help to begin with, it might be easier
to remember!