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

[Chris Evans]

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