VBA SQL Run time error

[jimmy_p84]

Hi

Im trying to pull some info from access into excel. i have a macro that does this fine but i have changed the SQL code to pull in some more advanced info and it is coming up with a run time error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect". I cant see why it is coming up with this as i have put the SQL into a cell then pasted it in a query and it works fine.

The code is

 Option Explicit

Global adoConn             As ADODB.Connection

Private Const strADOconn As String = _
"Data Source=c:Data.mdb"

Sub GetMargins()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim strName As String
Dim Lim As Worksheet
       
Set Lim = ThisWorkbook.Sheets("Limits")

          
        Call TraderDownload(Lim)
        
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
          
    
End Sub


Public Sub TraderDownload(sheet As Worksheet)
  
        Set adoConn = cn
        Call Import(fn_rstUnionAccount(), sheet, 1, 16)
        
End Sub

Private Sub Import(rst As ADODB.Recordset, sheet As Worksheet, iHeadRow As Integer, Optional iSheetColour As Integer)

    Dim vData       As Variant
    Dim wks         As Worksheet
    Dim rng         As Range
    Dim i           As Integer
    Dim V           As Variant
    Dim b           As Integer
   
   
   sheet.Range("a2:g65000").ClearContents
    
        If Not rst.EOF Then
            vData = rst.GetRows
                        
    
       
        For i = 1 To UBound(vData, 2)
               
                sheet.Cells(i + 1, 1) = vData(5, i - 1)
                sheet.Cells(i + 1, 2) = vData(2, i - 1)
                sheet.Cells(i + 1, 3) = vData(3, i - 1)
                'sheet.Cells(i + 1, 5) = vData(4, i - 1)
                sheet.Cells(i + 1, 4) = Round(vData(6, i - 1), 0)
            Next i
            
            
       End If
    Set rst = Nothing
    
End Sub


Public Function fn_rstUnionAccount() As ADODB.Recordset

    Dim strSQL                  As String
        
    strSQL = fn_ManDB() & vbCrLf & vbCrLf
    Set fn_rstUnionAccount = New ADODB.Recordset

    fn_rstUnionAccount.Open strSQL, adoConn
    
End Function


Public Function fn_ManDB() As String
    
    Dim s                       As String
   
    s = "SELECT con2.TraderAccount, con2.USER, tblLimit_ISV_Contract_Mapping.ISV, tblLimit_ISV_Contract_Mapping.ProductCode, tblLimit_ISV_Contract_Mapping.Description, con2.LIMIT" & vbCrLf
    s = s & " FROM (SELECT Con.USER, Con.Source, Con.CONTRACT, Con.LIMIT, tblISV_Traders.TraderAccount" & vbCrLf
    s = s & " FROM (SELECT 'RTS' AS Source, tblRTSLimits.Account AS USER, tblRTSLimits.product AS CONTRACT,   tblRTSLimits.[Max Long] AS LIMIT" & vbCrLf
    s = s & " FROM tblRTSLimits" & vbCrLf
    s = s & " WHERE (((tblRTSLimits.product)<>'OPTION') AND ((tblRTSLimits.[Max Long])<>0))" & vbCrLf
    s = s & " UNION ALL" & vbCrLf
    s = s & " SELECT 'TT' AS Source, tblTTLimits.Trader_ID AS USER, tblTTLimits.contract AS CONTRACT,  tblTTLimits.MaxPosition AS LIMIT" & vbCrLf
    s = s & " FROM tblTTLimits" & vbCrLf
    s = s & " WHERE (((tblttLimits.MaxPosition)<>0) AND ((tblttLimits.contract)<>'#num!'))" & vbCrLf
    s = s & " UNION ALL " & vbCrLf
    s = s & " SELECT 'STS' AS SOURCE, tblSTSLimits.Account AS USER, tblSTSLimits.Product AS CONTRACT,  tblSTSLimits.[Max Long] AS LIMIT" & vbCrLf
    s = s & " FROM tblSTSLimits) AS Con INNER JOIN tblISV_Traders ON Con.USER = tblISV_Traders.ISV_TraderAccount) AS con2 INNER JOIN tblLimit_ISV_Contract_Mapping ON (con2.CONTRACT = tblLimit_ISV_Contract_Mapping.ISV_Code) AND (con2.Source = tblLimit_ISV_Contract_Mapping.ISV)" & vbCrLf
        
    'Range("L3") = s
       fn_ManDB = s
 
End Function

Private Function cn() As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = strADOconn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open
    End With
End Function

 

[pgc01]

Hi Jimmy

I don't think you can use the word "USER" as an alias. Try replacing that alias with some other word.

 

[jimmy_p84]

Thanks PGC01 for your reply, i had just found that out from trail and error and it is all working fine now.

Thanks again for your responds

 

[pgc01]

I'm glad it's working now.

BTW, I think that if you really want to use the word "USER" as the alias, you may be able to do it if you explicitate it as a field,

Instead of

... SELECT 'TT' AS Source, tblTTLimits.Trader_ID AS USER, ...

try:

... SELECT 'TT' AS Source, tblTTLimits.Trader_ID AS [USER], ...

If you try this, please post feedback.

 

[jimmy_p84]

This also did the trick, Thanks very much