Changes in MySQL Connector/ODBC 5.3.14 (2019-10-30, General Availability) Changes in MySQL Connector/ODBC 5.3.13 (2019-04-29, General Availability). ODBC/JDBC Drivers - pre-version 19 (make selection from Product dropdown) Utilities. BBj Product Family Utilities Pack (.tar.Z) (4.56Mb) BBj Product Family Utilities Pack (.exe) (3.36Mb) Visual PRO/5 & PRO/5 Product Family Utilities Pack (.tar.Z) (2.67Mb) Visual PRO/5 & PRO/5 Product Family Utilities Pack (.exe) (2.15Mb) Each set includes. Microsoft ODBC Driver for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to SQL Server. Use Microsoft ODBC Driver 17 for SQL Server to create new applications or enhance existing applications that need to take advantage of newer SQL Server features.
These article describes how to connect to a Microsoft Access database file, if you use a MS Windows operating system.
Preconditions in Windows
This description has been tested on WinXP Home, for other Windows versions it might work a little differently.
The file type MDB has been used by versions of Microsoft Access, from Access 97 (Office 8) up to Access 2003 (Office 11). It can be handled with the database engine “Jet 4.0”. Starting with MS Access 2007 (Office 12), Microsoft Access has begun to use the new file type ACCDB. The latter uses a modified Jet database engine, called “Access Database Engine” (ACE).
The two versions (the former and the latter) are not compatible. On one hand ACCDB doesn't know user based security, on the other hand you can work with attachments inside the database file, for example.
Both versions can be controlled with “ODBC driver” or with “ OLE DB Provider”. Older MS Windows versions (e.g. Win98) might not include all needed files for handling MDB-files. You can download missing files from http://msdn2.microsoft.com/en-us/data/aa937730.aspx. You might need “Jet 4.0 Service Pack” and “Microsoft Data Access Components (MDAC)”. WinXP includes this files already. For ACCDB files you might need “ 2007 Office System Driver: Data Connectivity Components”. Searching for AccessDatabaseEngine (without spaces) in the Microsoft download center will lead you to the download link. Even in WinXP those file are not included automatically.
Kind of Connection (Wizard Step 1)
Start wizard with File ▸ New ▸ Database or use item LibreOffice Base from Windows (German) Startmenü, or use item Database in LibreOffice Quickstarter.
In the first step select Connect to an existing database. Open the drop-down list. You can use Microsoft Access, ADO, or ODBC. The next steps depend on the chosen kind of connection. When connection is finished, you see your chosen connection on the left side in the status bar.
Setup Connection (Wizard Step 2)
Microsoft Access
- File type MDB
- Choose the MDB-Datei on the second page of the Wizard. OOo uses a ADO-connection where all other parameters are set with default values. If your database is protected by password, you cannot use this method.
- File type ACCDB
- The simplified handling of the option Microsoft Access is not yet available for the ACCDB format.
ADO
Enter the Datasource URL on the second page of the wizard. That doesn't mean the path and filename of your database file, but it is the so called “connection string”.
Generating the Connection String
If you don't know the connection string, click on Browse. This opens the “ Universal Data Link (UDL) Wizard” of Windows. You see a dialog with the title Data Link Properties. The dialog pages Provider, Connections, Advanced, and All can be used in any order.When you close the dialog, the field Data source URL contains the constructed connection string.
Dialog Page Provider
- File type MDB
- Choose Microsoft Jet 4.0 OLE DB Driver
- File type ACCDB
- Choose Microsoft Office 12.0 Access Database Engine OLE DB Provider
Dialog Page Connections
- File type MDB
- Enter absolute path and file name of your MDB-file or click on the …-button to use a file picker to choose your file.
- If you have got a users administration for your database, enter the user name here, otherwise leave the default Admin unchanged. Let the option Blank Password unchanged too, password setting doesn't work here.
- File type ACCDB
- Enter absolute path and file name of your ACCDB-file here. A file picker is not available. The file type ACCDB doesn't know user administration, leave the default settings Admin and Blank Password unchanged.
Dialog Page Advanced
On the page Advanced you can constraint the access to the database. The default read/write is used without being checked.
Dialog Page All
Here you can set additional initializations for the OLE DB provider. Look at Microsoft site for further information.
Examples:
- If your Access database file has been given a password, you must enter it here. Choose the item Jet OLEDB Database Password and click on Edit Value. Enter the password there.
- For a MDB-file the item JetOLEDB:System database contains path and name of the workgroup information file. Default in WinXP Home is
c:WindowsSystem32system.mdw. You must enter it, if not “Admin” but another user is going to access the database.
At last go to the page Connections and test the connection. If you get an error message, you have to remove the error, otherwise no connection is possible. Finish the UDL-wizard with OK.
Now you are inside OOo again. Clicking on Next>> leads you to Setup User Authentication.
ODBC
On the second page of OOo database wizard enter the name of the ODBC data source. With Browse you get a list of all existing ODBC data sources.
Registering an ODBC Data Source
If you haven't register your Access database as ODBC data source in Windows, click on Browse and then on Organize… You come into the Microsoft dialog „ODBC Data Source Administrator“. On WinXP you can run the same dialog via Control Panel → Administrative Tools → Data Sources (ODBC) previously.
Choose one of the DSN-tabs and click on (German) Hinzufügen. Use Microsoft help to decide, which one is correct for you. If you will connect with a File DSN, you cannot use it directly, but you need an additional ADO connection string. The database tables will be readonly in this case.
- File type MDB
- Choose one of the Microsoft Access Driver and click on (German) Fertig stellen.
- File type ACCDB
- Choose Microsoft Access Driver (*.mdb *.accdb) and click on (German) Fertig stellen.
On the next window (German) ODBC-Setup für Microsoft Access specify the data source name. That identifier will be shown in the selection list later on.
Clicking on (German) Auswählen opens a file picker, where you can select your database file.
- File type MDB
- If you have got an user administration for your database, and not Admin but another user is going to access the database, check the option (German) Datenbank in the section (German) Systemdatenbank. The button (German) Systemdatenbank… opens a file picker. In WinXP Home the default file to choose is
c:WindowsSystem32system.mdw.
If your Access database file is password protected, you can click on (German) Erweitert and enter the password there. But then the password is stored readable in the ODBC information in the registry, therefore it is no good idea. Leave the field (German) Benutzername empty.
You can find further settings in (German) Erweitert and (German) Optionen>>. For example you can check opening (German) exclusive there, however then you will be unable to hide tables in OOo.
Leave the dialogs with OK until you get the OOo dialog Data Source. The new data source should be included in the list. Choose it and click on OK. Next>>.
ODBC File DSN
Choose connection type ADO and click on Browse to generate the connection string.
- Dialog page (German) Provider
- Choose 'Microsoft OLE DB Provider for ODBC Drivers'.
- Dialog page (German) Verbindung
- Choose (German) Verbindungszeichenfolge verwenden. Click on (German) Erstellen, mark the File DSN in the provided list and click OK, OK.
If the Access database file needs a password, you must enter it then.
Test the connection, OK.
Follow the description for ODBC in Wizard Step 3.
Setup User Authentication (Wizard Step 3)
ADO
Leave the fields empty. Test the connection. Next>>
ODBC
If your database is password protected and you haven't enter the password in the ODBC informations, check Password required.
- File type MDB
- If not Admin but another user shall work with the database, enter the user name here, otherwise leave the field empty.
- File type ACCDB
- The file type doesn't know user management, leave the field User name empty.
Test the connection. If necessary enter the password now. Next>>
Save and Proceed (Wizard Step 4)
If you want to use the data source in other office modules, for example for mail merge, you must check the option Yes, register the database for me. After click on Finish you determine file name and folder to save for the Open Document Base file. Now you can work with the database.
Properties of the Connections
Access to the Tables
- File type MDB
- You can alter the content of the table, add and remove records.
- File type ACCDB
- In an ADO connection you can only read the tables but not write them. It is not sure whether this is fault of OOo. In an ODBC connection you have got read and write access to the tables.
You are able to define new tables. However in both connection types you cannot alter the structure of existing tables.
MS queries are handled as views in OOo and shown in the table container.
See also
MSDN Office Development http://msdn2.microsoft.com/en-us/library/ms400535.aspx
Autor: Regina 14 September 2007 (original at openoffice.org)
Please don't hesitate to improve this guide and adapt it to new versions.
ODBC stands for Open Database Connectivity, the industry standard for database C APIs.
Most databases ship with ODBC drivers, so chances are high that you can use one of these drivers together with a Python ODBC interface to connect your Python application with any database on the market.
Since all ODBC Python interfaces need ODBC drivers to connect to the databases, we're hosting a list of ODBC drivers on a separate page.
DB API 2.0 Drivers
mxODBC
http://www.egenix.com/products/python/mxODBC/
Commercially supported fully DB-API 2.0 compliant ODBC database interface from eGenix.com; actively maintained since 1997.
mxODBC comes with full support for stored procedures, multiple result sets, Unicode, a common interface on all platforms and many other useful features.
Supports Windows, Mac OS X, iODBC, unixODBC and DataDirect ODBC driver managers. Is known to work with these ODBC drivers: MS SQL Server Native Client, MS SQL Server ODBC Driver, FreeTDS ODBC Driver, Oracle Instant Client ODBC Driver, IBM DB2 ODBC Driver, Sybase ASE ODBC Driver, Netezza ODBC Driver, Teradata ODBC Driver, PostgreSQL ODBC Driver, MySQL ODBC Driver, .MaxDB ODBC Driver as well as the ODBC driver sets of EasySoft, DataDirect, OpenLink, Actual Technologies.
pyodbc
https://github.com/mkleehammer/pyodbc
https://github.com/mkleehammer/pyodbc/wiki
Actively maintained Open Source project.
Precompiled binary wheels are available for Windows and macOS for Python 2.7 and 3.4+, installable using pip install pyodbc. RedHat Enterprise Linux, Centos, and Fedora have precompiled RPMs available in their Extras repositories.
turbodbc
https://github.com/blue-yonder/turbodbc
Actively maintained Open Source project.
Turbodbc offers turbocharged database access for data scientists. It heavily relies on buffered I/O for maximum performance, and comes with built-in (optional) NumPy support.
ceODBC
http://ceodbc.sourceforge.net
mxODBC Connect
http://www.egenix.com/products/python/mxODBCConnect/
mxODBC Connect is a commercial client-server product that allows connecting Python to ODBC compatible databases running on remote servers without requiring an ODBC driver on the client side. The product uses mxODBC on the server side and provides a highly portable Python library for the client side. As such it supports all database backend that mxODBC supports, but allows connecting to these from many different Python-supported platforms.
mxODBC Connect supports asynchronous query execution via the popular gevent package, provides secure certificate based authentication, SSL encrypted database connections, comes with full support for stored procedures, multiple result sets, Unicode, a common interface on all platforms and implements many other useful features.
For ODBC drivers supported on the server side, please see the mxODBC entry.
ODBTPAPI
http://benjiyork.com/odbtp.html
PyPyODBC (Pure Python)
https://github.com/jiangwen365/pypyodbc
A Hello World script of pypyodbc database programing
Built-in Access MDB file creation and compression functions on Windows.
A DBI 2.0 SQLAlchemy enabler driver for IronPythonAnd PyPy
One pure Python script, runs on CPython / IronPython / PyPy , Version 2.4 / 2.5 / 2.6 / 2.7 / 3.2 / 3.3, Win / Linux , 32 / 64 bit.
Almost totally same usage as pyodbc ( can be seen as a re-implementation of pyodbc in pure Python ).
Simple - the whole module is implemented in a single python script with less than 3000 lines.
DB API 1.0 Drivers
win32 odbc module (part of pywin32 package)
Actual Odbc Driver Pack 3.25
http://python.net/crew/mhammond/win32/
Odbc Sql Driver Download 17.2
This interface is rather old, mostly unmaintained and only provides an DB-API 1.0 interface, but listed here since it started the Python DB-API specification development back in 1996.