Get Table from MS Access Database

Provides data from an MS Access database table in forms of a data frame.

hsGetTable(
  mdb,
  tbl,
  cond = "TRUE",
  fields = "*",
  dbg = TRUE,
  check = TRUE,
  use2007Driver = NULL,
  ...
)

Arguments

mdb	full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”).
tbl	Table name. Put it into brackets [] if it contains spaces and if it mdb does not point to a MySQL database
cond	Condition string.
fields	Comma separated list of names of fields to be selected.
dbg	if TRUE, debug messages are shown, else not
check	if TRUE (default), tbl is checked for existence in mdb before trying to get the data and a list of available tables is shown in the case that the table does not exist.
use2007Driver	passed to hsTables and hsSqlQuery
...	Additional arguments to be passed to hsSqlQuery

Value

data.frame containing data from table in database

Details

ATTENTION: This function may not return what you want if the table contains a timestamp field. Use hsMdbTimeSeries instead.

See also

hsSqlQuery, hsPutTable, hsGetTimeSeries, hsMdbTimeSeries

Examples

if (FALSE) {
## Get all datasets from tbl_Hyd in example database where
## Q > 1.0 m3/s and temperature > 20 degree Celsius
## (only on Windows!)

if (.Platform$OS.type == "windows") {
  ts <- hsGetTable(xmdb(), "tbl_Hyd", "Q > 1.0 AND T_Kanal > 20")
  head(ts)
}

## Output:
# Zeitst     Q     v     H T_Kanal
# 1 2011-08-24 22:33:00 1.075 0.459 1.366    20.1
# 2 2011-08-24 22:34:00 1.062 0.453 1.370    20.2
# 3 2011-08-24 22:35:00 1.050 0.449 1.364    20.2
# 4 2011-08-24 22:36:00 1.042 0.446 1.361    20.3
# 5 2011-08-24 22:37:00 1.032 0.443 1.354    20.3
# 6 2011-08-24 22:38:00 1.010 0.436 1.348    20.4

## TAKE CARE when getting time-series data:
if (.Platform$OS.type == "windows") {
  hsGetTable(xmdb(), "tblTimestampTest_DST")
}

## Output:
#                tstamp
# 1 2011-03-27 01:00:00
# 2 2011-03-27 01:30:00
# 3                <NA>
# 4                <NA>
# 5 2011-03-27 03:00:00
# 6 2011-03-27 03:30:00

## As the output shows the timestamps between 02:00:00 and
## 02:59:59 have been set to <NA>. Reason: When retrieving
## date/time data from MS Access, R converts the timestamps
## from a text representation into POSIXct objects. As POSIXct's
## standard time zone seems to be taken from the Windows system
## R tries to convert to Central European Time (CET) which
## does not exist for the hour in which time is switched to
## daylight-saving time (as in the example).

## This standard behaviour can be changed by setting the
## standard time zone:
tz <- Sys.getenv("tz") # save current standard time zone
Sys.setenv(tz = "UTC") # set standard time zone to UTC

## The same command as above now delivers all timestamps
## (in Coordinated Universal Time, UTC):

if (.Platform$OS.type == "windows") {
  hsGetTable(xmdb(), "tblTimestampTest_DST")
}

## Output:
#                tstamp
# 1 2011-03-27 01:00:00
# 2 2011-03-27 01:30:00
# 3 2011-03-27 02:00:00
# 4 2011-03-27 02:30:00
# 5 2011-03-27 03:00:00
# 6 2011-03-27 03:30:00

## Reset standard time zone
Sys.setenv(tz = tz)
}