SQL UNION ALL problem after using UNION ALL more than 10 times

Posted by VBGKM on Stack Overflow See other posts from Stack Overflow or by VBGKM
Published on 2010-03-05T17:22:51Z Indexed on 2010/04/25 5:33 UTC
Read the original article Hit count: 349

Filed under:
|
|
|
|

I'm getting a formatting problem if I use more than 10 UNION ALL statements in my VBA Code.

If I use 10 or less everything works great.

What I'm trying to do is combine 12 worksheets (Excel 2007).

I have a numerical column called SC that turns into string and date if I have more than 10 UNION ALL. If I try to use ROUND with more than 10 UNION ALL my last selection will change all the records by one unit.

I'm using Microsoft.ACE.OLEDB.12.0 as my provider and my connection string has worked for several things in my code so far.

Is there any limit for UNION ALL statements when using OLEDB?

Here is my code.

Dim StrOr As String
Dim i As Variant
Dim Cnt As ADODB.Connection
Dim Rs As ADODB.Recordset

For i = 1 To 12
    StrOr = StrOr & " " & "SELECT SC FROM [" & MonthName(i, True) & "$" & "] UNION ALL"
Next

StrOr = Left(StrOr, Len(StrOr) - 9) & ";"

Call GetADOCnt

Call ADORs

© Stack Overflow or respective owner

Related posts about sql

Related posts about vba