st: Driver choice for MS-Access ODBC connectivity from Linux

["Neil Shephard" <nshephard@gmail.com>]

Hi,

I'm trying to get started using -odbc- to 'access' data from an
MS-Access database.

The problem I have is that I use Stata under GNU/Linux, whilst the
MS-Access database is (not surprisingly) on a Windows OS on the local
network.

I've read through the FAQ at
http://www.stata.com/support/faqs/data/odbcmu.html and have installed
iodbc as described (and for good measure I've also installed unixODBC
which is also and ODBC driver for linux, similar to iodbc, see
http://www.unixodbc.org/)

I'm now in the process of configuring my ~/.odbc.ini file to define
the DSN for the database and driver that I wish to connect to.

The trouble I have is that I've not idea whether I have a suitable
driver installed.

Installation of iodbc and unixODBC has added a number of libraries to
/usr/lib/ as the following shows....

$ ls /usr/lib/*odbc* -l
-rw-r--r-- 1 root root 513836 May 31 12:20 /usr/lib/libiodbc.a
-rw-r--r-- 1 root root    800 May 31 12:20 /usr/lib/libiodbc.la
lrwxrwxrwx 1 root root     18 May 31 12:20 /usr/lib/libiodbc.so ->
libiodbc.so.2.1.15
lrwxrwxrwx 1 root root     18 May 31 12:20 /usr/lib/libiodbc.so.2 ->
libiodbc.so.2.1.15
-rwxr-xr-x 1 root root 288792 May 31 12:20 /usr/lib/libiodbc.so.2.1.15
-rw-r--r-- 1 root root 392076 May 31 12:20 /usr/lib/libiodbcadm.a
-rw-r--r-- 1 root root   1060 May 31 12:20 /usr/lib/libiodbcadm.la
lrwxrwxrwx 1 root root     21 May 31 12:20 /usr/lib/libiodbcadm.so ->
libiodbcadm.so.2.1.15
lrwxrwxrwx 1 root root     21 May 31 12:20 /usr/lib/libiodbcadm.so.2
-> libiodbcadm.so.2.1.15
-rwxr-xr-x 1 root root 233608 May 31 12:20 /usr/lib/libiodbcadm.so.2.1.15
-rw-r--r-- 1 root root 122672 May 31 12:20 /usr/lib/libiodbcinst.a
-rw-r--r-- 1 root root    828 May 31 12:20 /usr/lib/libiodbcinst.la
lrwxrwxrwx 1 root root     22 May 31 12:20 /usr/lib/libiodbcinst.so ->
libiodbcinst.so.2.1.15
lrwxrwxrwx 1 root root     22 May 31 12:20 /usr/lib/libiodbcinst.so.2
-> libiodbcinst.so.2.1.15
-rwxr-xr-x 1 root root  69756 May 31 12:20 /usr/lib/libiodbcinst.so.2.1.15
-rwxr-xr-x 1 root root 164744 May 31 14:33 /usr/lib/libmyodbc3-3.51.12.so
-rw-r--r-- 1 root root 219030 May 31 14:33 /usr/lib/libmyodbc3.a
-rw-r--r-- 1 root root    978 May 31 14:33 /usr/lib/libmyodbc3.la
lrwxrwxrwx 1 root root     21 May 31 14:33 /usr/lib/libmyodbc3.so ->
libmyodbc3-3.51.12.so
-rwxr-xr-x 1 root root 164780 May 31 14:33 /usr/lib/libmyodbc3_r-3.51.12.so
-rw-r--r-- 1 root root 219510 May 31 14:33 /usr/lib/libmyodbc3_r.a
-rw-r--r-- 1 root root    994 May 31 14:33 /usr/lib/libmyodbc3_r.la
lrwxrwxrwx 1 root root     23 May 31 14:33 /usr/lib/libmyodbc3_r.so ->
libmyodbc3_r-3.51.12.so
-rw-r--r-- 1 root root 803806 May 31 14:10 /usr/lib/libodbc.a
-rw-r--r-- 1 root root    802 May 31 14:10 /usr/lib/libodbc.la
lrwxrwxrwx 1 root root     16 May 31 14:10 /usr/lib/libodbc.so ->
libodbc.so.1.0.0
lrwxrwxrwx 1 root root     16 May 31 14:10 /usr/lib/libodbc.so.1 ->
libodbc.so.1.0.0
-rwxr-xr-x 1 root root 423360 May 31 14:10 /usr/lib/libodbc.so.1.0.0
-rw-r--r-- 1 root root  85978 May 31 14:10 /usr/lib/libodbccr.a
-rw-r--r-- 1 root root    811 May 31 14:10 /usr/lib/libodbccr.la
lrwxrwxrwx 1 root root     18 May 31 14:10 /usr/lib/libodbccr.so ->
libodbccr.so.1.0.0
lrwxrwxrwx 1 root root     18 May 31 14:10 /usr/lib/libodbccr.so.1 ->
libodbccr.so.1.0.0
-rwxr-xr-x 1 root root  34980 May 31 14:10 /usr/lib/libodbccr.so.1.0.0
-rw-r--r-- 1 root root   1678 May 31 14:10 /usr/lib/libodbcdrvcfg1S.a
-rw-r--r-- 1 root root    854 May 31 14:10 /usr/lib/libodbcdrvcfg1S.la
lrwxrwxrwx 1 root root     24 May 31 14:10 /usr/lib/libodbcdrvcfg1S.so
-> libodbcdrvcfg1S.so.1.0.0
lrwxrwxrwx 1 root root     24 May 31 14:10
/usr/lib/libodbcdrvcfg1S.so.1 -> libodbcdrvcfg1S.so.1.0.0
-rwxr-xr-x 1 root root   3612 May 31 14:10 /usr/lib/libodbcdrvcfg1S.so.1.0.0
-rw-r--r-- 1 root root   1198 May 31 14:10 /usr/lib/libodbcdrvcfg2S.a
-rw-r--r-- 1 root root    854 May 31 14:10 /usr/lib/libodbcdrvcfg2S.la
lrwxrwxrwx 1 root root     24 May 31 14:10 /usr/lib/libodbcdrvcfg2S.so
-> libodbcdrvcfg2S.so.1.0.0
lrwxrwxrwx 1 root root     24 May 31 14:10
/usr/lib/libodbcdrvcfg2S.so.1 -> libodbcdrvcfg2S.so.1.0.0
-rwxr-xr-x 1 root root   3312 May 31 14:10 /usr/lib/libodbcdrvcfg2S.so.1.0.0
-rw-r--r-- 1 root root 203418 May 31 14:10 /usr/lib/libodbcinst.a
-rw-r--r-- 1 root root    830 May 31 14:10 /usr/lib/libodbcinst.la
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbcinst.so ->
libodbcinst.so.1.0.0
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbcinst.so.1
-> libodbcinst.so.1.0.0
-rwxr-xr-x 1 root root  72716 May 31 14:10 /usr/lib/libodbcinst.so.1.0.0
-rw-r--r-- 1 root root   1994 May 31 14:10 /usr/lib/libodbcminiS.a
-rw-r--r-- 1 root root    833 May 31 14:10 /usr/lib/libodbcminiS.la
lrwxrwxrwx 1 root root     21 May 31 14:10 /usr/lib/libodbcminiS.so ->
libodbcminiS.so.1.0.0
lrwxrwxrwx 1 root root     21 May 31 14:10 /usr/lib/libodbcminiS.so.1
-> libodbcminiS.so.1.0.0
-rwxr-xr-x 1 root root   3804 May 31 14:10 /usr/lib/libodbcminiS.so.1.0.0
-rw-r--r-- 1 root root   4778 May 31 14:10 /usr/lib/libodbcmyS.a
-rw-r--r-- 1 root root    819 May 31 14:10 /usr/lib/libodbcmyS.la
lrwxrwxrwx 1 root root     19 May 31 14:10 /usr/lib/libodbcmyS.so ->
libodbcmyS.so.1.0.0
lrwxrwxrwx 1 root root     19 May 31 14:10 /usr/lib/libodbcmyS.so.1 ->
libodbcmyS.so.1.0.0
-rwxr-xr-x 1 root root   6252 May 31 14:10 /usr/lib/libodbcmyS.so.1.0.0
-rw-r--r-- 1 root root   1350 May 31 14:10 /usr/lib/libodbcnnS.a
-rw-r--r-- 1 root root    819 May 31 14:10 /usr/lib/libodbcnnS.la
lrwxrwxrwx 1 root root     19 May 31 14:10 /usr/lib/libodbcnnS.so ->
libodbcnnS.so.1.0.0
lrwxrwxrwx 1 root root     19 May 31 14:10 /usr/lib/libodbcnnS.so.1 ->
libodbcnnS.so.1.0.0
-rwxr-xr-x 1 root root   3360 May 31 14:10 /usr/lib/libodbcnnS.so.1.0.0
-rw-r--r-- 1 root root 435828 May 31 14:10 /usr/lib/libodbcpsql.a
-rw-r--r-- 1 root root    849 May 31 14:10 /usr/lib/libodbcpsql.la
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbcpsql.so ->
libodbcpsql.so.2.0.0
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbcpsql.so.1
-> libodbcpsql.so.1.0.0
-rwxr-xr-x 1 root root 230644 May 31 14:10 /usr/lib/libodbcpsql.so.1.0.0
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbcpsql.so.2
-> libodbcpsql.so.2.0.0
-rwxr-xr-x 1 root root 246392 May 31 14:10 /usr/lib/libodbcpsql.so.2.0.0
-rw-r--r-- 1 root root   4702 May 31 14:10 /usr/lib/libodbcpsqlS.a
-rw-r--r-- 1 root root    833 May 31 14:10 /usr/lib/libodbcpsqlS.la
lrwxrwxrwx 1 root root     21 May 31 14:10 /usr/lib/libodbcpsqlS.so ->
libodbcpsqlS.so.1.0.0
lrwxrwxrwx 1 root root     21 May 31 14:10 /usr/lib/libodbcpsqlS.so.1
-> libodbcpsqlS.so.1.0.0
-rwxr-xr-x 1 root root   5788 May 31 14:10 /usr/lib/libodbcpsqlS.so.1.0.0
-rw-r--r-- 1 root root 459646 May 31 14:10 /usr/lib/libodbctxt.a
-rw-r--r-- 1 root root    829 May 31 14:10 /usr/lib/libodbctxt.la
lrwxrwxrwx 1 root root     19 May 31 14:10 /usr/lib/libodbctxt.so ->
libodbctxt.so.1.0.0
lrwxrwxrwx 1 root root     19 May 31 14:10 /usr/lib/libodbctxt.so.1 ->
libodbctxt.so.1.0.0
-rwxr-xr-x 1 root root 182920 May 31 14:10 /usr/lib/libodbctxt.so.1.0.0
-rw-r--r-- 1 root root   3470 May 31 14:10 /usr/lib/libodbctxtS.a
-rw-r--r-- 1 root root    826 May 31 14:10 /usr/lib/libodbctxtS.la
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbctxtS.so ->
libodbctxtS.so.1.0.0
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/libodbctxtS.so.1
-> libodbctxtS.so.1.0.0
-rwxr-xr-x 1 root root   4928 May 31 14:10 /usr/lib/libodbctxtS.so.1.0.0
-rw-r--r-- 1 root root   5186 May 31 14:10 /usr/lib/liboplodbcS.a
-rw-r--r-- 1 root root    826 May 31 14:10 /usr/lib/liboplodbcS.la
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/liboplodbcS.so ->
liboplodbcS.so.1.0.0
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/liboplodbcS.so.1
-> liboplodbcS.so.1.0.0
-rwxr-xr-x 1 root root   6328 May 31 14:10 /usr/lib/liboplodbcS.so.1.0.0
-rw-r--r-- 1 root root   2554 May 31 14:10 /usr/lib/liboraodbcS.a
-rw-r--r-- 1 root root    826 May 31 14:10 /usr/lib/liboraodbcS.la
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/liboraodbcS.so ->
liboraodbcS.so.1.0.0
lrwxrwxrwx 1 root root     20 May 31 14:10 /usr/lib/liboraodbcS.so.1
-> liboraodbcS.so.1.0.0
-rwxr-xr-x 1 root root   4232 May 31 14:10 /usr/lib/liboraodbcS.so.1.0.0

...but which one should be used to connect to the MS-Access database?

Can anyone suggest which?

(The example in the mentioned FAQ is connecting to a PostgreSQL
database and I've found a few other example .odbc.ini files on the
net, but none for acessing an MS-Access db)

Reading around at http://www.unixodbc.org/ suggests that a third-party
proprietary driver and ODBC-ODBC bridge/gateway (from
http://www.easysoft.com/) may be required (at a cost of ~�700!!!), and
if this is the case then I shan't bother as that outlay is far too
expensive.

Any other pointers/tips on configuration would be welcome.

Thanks,

Neil

--
"In mathematics you don't understand things. You just get used to
them."  - Johann von Neumann

Email - nshephard@gmail.com / n.shephard@sheffield.ac.uk
Website - http://slack.ser.man.ac.uk/
Photos - http://www.flickr.com/photos/slackline/

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/