Solved

using createquerydef for queries longer than 255

Posted on 2009-03-30
10
270 Views
Last Modified: 2012-08-14
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..
0
Comment
Question by:willis1970
  • 5
  • 4
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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
 

Author Comment

by:willis1970
Comment Utility
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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
 

Author Comment

by:willis1970
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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
 

Author Comment

by:willis1970
Comment Utility
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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
 

Author Comment

by:willis1970
Comment Utility
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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now