Refer to 'Microsoft ActiveX Data Object 2.x library' from
VBEToolsReferences and try the below...For testing keep your database in c:\


Sub ExtractFromAccess()
Dim strDB As String, lngRow As Long, varEmpNo As Variant
Dim con As New ADODB.Connection, rs As New ADODB.Recordset


strDB = "C:\db1.mdb"
varEmpno = <query employee number
con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}"
rs.CursorLocation = adUseClient

strQuery = "SELECT Sum(points) AS SumofPoints FROM Absences WHERE " & _
"[Absences.Date] Date() - 91 And [Absences.Employee Number] = " & varEmpNo

rs.Open strQuery, con, adOpenDynamic
Do While rs.EOF = False
Msgbox rs(0)
rs.MoveNext
Loop
rs.Close: con.Close
Set rs = Nothing: Set con = Nothing
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"AZSteve" wrote:

Jacob: This is what I have so far from your suggestion

Sub ExtractFromAccess()
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Dim strDB As String, strQ1 as String, lngRow As Long

strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee
File.mdb"

strQ1 = "13-Week Points for CSR"
'The following is the SQL from strQ1
'SELECT DISTINCT Sum([Points]) AS SumofPoints
'FROM Absences
'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])=[Enter
Employee Number]));

con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}"
rs.CursorLocation = adUseClient
rs.Open "select * from <tablename", con, adOpenDynamic
'---------------------------------
lngRow = 1
Do While rs.EOF = False
Range("A" & lngRow) = rs("opid")
Range("B" & lngRow) = rs("opname")
lngRow = lngRow + 1
rs.MoveNext
Loop
'---------------------------------
rs.Close: con.Close
Set rs = Nothing: Set con = Nothing
End Sub
============================
At "rs.CursorLocation = adUseClient" I am getting error message "Arguments
are of the wrong type, are out of the acceptable range, or are in conflict
with one another." What is the issue here?

As I had said, I am using a Query in the DB (called strQ1 above, with the
actual SQL of the query just below it). Instead of referencing a specific
Query do I have instead have to put the actual SQL from that Query in the
next line where you have

rs.Open "select * from <tablename", con, adOpenDynamic ?

Obviously I will have to reference an employee number rather than prompting
for it.

Is this code putting the results from all employee numbers I reference into
an Excel table at A1, B1, A2, B2, etc based on lngRow?

Thanks for your help.