If you are looking at executing without referencing the library...in the
previous code replace the below two lines

rs.CursorLocation = adUseClient
rs.Open "select * from <tablename", con, adOpenDynamic

with

rs.CursorLocation = 3
rs.Open "select * from <tablename", con, 2

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.