[R] Problem to fetch data from access using odbc connection
Christofer Bogaso
bogaso.christofer at gmail.com
Sun Nov 11 13:34:07 CET 2012
Hi all, apologies that I could not give any reproducible example,
however I think that following should be okay to explain my problem.
Basically I have an Access database (for time series data) I want to use
to R to analysis the data. Following is my code:
> library(RODBC)
> connect <- odbcConnectAccess("f:/DB/DB.mdb")
> Dat1 <- sqlQuery(connect, paste("select * from Tab1"))
> head(Dat1)
ID date V1 V2 V3 V4 V5 V6 V7
1 1 2010-11-09 834250.6 -16108789 -632484.8 63081.40 4458523 2555456
-207267.0
2 2 2010-11-10 851517.3 -16119263 -628400.6 54481.96 4479238 2567500
-214460.7
3 3 2010-11-11 775105.3 -15463394 -629370.5 53396.52 4076683 2343576
-207286.9
4 4 2010-11-12 792074.1 -15849460 -596806.3 56880.91 4344089 2491808
-208978.8
5 5 2010-11-13 786891.4 -15607995 -669157.0 58312.70 4197971 2414737
-210574.0
6 6 2010-11-14 781071.8 -15727322 -658322.0 56193.81 4277270 2462926
-211688.3
So far so good. However if I want to fetch data based on 'date', I am
getting error. Below are my tries:
> sqlQuery(connect, paste("select * from Tab1 where date < 2010-11-10"))
[1] ID date V1 V2 V3 V4 V5 V6 V7
<0 rows> (or 0-length row.names)
> sqlQuery(connect, paste("select * from Tab1 where date < '2010-11-10'"))
[1] "22018 -3030 [Microsoft][ODBC Microsoft Access Driver] Data type
mismatch in criteria expression."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select * from Tab1 where
date < '2010-11-10''"
Can somebody here help me with right code?
Thanks and regards,
More information about the R-help
mailing list