SQL statement too long

[darkwraith]

I have an UPDATE statement in my application. I use the DoCmd.RunSQL method to execute it. Sometimes it happens that the string that contains the statement is longer than 255 charachers, so my command fails.
How can I overcome it?
I tried dividing the string into more substrings and concatenating them but it doesn't work all the same.
Thanks for any suggestion,
Elena

 

[EvilCabal]

Hey,
Try "CurrentProject.Connection.Execute 'SQLstring'" instead of 'DoCmd.RunSQL', I think it supports strings up to 65535 caracters.

 

[scriverb]

another way is to use Alias for your table names. Many queries that are too long happen to have table or query references that are very word and long in the number of characters. By changing the name through the Alias claus you can eliminate a lot of characters in your query

FROM [MY TABLE WITH LOTS OF CHARACTERS IN ITS NAME] as A

Now use a Find and Replace with a word processor to substitute the A for the long table reference.

Post back with questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darkwraith]

Thanks EvilCanal and Bob,
I tried using CurrentProject.Connection.Execute 'SQLstring' instead of DoCmd.RunSQL. I had to divide the string again into substrings but this time it works!
About using alias for the table names you're absolutely right, Bob, I'll use it when I have very long table names.
Thanks again,
Elena

 

[darkwraith]

contermand...I was wrong, the query still doesn't work.
I used the 'CurrentProject.Connection.Execute' but it still supports only 255 characters like 'DoCmd.RunSQL' ...
I tried with aliases but the problem is that I have a lot of fields to update in my table with very long field names and using aliases for table name is not enough...
Moreover, when I put my alias in the SQLstring into 'CurrentProject.Connection.Execute 'SQLstring'' all the long names are back in their entire lenght.
Help please...what can I do?
Elena

 

[MichaelRed]

in similar situations, I have also recommended the study ad use of a standard naming convention. to borrow the previous example

"[MY TABLE WITH LOTS OF CHARACTERS IN ITS NAME]"

might become

"[tblLngNms]"

reducing the character count by 35 per instance.

Generaly, the various object names in a db are not intended to be full descriptions of the object, but just handy reminders of the existance. "Documentation" of the full description / use is more often placed in the description PROPERTY of the object. For many dbs, the replacement of these "Long Names" can be accomplished manually without very many problems, as the dbs are usually the product of mostly beginning programmers and the db structure reflects their capability so the names are not usually 'buried' in unexpected places. In more difficult situations, there are third party tools which do a reasonably comprehensive job of going through the entire database and replacing the object names.

One such product is 'Speed Ferret'. I am NOT specifically recommending this product, as I have had no experience with it and cannot personally attest to it's effectivness. It is, however, recommended by many reviewers and it appears in several posts in these fora, along with references to other products, thus it is a good reference for use in searches for itself and other similar products, somne of which are much more modestly priced.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DanJR]

1. How many records are you updating at once - is looping through a recordset practical?

2. You could try creating a temporary query on the fly using dao or adox, execute it, then delete it(?)

Cheers
Dan

 

[darkwraith]

Thanx a lot, I changed the field names, they were actually too long!
Elena