1

I have an R Shiny script that works fine on my local windows computer, but fails when I deploy it to shinyapps.io (which is some unix like).

I originally used: odbcConnectAccess('test.mdb') but when deployed, I get the follwing error:

Unhandled error in observer: could not find function "odbcConnectAccess"

I tested, and other RODBC functions still work. So I tried this code (based of this post, which also happens to be what the internals of odbcConnectAccess() return)

uid=""
pwd=""
con =paste("Driver={Microsoft Access Driver (*.mdb)};Dbq=", file.path(getwd(),'test.mdb'), ";Uid=", uid, ";Pwd=", pwd, ";", sep = "")
conn=odbcDriverConnect(con)

Again, this works on my local machine, but when deployed to ShinyApps.io I get the following error:

[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified

I also accidentally tried odbcConnect(con), which gives the following error:

Warning in odbcDriverConnect("DSN=Driver={Microsoft Access Driver (*.mdb)};Dbq=/srv/connect/apps/ALC_app_v0_1/Salvage_data_FTP.mdb;Uid=;Pwd=;") :

[RODBC] ERROR: state IM012, code 0, message [unixODBC][Driver Manager]DRIVER keyword syntax error

In the help for odbcDriverConnect() it mentions that the the format for the connection string depends on your ODBC, and Shinysapps.io seems to use unixODBC, but I cant find what format unixODBC wants me to use so that I can establish the connection?

Can anyone help me figure out how open a connection to a .mdb on shinyapps.io? I'm not sure If I'll be able to alter .ini files on shinyapps.io server, so I'm really hoping to find the correct format/syntax to establish the connection without a DSN.

Thanks for any help or feedback!

Community
  • 1
  • 1
Kai Ross
  • 43
  • 7

1 Answers1

0

"...We currently only support open source databases on shinyapps.io, but we are watching to see if we get a lot more requests for proprietary databases." From ShinyApps-User group. 10/23/2015

So it looks like the answer is NO currently. [UPDATE SEE BELOW]

Sorry to answer my own question, but I'm going to leave it up since it took me a while to find the confirmation, and it could save others some headache.

[UPDATE] "Andy corrected me (while on vacation) and let me know that you can make this work by using the FreeTDS driver. I am not an expert on this, but if you change your code to something like this:

odbcDriverConnect("driver=FreeTDS;Server=hostname;database=dbname;uid=username;pwd=password")

We don't have any DSNs configured, but you can see tips for how to do this here: Trying to connect to an ODBC server using RODBC in ubuntu "

So it seems like it is possible. I'll update if I get it working.

Community
  • 1
  • 1
Kai Ross
  • 43
  • 7
  • Since MS Access Jet/ACE is a file level database engine, consider its open-source file level counterpart, SQLite, which should keep your workflow intact. It is lightweight and not too heavy on resources or installs and you can keep databases still in individual files, no server level enterprise solution like MySQL, SQL Server, PostgreSQL, DB2, Oracle. – Parfait Oct 26 '15 at 00:59