Hi, I am having trouble getting SQL-data by using VBA.

Everything Works fine until I use the "inner Join"-sql command.

Basicly I want to exctract the newest updates from a employee table. The table would look something like this:


EmployeeNo ChangeNo EmploymentStatus FromDate
1 10 Working 01.01.2014
2 11 Working 01.02.2014
2 12 Sick 01.03.2014
1 13 Quit 01.03.2014

The ChangeNo is a PK, and will increase for each New row in the table.

I want the Query to understand that the ChangeNo 12 and 13 is the largest, and then display EmployeeNo, ChangeNo, EmploymentStatus and FromDate for the row With ChangeNo 12 and 13.
To do this, I have tried to tweek an excelent macro by Carl.

Unfortunately, I am struggeling with the "inner join"-bit of my macro.


Sub FindEmploymentStatus()
    ' Find_Highest_ChangeNo_And_EmploymentStatus_For_Each_Emplye
     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 6.x library
     '
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    Server_Name = "--MyServer--" ' Enter your server name here
    Database_Name = "--MyDatabase--" ' Enter your database name here
    User_ID = "--MyUserID--" ' enter your user ID here
    Password = "--MyPassword--" ' Enter your password here
    
    
'   This works OK
    SQLStr = "SELECT --Table--.EmployeeNo, --Table--.ChangeNo, --Table--.EmploymentStatus,--Table--.FromDate " & _
        "from [--MyDatabase--].[--MySchema--].[--Table--]"

     
     
'       This is what I need, and work when i copy it to the commandtext in a datadefinition-window
'    SQLStr = "SELECT --Table--.EmployeeNo, --Table--.ChangeNo, --Table--.EmploymentStatus,--Table--.FromDate " _
'        & "from [--MyDatabase--].[--MySchema--].[--Table--]" _
'        & "Inner join(select EmployeeNo, MAX(ChangeNo) ChangeNo from [--MyDatabase--].[--MySchema--].[--Table--] group by EmployeeNo) SS" _
'        & "On --Table--.EmployeeNo = SS.EmployeeNo and --Table--.ChangeNo=SS.ChangeNo"
     
     
          
     
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
     
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("--MySheet--").Range("a11") ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
        
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Where did I go wrong?

Best regards
Habang