AS400 and ODBC

The data source name is that of the library where the tables reside.

Code Extract (Using VBA / MsQuery)

    ThisWorkbook.Activate
    
    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    '' Open the database
    Set dbs = OpenDatabase("DT2TEST", _
                dbDriverCompleteRequired, True, _
                "ODBC;DSN=DT2TEST;UID=MYIDHERE;PWD=MYPASSWORD;")
            
    vtSql = ""
    vtSql = vtSql & " SELECT MID(CLMN,1,3) , Count(*)"
    vtSql = vtSql & " FROM AUDITPF"
    vtSql = vtSql & " GROUP BY MID(CLMN,1,3);"

    Set rs = dbs.OpenRecordset(vtSql)

    With ThisWorkbook.Sheets("Sheet1")
        With .Cells(1, 1)
            .CurrentRegion.Clear
            '' Copies the contents of a DAO Recordset object onto a worksheet
            numberOfRows = .CopyFromRecordset(rs)
        End With
    End With

Notes
The AS400 flavour of SQL would expect SUBSTR instead of MID.This is probably because SUBSTR was already in use elsewhere within the Access/Jet environment when the SQL bit was written