SQL Error Causes Compile Error

[goss]

Hi all,

Using Excel 2007.

Something I'm doing in my SQL string is causing a compile error

Compile error:
Expected: end of statement

SQL Snippet

 stSQL1 = "SELECT Dept_Name FROM Department & _
                LEFT OUTER JOIN Employees & _
                ON Department.DeptID = Employees.DeptID"

Full code below
thx
w

Option Explicit

Sub ImportRecords()
    '
    'Imports all records from the specified table
    'Uses Microsoft ActiveX Data Objects 2.7 Library
    '
    'Date       Developer       Action
    '---------------------------------------------
    '01/20/12   ws              Created
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strDBPath As String
    Dim strDB As String
    Dim strDBTable As String
    Dim strDBPathFile As String
    Dim cnt As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim stSQL1 As String
    Dim stConn As String
    
    'Initialize
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .DisplayAlerts = False
        End With
           
    'Instantiate objects
        Set cnt = New ADODB.Connection
        Set rst1 = New ADODB.Recordset
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("ImportRecs")
         
    'Get database string values
        With ws
            strDBPath = .Range("C4")
            strDB = .Range("C5")
            strDBTable = .Range("C6")
        End With
        
    ' Get the database name.
        strDBPathFile = strDBPath
        If Right$(strDBPathFile, 1) <> "\" Then strDBPathFile = strDBPathFile & _
            "\"
        strDBPathFile = strDBPathFile & strDB

    'Connection String
        stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & strDBPathFile & ";"
    
    'SQL-statement to be executed.
'        stSQL1 = "SELECT * FROM " & strDBTable
        stSQL1 = "SELECT Dept_Name FROM Department & _
                LEFT OUTER JOIN Employees & _
                ON Department.DeptID = Employees.DeptID"
        
    ws.Range("A20:XFD1048576").ClearContents 'XFD1048576 2007/2010 only
        
    With cnt
        .Open (stConn) 'Open the connection.
        .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
    End With
        
    With rst1
        .Open stSQL1, cnt 'Create the recordset.
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With
        
    With ws
        .Cells(20, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
    End With

    'Tidy up
        Set wb = Nothing
        Set ws = Nothing
        Set cnt = Nothing
        Set rst1 = Nothing
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .DisplayAlerts = False
        End With
        
End Sub

 

[Marcelo Branco]

Hi,

I have not looked at your code but you SQL string must be

stSQL1 = "SELECT Dept_Name FROM Department" & _
"LEFT OUTER JOIN Employees" & _
"ON Department.DeptID = Employees.DeptID"

M.

 

[Norie]

It's not the SQL statement itself, it's missing quotes.

You still need them even though you've used the continuation character.

stSQL1 = "SELECT Dept_Name FROM Department " & _
                " LEFT OUTER JOIN Employees " & _
                " ON Department.DeptID = Employees.DeptID"

 

[goss]

Perfect!

Thanks Barcelo and Norie

thx
w