Repost - Best way to get data from Access 2003 to Excel 2003

[AZSteve]

From posts on 9/21-9/22 responding to Jacob Skaria's help > 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" 'this is typical path to the Employee File DB on our network

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?

I need to get results for up to 20 employee numbers.

Thanks for your help.

 

[Jacob Skaria]

I am responding to your previous thread..

 

[Patrick Molloy]

adUseClient is a constant adn member of the Microsoft Active Data Objects
Library. If your project has not got a refrence to this, then use th evalue
intsead, which is 3

adOpenDynamic is 2


rather than looping through each record, you can drop the entire recordset
into the sheet

WITH rs
Range("A1").Resize( .RecordCount, .Fields.Count).CopyFromRecordset rs
END WITH