Improve company productivity with a Business Account.Sign Up

x
?
Solved

using createquerydef for queries longer than 255

Posted on 2009-03-30
10
Medium Priority
?
324 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 93

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?

Regards,

Patrick
0
 
LVL 23

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



Kelvin
0
 

Author Comment

by:willis1970
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.
LineBreakInScript.txt
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 23

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


Kelvin
0
 

Author Comment

by:willis1970
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?

Thanks,
0
 
LVL 23

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.


Kelvin

0
 

Author Comment

by:willis1970
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!

Willis
0
 
LVL 23

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.
0
 

Author Comment

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

Accepted Solution

by:
Kelvin Sparks earned 2000 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.


Kelvin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

595 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