Best way to get data from Access 2003 to Excel 2003
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.
----------------------------
I have tried lots of minor variations and still get error messages.
|