Microsoft Access Odbc Driver Mac

Posted on 20-05-2021 by admin

The following instructions assume you already have a SQL Server database running somewhere that your Mac has network access to. Just FYI, Microsoft's instructions for installing the latest drivers are here.

Hi, I am developing a Excel VBA application using a.xlsx workbook as database. This database is addressed by SQL queries through an ActualTechnologies Access ODBC driver. This worked very well, given. On the Data tab, click New Database Query SQL Server ODBC. Then use the dialog boxes to import the data. If you are connecting to other ODBC data sources (for example, FileMaker Pro), then you'll need to install the ODBC driver for the data source on your Mac. Drivers that are compatible with Excel for Mac are available from these companies.

Install FreeTDS and unixODBC

The connection to SQL Server will be made using the unixODBC driver manager and the FreeTDS driver. Installing them is most easily done using homebrew, the Mac package manager:

Edit the freetds.conf configuration file

Ensure the freetds.conf file is located in directory /usr/local/etc/, which will be a symlink to the actual file as installed by Homebrew. Check the specific location of the freetds.conf file by running tsql -C. The default file already contains a standard example configuration, but all you need to do is add your server information to the end, as follows:

There are other key/value pairs that can be added but this shouldn't usually be necessary, see here for details. The host parameter should be either the network name (or IP address) of the database server, or 'localhost' if SQL Server is running directly on your Mac (e.g. using Docker). A TDS version of 7.3 should be OK for SQL Server 2008 and newer, but bear in mind you might need a different value for older versions of SQL Server. For more information on TDS protocol versions see Choosing a TDS protocol version. Do not use TDS versions 8.0 or 9.0 though. Oddly, they are not newer than version 7.4. They are actually obsolete aliases for older TDS versions and their use is discouraged.

Test the connection using the tsql utility, e.g. tsql -S MYMSSQL -U myuser -P mypassword. If this works, you should see the following:

At this point you can run SQL queries, e.g. 'SELECT @@VERSION' but you'll need to enter 'GO' on a separate line to actually execute the query. Type exit to get out of the interactive session.

Edit the odbcinst.ini and odbc.ini configuration files

Run odbcinst -j to get the location of the odbcinst.ini and odbc.ini files (probably in the directory /usr/local/etc/). Edit odbcinst.ini to include the following: https://flashomg.netlify.app/is-mac-microsoft-office-different-than-pc.html.

Free microsoft word for mac for students. Find out what savings your school is offering with our! We carry software that are essential for students – including SPSS, Microsoft, and more – for up to 90% off retail price! Is it true I can download free, software?

Edit odbc.ini to include the following:

Note, the 'Driver' is the name of the entry in odbcinst.ini, and the 'Servername' is the name of the entry in freetds.conf (not a network name). There are other key/value pairs that can be included, see here for details.

Check that all is OK by running isql MYMSSQL myuser mypassword. You should see the following:

You can enter SQL queries at this point if you like. Type quit to exit the interactive session.

Connect with pyodbc

It should now be possible to connect to your SQL Server database using pyodbc, for example:

Connecting without defining a DSN

If you don't want to define a DSN in odbc.ini, you can reference the driver entry you added to odbcinst.ini.

E.g.:

Note: in this case you may need to specify all necessary TDS parameters in pyodbc.connect.

Connecting without modifying `odbcinst.ini` or `odbc.ini`

If you want to avoid modifying both odbc.ini and odbcinst.ini, you can just specify the driver file location in the driver param in pyodbc.connect.

E.g.:

Microsoft only produces Access ODBC drivers for the Windows platform. Third-party vendors may be able to provide Access ODBC drivers for non-Windows platforms. (This Stack Overflow answer also describes options for connecting to an Access database from Python on non-Windows platforms, but they do not involve pyodbc or ODBC.)

There are actually two (2) different Access ODBC drivers from Microsoft:

Ms Access Odbc Driver Mac

Microsoft Access Driver (*.mdb) - This is the older 32-bit 'Jet' ODBC driver. It is included as a standard part of a Windows install. It only works with .mdb (not .accdb) files. It is also officially deprecated.
Microsoft Access Driver (*.mdb, *.accdb) - This is the newer 'ACE' ODBC driver. It is not included with Windows, but it is normally included as part of a Microsoft Office install. It is also available as a free stand-alone 'redistributable' installer for machines without Microsoft Office. There are separate 64-bit and 32-bit versions of the 'ACE' Access Database Engine (and drivers), and normally one has either the 64-bit version or the 32-bit version installed. (It is possible to force both versions to exist on the same machine but it is not recommended as it can 'break' Office installations. Therefore, if you already have Microsoft Office it is highly recommended that you use a Python environment that matches the 'bitness' of the Office install.)

The easiest way to check if one of the Microsoft Access ODBC drivers is available to your Python environment (on Windows) is to do

If you see an empty list then you are running 64-bit Python and you need to install the 64-bit version of the 'ACE' driver. If you only see ['Microsoft Access Driver (*.mdb)'] and you need to work with an .accdb file then you need to install the 32-bit version of the 'ACE' driver.

Here is an example of how to open an MS Access database: