SQL Over clause does not work on Excel VBA

Question

I am trying to create a running total column but the SQL OVER clause seems not to be working on my code, not sure what I am doing wrong. Help appreciated. This is the code:

Sub TryWithOver()

    Dim strpath As String, constr As String
    Dim Conn As Object, rs As Object
    Dim fld As Variant

   strpath = Range("PathTry") 'double checked the path exists to a Access file

    ' OPEN CONNECTION '
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strpath & ";"
    Set Conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set rsGeneralData = CreateObject("ADODB.Recordset")

    Conn.Open constr

    rs.Open "SELECT ID, Name, Department,  Salary, COUNT(Department) OVER(PARTITION BY Department) As DepartmentTotals FROM Table1 ", Conn

    
    Range("C9").Select
    ActiveCell(2, 1).CopyFromRecordset rs
rs.Close
Conn.Close
End Sub

And this is the error I get

Run-Time error '-2147217900 (80040e14)':
Syntax error (missing operator) in query expression 'COUNT(Department) OVER(PARTITION BY Department)'.

The data is this:

ID	Name	Department	Salary
1	James	IT	15000
2	James	IT	15000
3	Smith	IT	35000
4	Rasol	HR	15000
5	Rakesh	Payroll	35000
6	Pam	IT	42000
7	Stokes	HR	15000
8	Taylor	HR	67000
9	Preety	Payroll	67000
10	Priyanka	Payroll	55000
11	Anurag	Payroll	15000
12	Marshal	HR	55000
13	David	IT	96000

I am trying to do this on an excel sheet, with data from an Access database. Thanks a lot

Cheers