Query string length limit in vba?

Question

I am trying to combine multiple audit tables, and then filter the results into an excel sheet. The Union All and parameters make the query in excess of 1200 characters. It appears the string is truncated when running this. What recommendations can anyone make. I have no control over the database structure and am only reading foxpro free tables.

I am permitted table creation but can write into the excel sheet connecting to the datasource

An update to this is that I was able to extend the query string beyond 1800 characters and get data back. So I conclude I had syntax errors and the apparent truncation I mentioned was a failure in my string development in the scripting.

I posted an example of my connection code and that answer has disappeared, so I am not sure how to designate this a closed issue. There is NOT an apparent string length limit and that was my initial concern. Thanks for the contributions.

Answer 1

Can you not create a recordset with a superset of the desired data, then run a second query against that to do the final filtering?

Alternatively, and I'm not in any way familiar with FoxPro or your database privileges, could you create a stored procedure in the database and then just pass parameters to it?

Edit to add: I presume you're already giving your tables short names? (e.g. ...FROM Extremely_Long_Table_Name a WHERE...) If not, this could probably save you a pile of characters.

Answer 2

If it's a Union Query, as your question suggests, then you could break it up into each sub-query and run them one after the other.

I can hardly believe that the limit on your query string would be so small though. Where is your query being truncated? By VBA? by FoxPro? Can you copy/paste your generated query directly into a database client and see if it runs correctly?

Answer 3

After rewriting the query a number of ways, I have gotten the string length for the query beyond 1800 characters successfully. I have to conclude that my previous error was syntax although the vba error didn't give many clues.

The query string is built using controls on an excel sheet and accumulate in SQLstr. Apparent truncation was an error in my scripting the string creation. Once that was resolved then this:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
    "ODBC;DSN=myDB;Description=myDB;DATABASE=myDB;Trusted_Connection=YES"), Destination:=Range("A2"))
    .CommandText = SQLstr
    .Name = "Query from myDB"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells 
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

Thanks for the help because the responses indicating that it seemed unlikely to be a string length limit made me look at syntax instead. I am still curious if there are query string size limits, if anyone knows.