Conserving timestamps for Date/Time column when importing access sheets into R using RODBC

Question

I have an access database (/access.mdb), one sheet of which ("dive") I am trying to import into R using the following code:

db <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};
                        DBQ=D:/folder/access.mdb")

data <- as_tibble(sqlFetch(db, "dive", rownames=TRUE)) %>%
  select("ref", "DE_DATE") 

The sheet imports fine however the "ds_date" column, which is a Date/Time object in the database, only includes the dates as a POSIXct object, and not the timestamp. In the database they are space separated of the form dd/mm/yyyy HH:MM:SS as seen below which I think is where the issue is arising:

I've had a look into the sqlFetch function but can't see an immediately obvious way to manipulate individual columns when reading in a sheet. I'm not well versed in SQL so not sure how I would query this to ensure all the information in those cells is conserved. I would like to import the column including both date and timestamps, of the same format as in the database.

Many thanks for any help.

Answer 1

Since you're using dplyr/tidyverse, why not go full DBI/dbplyr?

library(DBI)
library(odbc)
library(dplyr)
library(dbplyr)

db <- dbConnect(odbc(), .connection_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:/folder/access.mdb")
data <- tbl(db, "dive") %>%
    select(ref, DE_DATE) %>%
    collect()

In my experience, DBI tends to get types right more often, and using dbplyr (since you're using dplyr already) has the additional advantage of not fetching data you don't use. In the example, only the columns ref and DE_DATE are fetched, while if you use RODBC, all columns would be fetched and hen the unused columns would be discarded.

Note the collect() call means you're actually fetching the data, until then any operations actually compose an SQL statement and do not fetch data.