Solved

using createquerydef for queries longer than 255

Posted on 2009-03-30
10
283 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
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 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



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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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


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


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

Accepted Solution

by:
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.


Kelvin
0

Featured Post

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to join on ID, with prefix? 15 59
Where did System.Data.Objects go? 2 27
SharePoint 2013 Link to File on Network Drive Not Working 3 23
SQL Insert parts by customer 12 34
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn the different options available in the Backstage view in Excel 2013.
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…

809 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