Best way to get data from Access 2003 to Excel 2003

[AZSteve]

I have two Access queries set up for two tables in an employee database at a
network location. Each prompts for an employee ID and returns one number - a
sum of the employee's data in the particular table. With up to 20 Employees
in each spreadsheet, we could import up to 40 sums (presently 40 separate
queries).

The data will not end up in an Excel table. In Excel each employee's info
is in an area 25 rows x 15 columns on the same sheet, with the employee ID
and two pieces of imported data in the same relative cells in each of the 20
employee areas.

This import will be done daily as part of a macro and formulas processing
purely Excel data. This is my first foray into importing data into Excel
from Access. What is the best way to get this info into those Excel cells?
Importing the data into a separate Excel table having the employee ID as the
leftmost field, then using formulas in those 40 cells to grab the applicable
data from the table? Importing the data directly into those 40 cells through
an Excel macro? Another way? So far the "importing data" help I have found
has been mostly getting data directly from an Access table to an Excel table,
and not through queries that return info as I described in the first
paragraph.

 

[Jacob Skaria]

Try the below.. For connection strings you can refer
http://www.connectionstrings.com/

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

strDB = "C:\opstats.mdb"
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

If this post helps click Yes

 

[Jacob Skaria]

Just to add on

--You need to either
Refer to 'Microsoft ActiveX Data Object 2.x library' from VBE>Tools>References

OR reference the object using CreateObject Function at the beginning of the
code

Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

--You can either use the field names to retrieve data like

Range("A" & lngRow) = rs("opid")
Range("B" & lngRow) = rs("opname")

OR use the index number

Range("A" & lngRow) = rs(0)
Range("B" & lngRow) = rs(1)

If this post helps click Yes

 

[AZSteve]

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.

 

[Jacob Skaria]

Refer to 'Microsoft ActiveX Data Object 2.x library' from
VBE>Tools>References 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]

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

 

[AZSteve]

Based on previous suggestions from Jacob Skaria and others around 9/29, I
have done this but still get errors.

Sub Access_Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection, rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range, Here As Range
Dim varEmplNo As String
'Set destination
Set Location = [V3]
'Here = Range("V3").Address
'Who = Range("V3").Offset(0, -5).Value
varEmplNo = "400476"
'Set source
MyConn = "\\phepsilon\groups\NPIC_Restricted\Administration\NOOC
Employee File.mdb"
'Create query
sSQL = "SELECT DISTINCT Sum([Points]) AS SumofPoints FROM Absences WHERE
(((Absences.Date)>Date()-91) AND ((Absences.[Employee Number])= " & varEmplNo
& "));"

'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set rs = .Execute(sSQL)
End With
(etc)

Gives "Data type mismatch in criteria expression" with the "Set rs = " line
highlighted. I do have the reference specified at the beginning in the
Reference library.

---------------------

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:\Documents and Settings\mullst\Desktop\NOOC Employee File.mdb"
varEmpNo = "400476"
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

Gives "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression" with "rs.Open strQuery, con, adOpenDynamic" highlighted.