3,000+ character SQL string - Setting for this property is too long

  • Thread starter Thread starter Bill R via AccessMonster.com
  • Start date Start date
B

Bill R via AccessMonster.com

The query itself runs perfectly and returns the right records. Any
suggestions as to how to set a form.recordsource and cmb.rowsource to this
SQL string would be appreciated.

Thanks

Bill
 
Bill said:
The query itself runs perfectly and returns the right records. Any
suggestions as to how to set a form.recordsource and cmb.rowsource to this
SQL string would be appreciated.
Click to expand...


Use shorter table and field names. At least, use very short
aliases for table names.

Eliminate all uneeded fields.

As an alternative, instead of setting the record source
property to an SQL statement, set a saved query's SQL
property and use the query's name for the record source.
 
Please excuse the dupe post in this forum:

I followed your advice.

The last 2 lines of my code are:

db.QueryDefs("qryFrmPlanning").SQL = strSQL
frm.Requery

"frm" is a form based on the query "qryFrmPlanning". However, in order to see
the newly defined recordset, I have to open the form properties (not easy in
form view), hit the build button for the recordsource, run the query in the
grid, close the query, and move out of the recordsource property. Then the
form actually requeries and displays the correct records. Obviously, that's
not a usable solution.

Inasmuch as I know my code is returning a valid recordset, and the form is
set to the correct query, why doesn't it automatically update at the frm.
Requery command?

Thanks,

Bill
 
We are to assume that following does not work...right?

me.ReocrdSource = strSQL

First, are you actually changing the sql all the time, or just the "where"
(parameters) ? I would use the "filter" property of the form in this case,
and then you ONLY have to change the criteria...and not the whole sql each
time. (so, this would be my first choice).

However,:
db.QueryDefs("qryFrmPlanning").SQL = strSQL
frm.Requery
Click to expand...

You need to "force" the sql to be saved. Try:

db.QueryDefs.Refresh
frm.Requery
 
Setting the recordsource doesn't work because the length of the string is
over the 2,048(?) character limit. The Select statement is only about 200
characters, so setting the filter isn't going to work either. That's why I'm
stuck with changing the querydef of the underlying query as my only out.
I tried adding the refresh line:

db.QueryDefs("qryFrmPlanning").SQL = strSQL
db.QueryDefs.Refresh
frm.Requery

But it still doesn't refresh and show the new recordset.
 
db.QueryDefs("qryFrmPlanning").SQL = strSQL
db.QueryDefs.Refresh
frm.Requery
Click to expand...

Ok, never done the above, and as you point out ...it don't work...

However, you can just go:

db.QueryDefs("qryFrmPlanning").SQL = strSQL
db.QueryDefs.Refresh
me.RecordSource = "qryFrmPlanning"

The above seems to do the trick.....
 
Gee, I would have expected that to work

Just a guess now, but you might try using:

db.QueryDefs("qryFrmPlanning").SQL = strSQL
db.QueryDefs.Refresh
frm.RecordSource = "qryFrmPlanning"
 
Bill R via AccessMonster.com said:
The query itself runs perfectly and returns the right records. Any
suggestions as to how to set a form.recordsource and cmb.rowsource to this
SQL string would be appreciated.

Thanks

Bill
Click to expand...

I'm intrigued. What kind of SQL statement uses 3000 characters? What does
it look like?
 
I'm intrigued. What kind of SQL statement uses 3000 characters? What does
it look like?
Click to expand...

Just guessing:

- either a (intentionally or inadvertantly) denormalized wide-flat
table with lots of fields, perhaps with long fieldnames
- or, an IN() clause with a whole bunch of criteria

I'm curious too!

John W. Vinson[MVP]
 
John,

You guessed it. The query is the result of a filter form that permits the
user to select a number of criteria to filter records. One of those criteria
requires an IN(SELECT statement. The other criteria may result in as many as
16 or more lines of criteria, each of which would than have to include the IN
(SELECT statement. What a nightmare! But I made it work.

Thanks,

Bill
 
You mean that you have the IN criteria repeated in there?

Access doesn't necessarily use the most efficient method when creating the
SQL for queries. You should be able to rewrite it a touch so that instead
of using SQL like

(field1 IN (1,2) AND field2='a') OR (field1 IN (1,2) AND field3='b')

you replace it with

field1 IN (1,2) AND (field2='a' OR field3='b')
 
....or, another way of doing it... just turn your SQL into a query and
reference that instead.

dim db as database
set db=currentdb
dim qdf as querydef
on error resume next
docmd.deleteobject acquery, "whatever"
on error goto 0
set qdf=db.createquerydef("whatever", sql)
set db=nothing

Not wildly elegant (so I prefer my first idea) but is probably easier.


Rob Oldfield said:
You mean that you have the IN criteria repeated in there?

Access doesn't necessarily use the most efficient method when creating the
SQL for queries. You should be able to rewrite it a touch so that instead
of using SQL like

(field1 IN (1,2) AND field2='a') OR (field1 IN (1,2) AND field3='b')

you replace it with

field1 IN (1,2) AND (field2='a' OR field3='b')


Bill R via AccessMonster.com said:
John,

You guessed it. The query is the result of a filter form that permits the
user to select a number of criteria to filter records. One of those criteria
requires an IN(SELECT statement. The other criteria may result in as
Click to expand...
many
as
16 or more lines of criteria, each of which would than have to include
Click to expand...
the
Click to expand...
 
Thanks for you suggestions. I just posted an example of the query generated
by my filter form under the thread "Very Complex Query". Maybe taking a look
at that might suggest something to you.

Bill

Rob said:
...or, another way of doing it... just turn your SQL into a query and
reference that instead.

dim db as database
set db=currentdb
dim qdf as querydef
on error resume next
docmd.deleteobject acquery, "whatever"
on error goto 0
set qdf=db.createquerydef("whatever", sql)
set db=nothing

Not wildly elegant (so I prefer my first idea) but is probably easier.
You mean that you have the IN criteria repeated in there?
Click to expand...
[quoted text clipped - 36 lines]
Click to expand...
 
Back
Top