255 char limit on strings

[TJones8]

I'm using VBA and trying to pass quite a long SQL query (>255 chars in length). But of cause when i try to pass this to DoCmd.RunSQL strSQL it of cause cannot continue since it only has a truncanated version of the complete query.
Can i insted put the statement into two strings and somehow run them together - DoCmd.RunSQL strSQL1 & strSQL2

 

[Sorrells]

TJones8,

I don't know but you can surely try what you propose. Also try with the concatenation symbol "+".

Another thought is to experiment on a short SQL statement so that any error you receive is from the method not the length of the strings.

Good luck!

Regards, Sorrells

 

[TJones8]

I know it's the string length as i added it to thw watchlist and put a breakpoint in just after it.

 

[scking]

Your problem is not in the length of the SQL string. It could be as long as you want. The problem is that the DoCmd.RunSQL limits the size of SQL strings that it handles to 255 characters. You will need to use an alternative approach.

Steve King Growth follows a healthy professional curiosity

 

[TJones8]

arrrgh
What alternitive approach would i need to use??

 

[LittleSmudge]

Well, the first approach could be post the beast and we'll take a look at it.

There are quite a lot of common coding conventions that take up a lot of character count that are not strictly necessary.

Alternatively,
Is there some section of the query that could be stored as a discrete query and called in directly?

Are there any field names that can be reduced in size in the underlying table without affecting too much reworking?

Are there any field names that can be renamed to avoid having to explicitly refer to the table qualifier?

Are there any 'As name' values that could be shortened or removed?



G LS

 

[scking]

Although my network is currently down and I can't provide the examples, there are alternatives to the DoCmd.RunSQL construct. You could use ADO and a command, setting the CommandText to your SQL statement.

Steve King Growth follows a healthy professional curiosity

 

[Guest_imported]

I am creating a database and have come to the report writing section, I have created a form that allows you to choose fields from several different tables within drop down boxes, I have created a query that has the same fields in there, the form in question has a button used to run a macro that calls that query to show the fields on a report (report by query), my problem is that I can't get the report to show any data, the macro itself will run if you type in your data, but it will not select the fields from the form and match them to the query (the selection criteria in teh query does not use the loaded form data for some reason)

Can someone please help me and give me an insight as to what I am doing wrong????

Mike

 

[LittleSmudge]

Not without seeing a (short) section of code Michael.

The key to this sort of problem is getting the 'principle' working first. ( Small No of fields that show the process ) then add more fields.

So post a cut down version ( if the whole thing is large ) because I think your problem is not so much what you're doing as how you are doing it.


G LS

 

[vesmir2]

I got a same problem some time ago.
My solution was to do it as a "two step" action/method.
A limited "first" SQL to a temporary storage and a "second"
SQL from this temporary storage for some refinement.
Can be done in just one module at once.
Works fine, no measurable delays or bottlenecks.