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

Question

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

Answer 1

If you're appending the UNION of the 12 worksheets, why not just use your loop to append each worksheet one at a time? You obviously don't need to de-dupe (you're using UNION ALL), so that seems like it would be an obvious solution to avoiding whatever problem is happening with your UNION'ed recordset.