Long VBA generated queries

[pyth0n]

Hi

I have a section of VBA code that generates my SQL query, but when I try to use the query it errors.

I have seen found that the query is being split into 1024 character blocks by Access and therefore not providing correct syntax for the query to work.

Is there anything that I can do to work around this?

The SQL query is generated using the code below:
[tt]
For Each x In table_list
sql = "SELECT DISTINCT Product FROM " & x(0) & ";"
query_name = "query_" & x(0)
Set qry = db.CreateQueryDef(query_name, sql)
Set rs = qry.OpenRecordset
qry.Close

datalist_query_sql = "SELECT [CHTest1].Area, [CHTest1].Employee, [CHTest1].[LM ID Number], [CHTest1].Name, [CHTest1].Address, [CHTest1].[Address 2], [CHTest1].Town, [CHTest1].Postcode, [CHTest1].[Phone Number], [CHTest1].Category, [CHTest1].Profile, [CHTest1].Type, [CHTest1].Owner, [CHTest1].Operator, [CHTest1].MFT, [CHTest1].Fascia, [CHTest1].MF, [CHTest1].[Serve Size], [CHTest1].[Free Pour], [CHTest2].[SeventyMl], [CHTest1].LastSurveyDate,"

While Not rs.EOF
str_replace = Trim(Replace(rs.Fields(0).Value, ".", "_"))
datalist_query_sql = datalist_query_sql & " [" & x(0) & "_crosstab].[" & str_replace & _
"] AS [" & x(1) & str_replace & "],"
rs.MoveNext
Wend
Next
[/tt]

I would really appreciate any help

Thank

 

[Crowley16]

declare your strings as strings...

--------------------
Procrastinate Now!

 

[pyth0n]

I have only posted the small amount of code that causes the problem. All variables are declared to their proper types and the module uses Option Explicit.

If you need, I can post the entire code for that sub.

 

[lespaul]

have you tried an alias to shorten the SQL?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[pyth0n]

Lespaul

No I haven't tried to use an alias to shorten the SQL, so I will give that a go.

Will let you know how I get on.

Thanks