Limit of 255 characters of connections string

[spence27]

i have been seting up a front end to work of access using ado and i have hit a brick wall. My font end builds a SQL string using the limits and grouping useres select. Once this SQl string is pulled together i send it against the db for the results.

In simple testing everything was fine however once i doing some realistic testing things stopped working, and it looks to be down to the length of the query 600 characters or more.

Is there a way to pass a long sql string to access via ADO ?

Can anyone think of a way around this other than stored procedures (way to many options to predict each and every one)

I really would appreciate any help on this

 

[Remou]

You should post sample SQL, it attracts the attention of the people here in a way that writing does not.

 

[spence27]

Sorry for the delay in getting back (personal stuff etc..)

Below is an example of the sql string. The Select clause can have any number of elements from 1 upwards this is the sql generated that needs to be sent to an access database but there looks to be a limit of 255 characters. So I need to know how to get past this limit. I have found reference to using arrays but can't find any examples.

If anyone could help

Thanks#Spence

Select [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location] As [Location], [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] As [WEEKLY_NUM], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_TOTAL_VAS_SCORE]) As [dim_ft_TOTAL_VAS_SCORE], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_Staffed Time]) As [dim_ft_Staffed Time], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_Aux1]) As [dim_ft_Aux1], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_Outbound Calls]) As [dim_ft_Outbound Calls] From ADDING_VALUE_DATA_WEEKLY Group By [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location], [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] Having [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] between 200632 AND 200633 AND [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location] in ('Leed') Order By [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] DESC, [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location] ASC;

 

[Remou]

Using the alias 'a' for ADDING_VALUE_DATA_WEEKLY may help:

[tt]Select a.[dim_gr_Location] As [Location],
a.[dim_dt_WEEKLY_NUM] As [WEEKLY_NUM],
SUM(a.[dim_ft_TOTAL_VAS_SCORE]) As [dim_ft_TOTAL_VAS_SCORE],
SUM(a.[dim_ft_Staffed Time]) As [dim_ft_Staffed Time],
SUM(a.[dim_ft_Aux1]) As [dim_ft_Aux1],
SUM(a.[dim_ft_Outbound Calls]) As [dim_ft_Outbound Calls]
From ADDING_VALUE_DATA_WEEKLY a
Group By a.[dim_gr_Location], a.[dim_dt_WEEKLY_NUM]
Having (a.[dim_dt_WEEKLY_NUM] between 200632 AND 200633)
AND a.[dim_gr_Location] in ('Leed')
Order By a.[dim_dt_WEEKLY_NUM] DESC, a.[dim_gr_Location] ASC;[/tt]

 

[spence27]

I understand what you are saying and true it makes sence but i cant changes the field names and the user can choose to as many fields as he or she wants, The code i posted is just a very small sample in general use i expect 10 options minimum to be selected.

I have found reference to a kind of solution at MS

http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch15.mspx

and oj J-walk here

http://j-walk.com/ss/excel/faqs/xl95faq3.htm

BUt i cant see how to pass the array through to the database. If anyone could help this could solve my problem - Thanks

 

[Golom]

Actually, I doubt that a limit to the length to the SQL string is the problem. First, I just checked the ADO documentation and there is no mention of a limitation on the length of the SQL statement. Second, I just did a quick check of some of my own code and I have SQL statements that are in excess of 2,000 characters and they run fine through ADO.

When you say it stops working ... what happens exactly? Are there errors? Does the system freeze up? Are the results incorrect?

 

[lespaul]

but i cant changes the field names

Remou didn't suggest that you change the field names, he suggested that you use an alias for the table name so that the string you build is much shorter:

you have:

[tt]Select [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location] As [Location], [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] As [WEEKLY_NUM], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_TOTAL_VAS_SCORE]) As [dim_ft_TOTAL_VAS_SCORE], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_Staffed Time]) As [dim_ft_Staffed Time], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_Aux1]) As [dim_ft_Aux1], SUM([ADDING_VALUE_DATA_WEEKLY].[dim_ft_Outbound Calls]) As [dim_ft_Outbound Calls] From ADDING_VALUE_DATA_WEEKLY Group By [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location], [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] Having [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] between 200632 AND 200633 AND [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location] in ('Leed') Order By [ADDING_VALUE_DATA_WEEKLY].[dim_dt_WEEKLY_NUM] DESC, [ADDING_VALUE_DATA_WEEKLY].[dim_gr_Location] ASC;[/tt]

Remou's is exactly the same, but uses fewer charaters by replacing all the [ADDING_VALUE_DATA_WEEKLY] with a:
[tt]
Select a.[dim_gr_Location] As [Location],
a.[dim_dt_WEEKLY_NUM] As [WEEKLY_NUM],
SUM(a.[dim_ft_TOTAL_VAS_SCORE]) As [dim_ft_TOTAL_VAS_SCORE],
SUM(a.[dim_ft_Staffed Time]) As [dim_ft_Staffed Time],
SUM(a.[dim_ft_Aux1]) As [dim_ft_Aux1],
SUM(a.[dim_ft_Outbound Calls]) As [dim_ft_Outbound Calls]
From ADDING_VALUE_DATA_WEEKLY a
Group By a.[dim_gr_Location], a.[dim_dt_WEEKLY_NUM]
Having (a.[dim_dt_WEEKLY_NUM] between 200632 AND 200633)
AND a.[dim_gr_Location] in ('Leed')
Order By a.[dim_dt_WEEKLY_NUM] DESC, a.[dim_gr_Location] ASC;
[/tt]

it also makes reading a query a LOT easier!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

It looks like all the links you have found regarding your issue point to limitations from within Excel. Are you using Excel or just Access?

Apparently you are dynamically building a SQL string. Can you share this code?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]