[R] Import Access MDB on line

[Raphael Saldanha]

I am really sorry for the inconvenience, this will not repeat again.
This message is in plain text and I will try give every information
needed. If I'm not obeying some of the recommendations on the Posting
Guide, please let me know.

The IBGE, a Brazilian foundation of Geography and Statistics, had
granted access to a MDB (MS Access) file which contains geographic
localizations with geographic coordinates and altitude. I would like
to import this dataset direct into R, without saving the file on disk
(for using the most updated file), and without using MS Acces (I don't
use the Office pack). With this code, I thinking to create a package
or suggest the code for some spatial package.

With the RODBC package, I'm using the following code:

loc <- odbcConnectAccess("ftp://geoftp.ibge.gov.br/Organizacao/Localidades/cadastro_localidades_selecionadas")

And receive the message box: "Nome de arquivo inválido" (invalid file
name), with Ok and Cancel. When I press Ok, a dialog window is
prompted,  where I should point the file locally. After pressing the
Cancel button, the console gives me:

Warning messages:
1: In odbcDriverConnect(con, ...) :
  [RODBC] ERROR: Could not SQLDriverConnect
2: In odbcDriverConnect(con, ...) : ODBC connection failed


The file can be downloaded without problems, but I haven't MS Access
to open it, and a checksum hash to check the integrity of the file.
After download the file, I have the following:

> loc <- odbcConnectAccess("C:/Users/PLANGEO/Downloads/cadastro_localidades_selecionadas")
> loc
RODBC Connection 6
Details:
  case=nochange
  DBQ=C:\Users\PLANGEO\Downloads\cadastro_localidades_selecionadas
  Driver={Microsoft Access Driver (*.mdb)}
  DriverId=281
  FIL=MS Access
  MaxBufferSize=2048
  PageTimeout=5
  UID=admin

Which shows that the file is OK, and the odbcConnectAccess function is
going good. So, I think the problem is acquiring the file from the FTP
address.

With the Hmisc package, I'm trying the following code:

> mdb.get("ftp://geoftp.ibge.gov.br/Organizacao/Localidades/cadastro_localidades_selecionadas.mdb")
Erro em system(paste("mdb-tables -1", file), intern = TRUE) :
  'mdb-tables' not found
> mdb.get("ftp://geoftp.ibge.gov.br/Organizacao/Localidades/cadastro_localidades_selecionadas.mdb", table=TRUE)
Erro em system(paste("mdb-tables -1", file), intern = TRUE) :
  'mdb-tables' not found

I don't know if I have the mdb-tool on my system, may is this. If so,
how should I install this?


Details about the OS and R:

OS: Windows 7 and Ubuntu 11.10 (both 32 bits)

sessionInfo:

R version 2.12.1 (2010-12-16)
Platform: i386-pc-mingw32/i386 (32-bit)

locale:
[1] LC_COLLATE=Portuguese_Brazil.1252  LC_CTYPE=Portuguese_Brazil.1252
[3] LC_MONETARY=Portuguese_Brazil.1252 LC_NUMERIC=C
[5] LC_TIME=Portuguese_Brazil.1252

attached base packages:
[1] splines   stats     graphics  grDevices utils     datasets  methods
[8] base

other attached packages:
[1] RODBC_1.3-3     Hmisc_3.8-3     survival_2.36-2

loaded via a namespace (and not attached):
[1] cluster_1.13.2  grid_2.12.1     lattice_0.19-13 tools_2.12.1


On Fri, Nov 18, 2011 at 11:19 AM, Barry Rowlingson
<b.rowlingson at lancaster.ac.uk> wrote:
>
> On Fri, Nov 18, 2011 at 12:15 PM, Raphael Saldanha
> <saldanha.plangeo at gmail.com> wrote:
> > Hi!
> >
> > I need to import an Access MDB database from this FTP address:
> > ftp://geoftp.ibge.gov.br/Organizacao/Localidades/cadastro_localidades_selecionadas.mdb
> >
> > I tried with Hmisc mdb.get and RODBC without sucess.... any tip?
> >
>
>  What kind of not success did you have?  Did you download the file
> okay? Did mdb.get give you any error messages? Did you try running
> some of the mdb-tools commands on it? Did you try loading it into
> Access? You haven't told us what operating system you are running or
> what.
>
>  More details please!



--
Atenciosamente,

Raphael Saldanha
saldanha.plangeo at gmail.com