VBA SLQ string longer than 256 chars

[SallyJenkins]

I am using MS Access 2012 (MDB). My understanding is that my sqlstatement can be as long as 32,000 + characters. When in debug mode I can see the sql string and when longer the 256 chars it is cut off. Is there some secret switch I need to set to accept longer strings? Also I was getting an error message (sorry can't remember what it was).
This code is just a sample so you can see how I write the sql code. I had to remove the table name from this so it would be under 256 chars.

SQL = "SELECT GrantAmt, GrantDate, Action, FundId, payee_name INTO EndowGrants " & _
"FROM FOUND_sql_Grants " & _
"WHERE (GrantAmt > 0) AND (Action = 'A') AND (FundId > '19999' And FundId < '30000') " & _
"AND (GrantDate between #" & dtBeginFyDate & "# and #" & dtEndDate & "#) "
DoCmd.RunSQL SQL

 

[boblarson]

In order to see it for long SQL Strings, you can try using

Debug.Print SQL

You should NOT have a variable named SQL though. Try using strSQL or something. SQL is an Access Reserved Word.

If the Debug.Print cuts off, then I will usually write it out to a text file:

Dim i As Integer
 
i = FreeFile
 
Open "C:\Temp\MySQLString.txt" For Output As #i
 
Print #i, strSQL
 
Close #i

And then look at it there.

 

[SallyJenkins]

Thanks for the quick reply. I will try the debug and i WILL change the variable name.

 

[mdlueck]

Alternatively I would suggest moving the query into ADO objects. Examples of INSERT / UPDATE / SELECT below.

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Using this type of query, you may encapsulate the values you need to dynamically plug into the query inside the Parameters objects, avoid the hassle of quoting problems / being able to pass special characters through to the query, and so on.

 

[SallyJenkins]

Super Duper. I will give this a go later on today or tomorrow.
Thank you sor the suggestions.