odbc load can't find data source

[Austin Bean]

Hi All -

I've been trying to query an ODBC (.accdb) file via Stata 16 on a Mac. (This is for a local file.)

- I've tried to follow these steps: https://www.stata.com/support/faqs/d...figuring-odbc/

- I've set up the data source using both ODBC manager and iODBC Adminstrator64. The latter program will test a connection, and reports that it is working.

- I've looked at the odbc.ini file in the /Library/ folder and set it to resemble the sample at the link above.

- odbc.ini looks like what follows (with filenames and paths removed, but they are correct in the actual file)

[ODBC Data Sources]
database_name = Actual Access

[database_name]
Driver  = /...path to driver.../ataccess.so
DBQ = /...path to data source .../filename.accdb
CacheResults = No
ConnectionType = Direct
Host = localhost

but

 odbc list

does not return the data source (this is despite a few restarts).

Is there another way to configure access to the file from within Stata itself?

[Duc Ngo]

Hello,

i have exactly the same problem as you. I had contacted STATA for support, however, their solution didnt work at all.

Basically they told me to

set odbcmgr iodbc, perm 
odbc list
set odbcdriver ansi, perm 
odbc list

[Duc Ngo]

Halo,
I happend to be able to solve the issue.

Solution: Copy the odbc.ini from /Library/ODBC/ to /Users/<username>/Library/ODBC/
alternatively: Create the DSN under User DSN

please try and let me know.

Regards

Duc

[Tyson Van Alfen]

I also tried to follow the documentation Austin Bean mentioned and had similar difficulties. Here is what I eventually did to get ODBC working on Mac (Catalina) with Stata 16.

• Download and install the iODBC Administrator64 app (mxkozzzz.dmg from iodbc.org)
• Create a file named odbc.ini and place it in /Users/<username>/Library/ODBC/ like Duc Ngo mentions (not /Library/ODBC/). My odbc.ini file looks like this:

[ODBC Data Sources]
YourDataName = SQLite3 Driver

[YourDataName]
Driver         = /usr/local/lib/libsqlite3odbc.dylib
Description    = Add a description if you prefer.
database       = /Users/<username>/your/path/to/data.sqlite
CacheResults   = No
ConnectionType = Direct
Host           = localhost

• Create a file named odbcinst.ini and place it in the same directory as odbc.ini. My odbcinst.ini file looks like this:

[ODBC Drivers]
SQLite3 Driver = Installed

[SQLite3 Driver]
Driver = /usr/local/lib/libsqlite3odbc.dylib
Setup  = /usr/local/lib/libsqlite3odbc.dylib

• Obviously, you will need to point your files to your data and the driver you want to use. While attempting to get this working, I downloaded and installed an SQLite driver: sqliteodbc-0.9994.dmg from http://www.ch-werner.de/sqliteodbc/. In hindsight, I'm not sure if this was necessary since the driver I'm using may have already been installed.
• Once those two files have been created and saved, you should see YourDataName under User DSN in the iODBC Administrator64 app. You should also see your driver listed under ODBC Drivers in the iODBC Administrator64 app.
• I also ran the following commands in Stata, as Duc Ngo mentions, but again, I'm not sure if they are necessary or not.

set odbcmgr iodbc, perm
odbc list
set odbcdriver ansi, perm
odbc list

This was enough to get ODBC working in Stata for me, however, I still don't fully understand the process, so additional comments are welcome. It is also possible that some of what I suggested is unnecessary. But once it is working you should see YourDataName after running odbc list in Stata.

Some additional resources:

• https://github.com/softace/sqliteodbc (The bottom of the README briefly discusses .ini files for Mac.)
• https://www.stata.com/support/faqs/d...figuring-odbc/