255 limit in SQL in code 
Author Message
 255 limit in SQL in code

I have a function (from this newsgroup) that returns the
sql of a query so I can alter it in code.  The function is:

Public Function GetTheSQL(TheQuery As String) As String
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Set db = CurrentDb
   Set qd = db.QueryDefs(TheQuery)
   GetTheSQL = qd.SQL
End Function

It usually works fine, but today it caused an error.  The
function was only returning the first 255 characters of
the sql.  After rebooting the computer, it worked fine
again.  Is this something I can prevent from happening
again?



Tue, 10 Aug 2004 08:21:19 GMT  
 255 limit in SQL in code
I don't have a firm answer.  Seems like a strange fluke to
me.  However, you could simply replace all that with this
single line and get the same result:

Public Function GetTheSQL(strQueryName As String) As String
   GetTheSQL = CurrentDb.QueryDefs(strQueryName).SQL
End Function

- Tim

Quote:
>-----Original Message-----
>I have a function (from this newsgroup) that returns the
>sql of a query so I can alter it in code.  The function
is:

>Public Function GetTheSQL(TheQuery As String) As String
>   Dim db As DAO.Database
>   Dim qd As DAO.QueryDef
>   Set db = CurrentDb
>   Set qd = db.QueryDefs(TheQuery)
>   GetTheSQL = qd.SQL
>End Function

>It usually works fine, but today it caused an error.  The
>function was only returning the first 255 characters of
>the sql.  After rebooting the computer, it worked fine
>again.  Is this something I can prevent from happening
>again?
>.



Tue, 10 Aug 2004 09:23:46 GMT  
 255 limit in SQL in code
The function is fine, though I'd recommend adding:
    Set qd = Nothing
    Set db = Nothing
It's possible that a memory leak could contribute to the problem, though not
the symptoms you describe.

Were you processing the output of the function in any way, e.g. passing it
into UCase(), or into a text box with ">" in the Format property? These are
known to cut the string to 255 chars.

The fact that a reboot fixed the issue would imply that something had
crashed (e.g. a dll?) or that a memory problem had developed.

--
Allen Browne, Microsoft MVP (Most Valuable Professional)
Perth, Western Australia
Tips for MS Access users at:
    http://users.bigpond.net.au/abrowne1


Quote:
> I have a function (from this newsgroup) that returns the
> sql of a query so I can alter it in code.  The function is:

> Public Function GetTheSQL(TheQuery As String) As String
>    Dim db As DAO.Database
>    Dim qd As DAO.QueryDef
>    Set db = CurrentDb
>    Set qd = db.QueryDefs(TheQuery)
>    GetTheSQL = qd.SQL
> End Function

> It usually works fine, but today it caused an error.  The
> function was only returning the first 255 characters of
> the sql.  After rebooting the computer, it worked fine
> again.  Is this something I can prevent from happening
> again?



Wed, 11 Aug 2004 09:39:47 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. 255 SQL character limit

2. 255 character limit on SQL statements

3. Crystal VARCHAR(255) limit with SQL Server

4. 255 SQL character limit

5. 255 character limit on SQL statements

6. Winsock INET_ADDR returns INADDR_NONE for 255.255.255.255

7. 255 char limit with FormField from VBA?

8. RecordSource 255 char limit workaround?

9. Info on 255 field limit

10. TabStrip has a 255 limit ?!?

11. VB5: 255 limit on forms

12. Access 255 record limit, instrumentation database?

 

 
Powered by phpBB® Forum Software