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
Bookmarks