[R] Import of Access data via RODBC changes column name ("NO" to "Expr1014") and the content of the column

[Maciej Hoffman-Wecker]

Dear all,
 
I have some problems with importing data from an Access data base via
RODBC to R. The data base contains several tables, which all are
imported consecutively. One table has a column with column name "NO". If
I run the code attached on the bottom of the mail I get no complain, but
the column name (name of the respective vector of the data.frame) is
"Expr1014" instead of "NO". Additionally the original column (type
"text") containes "0"s and missings, but the imported column contains
"0"s only (type "int"). If I change the column name in the Access data
base to "NOx", the import works fine with the right name and the same
data. 
 
Previously I generated a tiny Access data base which reproduced the
problem. To be on the safe site I installed the latest version (2.5.1)
and now the example works fine, but within my production process the
error still remaines. An import into excel via ODBC works fine.

So there is no way to figure it out whether this is a bug or a
feature.-)
 
The second problem I have is that when I rerun "rm(list = ls(all = T));
gc()" and the import several times I get the following error:
 
Error in odbcTables(channel) : Calloc could not allocate (263168 of 1)
memory
In addition: Warning messages:
1: Reached total allocation of 447Mb: see help(memory.size) in:
odbcQuery(channel, query, rows_at_time) 
2: Reached total allocation of 447Mb: see help(memory.size) in:
odbcQuery(channel, query, rows_at_time) 
3: Reached total allocation of 447Mb: see help(memory.size) in:
odbcTables(channel) 
4: Reached total allocation of 447Mb: see help(memory.size) in:
odbcTables(channel) 

which is surprising to me, as the first two statements should delete all
objects and recover the memory. Is this only a matter of memory? Is
there any logging that reduces the memory? Or is this issue connected to
the upper problem?
 
I added the code on the bottom - maybe there is some kind of misuse I
lost sight of. Any hints are appreciated.
 
Kind regards,
Maciej
 
> version
               _                           
platform       i386-pc-mingw32             
arch           i386                        
os             mingw32                     
system         i386, mingw32               
status                                     
major          2                           
minor          5.1                         
year           2007                        
month          06                          
day            27                          
svn rev        42083                       
language       R                           
version.string R version 2.5.1 (2007-06-27)

 
## code
 
get.table <- function(name, db, drop = NULL){
  .con <- try(odbcConnectAccess(db), silent = T)
  if(!inherits(.con, "RODBC")) return(.con)
  ## exclude memo columns
  .t <- try(sqlColumns(.con, name))
  if(inherits(.t, "try-error")){close(.con); return(.t)}
  .t <- .t[.t$"COLUMN_SIZE" < 255, "COLUMN_NAME"]
  .t <- paste(.t, collapse = ",")
  ## get table
  .t <- paste("select", .t, "from", name)
  .d <- try(sqlQuery(.con, .t), silent = T)
  if(inherits(.d, "try-error")){close(.con); return(.d)}
  .con <- try(close(.con), silent = T)
  if(inherits(.con, "try-error")) return(.con)
  .d <- .d[!names(.d) %in% drop]
  return(.d)
}
 
get.alltables <- function(db){
  .con <- try(odbcConnectAccess(db), silent = T)
  if(!inherits(.con, "RODBC")) return(.con)
  .tbls <- try(sqlTables(.con)[["TABLE_NAME"]])
  if(inherits(.tbls, "try-error")){close(.con); return(.tbls)}
  .con <- try(close(.con), silent = T)
  if(inherits(.con, "try-error")) return(.con)
  .tbls <- .tbls[-grep("^MSys", .tbls)]
  .d <- lapply(seq(along = .tbls), function(.i){
    .d <-
      try(get.table(.tbls[.i], db = db)) 
    return(invisible(.d))
  })
  names(.d) <- .tbls
  .ok <- !sapply(.d, inherits, "try-error")
  return(list(notdone = .d[!.ok], data = .d[.ok]))
}
 
library(RODBC)
 
alldata <- get.alltables(db = "./myaccessdb.MDB")
 
## code end