Length of the SQL that can be entered in a passthrough query

Good morning Experts,

I frequently use pass-through queries in MS Access. Unfortunately, it seems that there is a finite length to the SQL one can enter. Is there a way I can extend the length of the SQL I can enter or do I need to switch to a different application to query an database using ODBC such as SQL Assistant (formerly know as QueryMan).

Any insight you can provide is greatly appreciated.

Thank you,
TylerDerdenAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
I get the impression that using a report with linked subreports (e.g. by the "part of 3 larger groupings" as you call it) will open the possibility to use two queries instead of one "giga" query.

Did you consider this ?

Nic;o)
0
 
johnson00Commented:
How do you have the SQL tables tied into Access now?  Are you using ODBC linked tables?
0
 
rockiroadsCommented:
Not sure what the size is, I remember hitting it one time when I migrated an app to SQL Server.

The solution I used was to create stored procedures in the host DB then call that. Can you in your DB, create stored procedures?



0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nico5038Commented:
When you first build a string in Access VBA than I guess you´re limited to approx 6,000 characters.
Best to use the querydef object and place it there as that´s much larger.
Code sample:

dim dq as DAO.querydef

set qd = currentdb.querydefs("name of your passthrough query")
qd.SQL = "Select ...."
qd.SQL = qd.SQL & " WHERE ...."
qd.SQL = qd.SQL & " AND ...."
etc.

after the last assign statement the query can be executed as the SQL is stored "immediately"

Nic;o)
0
 
TylerDerdenAuthor Commented:

The query is written using SQL not the design grid. The ODBC connection string is stored in the properties.
0
 
nico5038Commented:
Hi Rocky, rocking all over EE again :-)

Nic;o)
(Sorry for the off-topic TylerDerden, we´re "old buddies" :-)
0
 
TylerDerdenAuthor Commented:

rockiroads, No unfortunately I do not have permission to create stored procedures.

nico5038, I'm not using the querydef object but it does seem that I am limited to a certain number of characters. No worries about the off topic.

Sounds like a may need to use another application. Many people in my organization use Cognos Impromptu but I like to write my own SQL. More flexibility that way. Some people are using SQL Assistant. Can anyone make a suggestion on a good application I can use to connect to a database using ODBC, write my own SQL and not be capped on the character length?
0
 
rockiroadsConnect With a Mentor Commented:
Hey Nico, how ya doing. I see your writing books now. What next :)

Tyler, what if you cache the data locally?

Is it possible to split your queries into two queries say, then run both queries, both of which insert into a local table. Then whatever form your using to get that data, use local tables instead

0
 
nico5038Commented:
In general my queries are rather small, but I use a lot of subqueries and sometimes a temp table to limit the number of rows.
What´s the big space consumer in your "trouble" query. Are you using e.g. an elaborate IN() clause ?

Nic;o)
(Rocky, next is a Dutch A2007 book next year :-)
0
 
TylerDerdenAuthor Commented:

Yes, rockiroads that is currently an option I am using. However, it is very time consuming to pull all the data locally and finish the assembly. I’d rather run the whole thing on the server side. Fast and clean.

Nico5038, I am putting together a report that shows all the various characteristics of a customer. This is just one part of a four queries. Lets call this what the customer uses. This part has 29 categories that are part of 3 larger groupings. So I have 87 columns (not counting the customer specific fields such as customer_id). Each of theses categories requires a case statement to calculate the proper category totals.

To make things more interesting the database is split into 4 regions so I need to union the SQL across all regions.

Think of this as one logical table showing usage that will be joined to three other logical tables to create an all in one view of a customer’s characteristics. If that makes sense?


0
 
rockiroadsCommented:
Nico, your supposed to be an Oracle guru now.

ODBC size limit might be 32766 characters?

Perhaps its not ODBC, but the ODBC driver that you are using is limiting it
0
 
TylerDerdenAuthor Commented:

rockiroads,

Interesting, comment on the ODBC driver. I didn't know the driver could have a character limit.
It's actually Teradata not Oracle, if that helps.
0
 
TylerDerdenAuthor Commented:

The error I am getting is not related to the ODBC driver. As mentioned in my previous comments the database is split into 4 areas. So I need to union the SQL. Straight forward. I write the SQL for one area then use a union all clause and copy and past the code. Problem is when I get to copy and past the third of four regions MS Access populates the an alert box advising me the text is to long to be edited.

So I’m not getting the error when running the query but rather when copy and pasting in the SQL code in the passthrough query.
0
 
rockiroadsCommented:
There shouldnt be, but I was just thinking that the driver created may of put some limits in, its only a possibility.
0
 
rockiroadsCommented:
Try running that query using MSQueryEditor (part of Office), I think its called MSQRY32.EXE
you can run your union query and select odbc driver
0
 
TylerDerdenAuthor Commented:
rockiroads, Sorry but I can’t find the MSQueryEditor or MSQRY32.EXE. I search the file names, office help but I can still look around.

Nico5038, Yes, besides pulling the data locally like rockiroads suggested I have run this as a series of separate queries and it does work.

I guess I just like the idea of an all in one “giga” query as you call it.

Thanks you for all your help. I’m going to close out the question and split the points since both of you have been so helpful and it seems like we’ve gone as far as we can.
0
 
nico5038Commented:
For me "small is beatifull" as it's easier to test the individual queries :-)

Success with the application !

Nic;o)
0
 
rockiroadsCommented:
Normal path is ususally where other progs are  e.g. C:\Program Files\Microsoft Office\Office10\MSQRY32.EXE.

Perhaps you dont have it installed. I know its an option when installing Office.

If u got the Office CD, see if you can install it, it may be worth a shot. It should run the sql native to the DB u use.

It's good for correcting queries I guess. No fancy design editor though, just sql editing
if u know your sql, which it seems u do, then this usage should be a breeeze

0
All Courses

From novice to tech pro — start learning today.