Really an Access question?
I'm sure of the full code you'd need but assuming that this syntax works with Access as it does with Excel:
Private Sub ODBCDirectTest()
Dim wrkKros As Workspace
Dim cnnKros As Connection
Dim qdfKros As QueryDef
Dim Results As Recordset
Dim Ret As Boolean
Dim sqlKros As String
Set wrkKros = CreateWorkspace("mykronos", "admin", "", dbUseODBC)
Set cnnKros = wrkKros.OpenConnection("", dbDriverNoPrompt, False, "ODBC;dsn=kronos;uid=userid;pwd=Password;")
Ret = wrkKros.Connections.Count
If Ret = False Then
MsgBox ("There was an Error connecting to the database"), vbCritical, "Error"
End
End If
sqlKros = "SELECT VWACTIVEEMPS.EMPLOYEEID, VWACTIVEEMPS.FIRSTNAME, VWACTIVEEMPS.MIDDLEINITIAL, VWACTIVEEMPS.LASTNAME, VWACTIVEEMPS.FULLNAME, VWACTIVEEMPS.EMPLOYEENUMBER, VWACTIVEEMPS.HIREDATE, VWACTIVEEMPS.WAGER" & _
"ATE, VWACTIVEEMPS.ACCRUALPROFILE, VWACTIVEEMPS.SITE, VWACTIVEEMPS.DEPT, VWACTIVEEMPS.JOBCODE, VWACTIVEEMPS.ACTIVITYCODE, VWACTIVEEMPS.LINEMANAGER, VWACTIVEEMPS.EMPLOYEETYPE, VWACTIVEEMPS.TEMP" & Chr(13) & "" & Chr(10) & "FROM TK" & _
"CSOWNER.VWACTIVEEMPS VWACTIVEEMPS"
Set qdfKros = cnnKros.CreateQueryDef("kronostemp", sqlKros)
Presuming you have the data source name set up it will pull the info into Excel.
Sorry it's not a full answer but it should give a sniff of the correct syntax.