[R] odbcConnect won't open a DSN that opens using isql (Linux: Ubuntu)

Mon Nov 2 18:38:35 CET 2026

Glad to hear that was helpful Chris.
All the best!
gregg

On Monday, November 2nd, 2026 at 5:46 AM, Chris Evans <chrishold using psyctc.org> wrote:

> Many thanks to both Gregg and Ivan (I hope first names are OK here) First to Gregg's points.
> 

> 

> 

> On 01/11/2026 20:24, Gregg Powell wrote:
> 

> > Hi Chris,
> > 

> > I think there are two separate things going on, and only one of them is RODBC's fault.
> > 

> > 1. The warnings are probably harmless.
> > 

> > After connecting, odbcConnect() asks the driver to return the completed connection string and then runs grepl("PWD=", cs) on it so it can hide any password. The MDBTools ODBC driver doesn't seem to fill in that returned string properly, so R ends up reading whatever bytes happen to be in the buffer. That explains why the "unable to translate" garbage changes on every run. The connection itself is probably open. You can check with:
> > 

> > > connEOS <- odbcConnect("EOS")
> > > sqlQuery(connEOS, "SELECT COUNT(*) FROM SomeRealTableName")
> > 

> > 2. The empty sqlTables() result and the sqlFetch() failure are driver limitations.
> > 

> > MDBTools' implementation of the ODBC catalog functions (SQLTables and related calls) is minimal. sqlFetch() first calls odbcTableExists(), which relies on sqlTables(), so it fails even when the table exists. You can skip that check by querying directly:
> > 

> > > sqlQuery(connEOS, "SELECT * FROM SomeRealTableName") %>% as_tibble()
> > 

> > 3. The bigger problem: vw_Prodotti is almost certainly an Access saved query, not a table.
> 

> I am neither skilled in SQL, Access or Italian but I think I can see that "vw_Prodotti" sounds like a query (a.k.a. "view"?) but it's not, it really is a table confirmed by converting the database into sqlite and looking at it with "DB browser for SQLite" and also I did find my way to opening that sqlite in R and vw_Prodotti pulls through as a table from that sqlite (one with 49,717 rows and 16 variables!)
> 

> > MDBTools can only read tables. It cannot execute Access queries or views at all, so vw_Prodotti won't work through any route on Linux. If this worked two years ago, I'd guess you were running on Windows with the Microsoft Access driver, which does support queries. You can confirm this in isql: SELECT * FROM vw_Prodotti will fail there too, 

> 

> Reassuringly, to me, it works fine ending:
> 

> SQLRowCount returns 49717
> 49717 rows fetched
> 

> > while selecting from the underlying tables will work.
> > 

> > The fix is to get the SQL behind the view and run it against the base tables yourself. mdbtools can show it from the shell (or from R via system()):
> 

> Aha, this too was very helpful. I ran as ordinary user (same as R) in the directory with the mdb file
> 

> > > system("mdb-queries -L EOS2024a.mdb")              # list saved queries
> 

> Got me ten queries:
> 

> > > system("mdb-queries EOS2024a.mdb vw_Prodotti")     # print its SQL
> 

> worked fine on the first of those queries and being able to pull up those queries may be helpful for me.
> 

> > > system("mdb-tables -1 EOS2024a.mdb")               # list real tables
> 

> Got me a list of 27 tables matching the list I got pulling into R from the sqlite converted database, reassuring again!
> 

> > You may need to adjust the Access-flavored SQL slightly (for example IIf(), square brackets, or #date# literals) before MDBTools will accept it. Often it's simpler to pull the base tables into R and rebuild the joins in dplyr.
> > 

> > 4. Alternatively, you can skip ODBC entirely.
> > 

> > For read-only extraction this is often more reliable:
> > 

> > > library(Hmisc)
> > > f <- "/datadisk/.../EOS2024a.mdb"
> > > mdb.get(f, tables = TRUE)                # list tables
> > > prod <- mdb.get(f, tables = "vw_Prodotti")  # wraps mdb-export
> 

> Ah, I didn't know that bit of (the ever useful!) Hmisc package. Works fine and concurrs with other ways of opening vw_Prodotti.
> 

> > Or call mdb-export yourself, e.g.:
> > 

> > > prod <- read_csv(pipe(paste("mdb-export", shQuote(f), "Prodotti")))
> 

> Also worked fine so I am expanding my repertoire of ways to pull data into R, in Linux, from Access mdb database. This will all go in my Rblog later today (https://www.psyctc.org/Rblog/posts/2022-06-19-reading-data-into-r-from-msoft-access-mdb/).
> 

> > In short: the warnings are probably cosmetic, the table check fails because of MDBTools' weak catalog support, and the view fails because MDBTools can't run Access queries. Use sqlQuery() on the base tables and recreate the view from the SQL shown by mdb-queries.
> 

> Well yes and no but this was extremely helpful and appreciated and that takes me to Ivan's post in a minute.
> 

> Very best Gregg, Ivan and all,
> 

> Chris
> 

> > HTH,
> > gregg
> > 

> > 

> > 

> > 

> > 

> > On Monday, October 26th, 2026 at 11:06 AM, Chris Evans via R-help <r-help using r-project.org> wrote:
> > 

> > > It's a few years since I last did this and opening SQL and/or odbc
> > > databases is not my expert area but when I use the following code (which
> > > I am pretty sure worked two years ago!) I get this:
> > 

> > > > library(RODBC) # has the vital functions to access the database
> > > > library(tidyverse) # optional but I like working in the tidyverse manner if I can
> > > 

> > > ── Attaching core tidyverse packages ─────────────────────────────────────────────────────────────────────────────────────────── tidyverse 2.0.0 ──
> > > ✔ dplyr     1.2.1     ✔ readr     2.2.0
> > > ✔ forcats   1.0.1     ✔ stringr   1.6.0
> > > ✔ ggplot2   4.0.3     ✔ tibble    3.3.1
> > > ✔ lubridate 1.9.5     ✔ tidyr     1.3.2
> > > ✔ purrr     1.2.2
> > > ── Conflicts ───────────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
> > > ✖ dplyr::filter() masks stats::filter()
> > > ✖ dplyr::lag()    masks stats::lag()
> > > ℹ Use the conflicted package to force all conflicts to become errors
> > > 

> > > > ### this opens the mdb file pointed to in the DSN created in Windoze
> > > > connEOS <- odbcConnect("EOS")
> > > 

> > > Warning messages:
> > > 1: In grepl("PWD=", cs) :
> > >    unable to translate '<c0><d3>6<ed><b1>{' to a wide string
> > > 2: In grepl("PWD=", cs) : input string 1 is invalid
> > 

> > > > allTables3 <- sqlTables(connEOS) # get a list of all the tables in the database
> > > > allTables3
> > > 

> > > [1] TABLE_CAT   TABLE_SCHEM TABLE_NAME  TABLE_TYPE  REMARKS
> > > <0 rows> (or 0-length row.names)
> > > 

> > > > ### pull one table through
> > > > sqlFetch(connEOS, "vw_Prodotti") %>%
> > > 

> > > +   as_tibble()
> > > Error in odbcTableExists(channel, sqtable) :
> > >    ‘vw_Prodotti’: table not found on channel
> > 

> > > > ### close the connection
> > > > odbcClose(connEOS)
> > 

> > > As I read that RODB (and tidyverse) packages loaded OK but odbcConnect()
> > > throws two warnings (just warnings) but in fact the connection hasn't
> > > opened usably so perhaps they are at least useful clues.  One odd thing
> > > is that the contents of that "unable to translate ' " change each time I
> > > run the command.
> > 

> > > I am running R 4.6.1 on Ubuntu 26.04 with all packages updated so RODBC
> > > is 1.3.26.3.
> > 

> > > My /etc/odbcinst.ini has this in it:
> > 

> > > [MySQL ODBC 26.10 Unicode Driver]
> > > DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc26w.so
> > > UsageCount=1
> > 

> > > [MySQL ODBC 26.10 ANSI Driver]
> > > DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc26a.so
> > > UsageCount=1
> > 

> > > [MDBTools]
> > > Description=MDB Tools ODBC driver
> > > Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
> > > Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
> > > FileUsage=1
> > > UsageCount=1
> > 

> > > [MDBToolsW]
> > > Description=MDB Tools ODBC driver (Unicode)
> > > Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so
> > > Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so
> > > FileUsage=1
> > > UsageCount=1
> > 

> > > and /etc/odbc.ini has this:
> > 

> > > [EOS]
> > > Description=EOS database
> > > Driver=MDBTools
> > > Database=/datadisk/Current_share/Current_Data/CORE/translations/Italian/Modenaplus_CORE-OM/202410/EOS2024a.mdb
> > 

> > > and opening the database in the Linux console with "isql EOS" seem to
> > > show all the contents of the database so I think the problem is either
> > > within the RODBC package or something I have wrong in that odbc.ini or
> > > odbcinst.ini or something more esoteric.  (Not loading tidyverse doesn't
> > > change anything so not a collision between those packages and otherwise
> > > only the default packages are loaded.
> > 

> > > Searching the internet throws up information about gsub() giving a
> > > similar "unable to translate" issue but nothing more obviously useful so
> > > I have come here in hope someone will recognise this and have suggestions.
> > 

> > > TIA,
> > 

> > > Chris
> > 

> > > ______________________________________________
> > > R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > > https://stat.ethz.ch/mailman/listinfo/r-help
> > > PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> > > and provide commented, minimal, self-contained, reproducible code.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 603 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20261102/5d5cb114/attachment.sig>