Connect R to Filemaker Pro 15 on Mac
I am trying to create a connection between R (3.3.3). Using RStudio (1.0.143) and Filemaker Pro Advanced 15 (15.0.3.305). I am trying to create a connection using RODBC (1.3-15).
So far I have:
FM Pro toys database created for testing
- User ID: Admin
- Password: password
Follow these instructions to create a DSN
Created a DSN for my FM Pro toy database called test_r
Successfully tested connecting to test_r
Failed to try to connect to DSN in RStudio in two ways:
fm_connection <- odbcConnect(dsn="test_r", uid="Admin", pwd="password")
Returns the following error:
[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specifiedODBC connection failed
and
constr <- paste("driver={FileMaker ODBC}",
"server=127.0.0.1",
"database=test_r",
"uid=Admin",
"pwd=password",
sep=";")
fm_connection <- odbcDriverConnect(constr)
Returns the following error:
[RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib 'FileMaker ODBC' : file not foundODBC connection failed
However, you can see that the driver is there: 
Finally, I tried unsuccessfully to use these (and other) links to solve this problem:
- https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
- https://community.filemaker.com/thread/165849
Nothing works yet. I am not tied to RODBC, but I need a solution that works for Mac OS . Any help is appreciated!
source to share
I got this to work with odbc instead of RODBC with the new R code:
con <- DBI::dbConnect(odbc::odbc(),
driver = "/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/FileMaker ODBC",
server = "127.0.0.1",
database = "/Users/bradcannell/Dropbox/Filemaker Pro/Notes/test_r.fmp12",
uid = "Admin",
pwd = "password")
source to share
Here are some troubleshooting examples for macOS. I had the same error in R and so I think you have a good chance that this is your problem. Configuring ODBC can be quite complex as there are multiple software components with multiple versions involved. You have already confirmed that ODBC Sharing is included in this particular FileMaker database.
Check your unixodbc installation:
The ODBC manager is actually optional. It manages the ini files described below. But after installing unixodbc, you can also edit these ini files in a text editor without ODBC Manager.
To install Homebrew, run this command.
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Then install unixodbc. This provides system-level ODBC connectivity.
brew update brew install unixodbc
Check driver:
The driver must be installed here:
/Library/ODBC/FileMaker\ ODBC.bundle/Contents/MacOS
This folder will contain these two files:
SetupToolTemplate fmodbc.so
Here's the full path to the driver:
/Library/ODBC/FileMaker\ ODBC.bundle/Contents/MacOS/fmodbc.so
Check config files:
Folder
/Library/ODBC
should contain these files:
FileMaker ODBC.bundle odbc.ini odbcinst.ini
Also, unixodbc should only contain the latest version. If you have an earlier version, please uninstall it. Now only 2.3.4 is present.
/usr/local/Cellar/unixodbc/2.3.4
also contains
odbc.ini odbcinst.ini
Flip files odbc.ini and odbcinst.ini:
As described above, there are two copies of each of these files in two different locations. Make sure the content of both is equal. This means that both copies of odbcinst.ini will identify drivers. And both copies of odbc.ini will contain connections. It may not be 100% necessary, but this is what I needed to do.
Test with isql:
B:etc bobby$ isql -v DSNname admin password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
If you still have problems after following these steps, please submit more information so that I can update the answer.
source to share