Reading date time fields from MS Access
I understand that reading date time fields through ODBC can be problematic. When reading a table containing several MS Access date time fields, some are correctly read, others are not. In the snippet below, both DateTimeFld1 and DateTimeFld2 are formatted identically within MS Access. When read in through RODBC date times in the first field are truncated to dates, but the second field imports correctly. I have tried this from both MS Access .mdb and .accdb files, and tried both the odbcConnect and odbcConnectAccess2007 functions with the same results. When using odbcConnect, I have specified a dsn created by C:\Windows\SysWOW64\odbcad32.exe driver? Please advise if there may be a solution to reliably handle date time fields from MS Access. Snippet:
myMSAccessCnx<-"D:/TEMP/TEST.accdb" data.ch<-odbcConnectAccess2007(myMSAccessCnx, uid="afischbach") data.ch Test_tbl<-sqlFetch(data.ch, "Test_tbl") Test_tbl[1,]
PK DateTimeFld1 DateTimeFld2 1 41910 2004-04-26 2004-04-27 02:00:00