using createquerydef for queries longer than 255

I am using access 2007 to construct an SQL query, but I need to do this in code.  I have several strings (each <= 255 bytes that I attempted to concatenate as below:
       Set qdf = dbs.CreateQueryDef("qryTest", str1 & str2 & str3 &str4)
apparently that doesn't work.  Does anyone know how to write an Access 2007 query where the query is longer than 255 bytes..
willis1970Asked:
Who is Participating?
 
Kelvin SparksCommented:
Go and create a query named qryTest or anything else (just use it's name in the code instead of qryTest. Use some simple SQL such as SELECT 8 FROM OneOfYourTables and save it.

Than run that code - the query has to exist before the code is run. The code will destroy the existing SQL (SELECT *.....) and replace it with your SQL statement.


Kelvin
0
 
Patrick MatthewsCommented:
Hello willis1970,

1) If you try putting the resulting SQL statement into Access directly, will it work?

2) Are you sure you are putting in spaces as needed when you concatenate the strings?

Regards,

Patrick
0
 
Kelvin SparksCommented:
I am not aware of a 255 byte limitation. That may be a single line limit when creating the SQL String

I use the following

Dim sSQL as string

sSQL = "some text "
sSQL = sSQL & "some note text "

and repeat the style for line two for as many lines as I need. My understanding is that 64,000 characters can form an SQL string

 then finish with

Set qdf = db.CreatQueryDef("qrytTest", sSQL)

If you use that line, you cannot repeat it as it is permanently created
I use

Set qdf = db.CreatQueryDef("",sSQL) as there is no permanent query left.

If yo need a permanent one, create a simple query that you can then replace the SQL and use

Set qdf - db.QueryDefs("qryTest")
qdf.SQL = sSQL



Kelvin
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
willis1970Author Commented:
Hi Kelvin,
Your code does allow me to build a very long SQL statement (3110 or so bytes), and while it does read to me as a valid query, there are some line breaks that cause SQL to interpret as errors.  I searched the string for  ASC 10, and ASC 13, and found none.  I have attached a text file with a portion of the script, and when I past the SQL string from the debug window into the View SQL in Access, these line breaks appear, and "break" the script, i.e., are interpreted as syntax errors and such.
I have attached a text file that shows this line break.  I can correct the SQL script by pressing the del key after the character before the line break, and then SQL is ok with it.
LineBreakInScript.txt
0
 
Kelvin SparksCommented:
Yes , if you paste the resulant SQL into the Query builkder and view, yest there are line breaks, which you manually have to join.

We you execute from code however (after using the viewer to degug etc...) the line breaks are ignored. Ig you sa=ve the query for reuse, yes you have that problem which is why I tend to use the CreateQueryDef("",sSQL) style


Kelvin
0
 
willis1970Author Commented:
Thanks, Kelvin.  I guess I will try the temp query as you suggest just to see if I can work around this probem, but I really do need to be able to save the query that I am building, and the 'where' clause is built based on filling out fields on a from, and it could be different depending on the information I am trying to pull from the file.
Does anyone else know why the SQL view causes these line breaks which have to be manually removed?

Thanks,
0
 
Kelvin SparksCommented:
Did you try my alternative. Create & save a simple query.

Then use the

Set qdf - db.QueryDefs("qryTest")
qdf.SQL = sSQL

where qrtTest is the name of the simple query. That query can have any SQL when you create it as this overwrites the SQL

I'm very aware of the line break in the CreateQueryDef, but can't recall it being an issue in the example above.


Kelvin

0
 
willis1970Author Commented:
Thanks, Kelvin, I'm going to try this right now.

By the way, My wife asked me if you sit at your computer all the time.... like me!  LOL!

Willis
0
 
Kelvin SparksCommented:
Around 15 hours a day (self employed!!)

Also being in New Zealand, the time difference means while it is late for you, it's mid afternoon (3.50 p,m,) on Wednesday here.
0
 
willis1970Author Commented:
Makes sense to me.  I'm also self-employed!
Tried, but getting an error:
Set qdf = dbs.CreateQueryDef("", sSql) to create the temp query, which seems to work fine.
But,                
                Set qdf = dbs.QueryDefs("qryTest") - give me error 3265, Item not found in collection.
                qdf.SQL = sSql
Probably something simple...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.