Access a Microsoft SQL by R

Photo by Rubaitul Azad on Unsplash

Purpose

I’m working in a hospital. 5 years ago, I established a monthly report based on a data export from our electronic health record (EHR). This report based on R and running on shiny apps.io gives us information about the performance of every department in our hospital. The export of the data is very time consuming, therefore I make it once per month. For better control of our performance, we needed a system which delivers data on a daily base. Therefore, we connected the EHR data to a MS SQL database. For further use of R, I established a connection to the SQL database. This task was difficult.

Load driver

First step is loading an ODBC-driver. An Instruction is available at:

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16

The installation uses home-brew packet-manger for Mac. First, you must install home-brew if not already done.

`/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/mas
ter/install.sh)"`

Then you can install the ODBC-driver:

`brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release`
`brew update`
`HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18…