using createquerydef for queries longer than 255

Posted on 2009-03-30
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..
Question by:willis1970
  • 5
  • 4
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24025120
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?


LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24025129
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


Author Comment

ID: 24035511
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.
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24035523
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


Author Comment

ID: 24035584
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?

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24035605
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.



Author Comment

ID: 24035630
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!

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24035639
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.

Author Comment

ID: 24035707
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.
                Set qdf = dbs.QueryDefs("qryTest") - give me error 3265, Item not found in collection.
                qdf.SQL = sSql
Probably something simple...
LVL 22

Accepted Solution

Kelvin Sparks earned 500 total points
ID: 24035722
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.


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Left Join with Tuple returning more rows 10 90
encyps queries mssql 15 38
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 72
SSRS troubles 4 20
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

867 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

18 Experts available now in Live!

Get 1:1 Help Now