Can't open MS ACESS Database with ADOBDAPI

Question

When I try to connect to an MS-Access database with Python ADODBAPI library, I have an error message.

I have tried to use the Microsoft.ACE.OLEDB.12.0 provider but it's the same thing. I can open the database with the Microsoft.Jet.OLEDB.4.0 provider in C# on the same computer.

# -*- coding:Utf-8 -*-

import sys
import adodbapi
import argparse

__version__ = 1.0
debugmode = False

# Constantes de connection sur la base de donnees.

MDB = 'D:\lp_2012_3.mdb'
DRV = 'Microsoft.Jet.OLEDB.4.0'
PWD = ''
conn_string = r'Provider=%s; Data Source=%s' % (DRV,MDB)

def main():

    #Connection a la base de donnees.
    print('%s'%conn_string)
    conn = adodbapi.connect(conn_string)
    conn.autocommit = False
    cur = conn.cursor()

    #Definition de la requete
    SQLselect = 'SELECT TOP 10 * FROM PERSONNE;'
    # Lancement de la requete 
    record = cur.execute(SQLselect).fetchall()
    #print('%s'%(type(cur.description)))
    #Get columns name
    entete = []
    for fld in cur.columns('PERSONNE'):
        entete.append(fld.column_name)
        #print(fld.table_name, fld.column_name)
    #print('%s'%(entete))

    cur.close()
    conn.close()

if (__name__ == '__main__'):
    #Recupere les arguments transmis
    parser = argparse.ArgumentParser()
    parser.add_argument('-v', '--verbose', action = 'store_true', help = 'augmente la verbosité')
    parser.add_argument('-l', '--log', action = 'store_true', help = 'active le mode log')
    parser.add_argument('-d', '--debug', action='store_true', help='debug mode')
    args = parser.parse_args()
    #applique la configuration
    if args.debug:
        debugmode = True
    sys.exit(main())

I have the following error code :

adodbapi.apibase.OperationalError: (InterfaceError("Windows COM Error: >Dispatch('ADODB.Connection') failed."), 'Error opening connection to >"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\lp_2012_3.mdb"')

Thanks for your help.

PS : Some people are still using MS-ACCESS.

First, avoid using the `%` operator for string interpolation as it is [unofficially deprecated](https://stackoverflow.com/a/13452357/1422451) for `str.format()`. Try to double the backslash in file path, switch to forward slash, or use raw string: `r"D:\lp_2012_3.mdb"`. Try also ACE version: `Microsoft.ACE.OLEDB.12.0`. — Parfait, Jul 17 '19 at 14:24
Thanks for your comments. So I tried the ACE provider and it's the same error adodbapi.apibase.OperationalError: (InterfaceError("Windows COM Error: Dispatch('ADODB.Connection') failed."), 'Error opening connection to "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\lp_2012.mdb"') — Foxrider83, Jul 18 '19 at 04:57
Check if you have any of these providers installed by running this [Powershell script](https://pastebin.com/3QDna702). How to run? Search and open "Windows PowerShell ISE" (x86 for 32-bit and non-x86 for 64-bit) and copy/paste code in a new script then click run. See [docs](https://learn.microsoft.com/en-us/powershell/scripting/components/ise/how-to-write-and-run-scripts-in-the-windows-powershell-ise?view=powershell-6). — Parfait, Jul 18 '19 at 15:15
Thanks for your answer. This PS Script show me Microsoft.ACE.OLEDB.16.0 and Microsoft.ACE.OLEDB.12.0 providers. So I don't understand why the Microsoft.ACE.OLEDB.12.0 provider return me an error. — Foxrider83, Jul 19 '19 at 08:57
Check if your Python version is running 32 or 64-bit. If the former, you must use 32-bit OLEDB providers, same for 64-bit. Also, your connection assumes the database is not password-protected and its directory is writeable. — Parfait, Jul 19 '19 at 12:36

score 0 · Answer 1 · answered Aug 29 '19 at 16:07

If it is possible to use a different module...

I found the pyodbc module much less problematic compared to the adodbapi module after using both for a recent project. pyodbc is currently still being maintained (unlike adodbapi, I have been told) and there is much better documentation for it.

If you need to do parameterized queries make sure to use version 4.0.27 or later, as discussed here https://github.com/mkleehammer/pyodbc/issues/509.

I have been successful connecting to MS Access 2016 with the 64 bit ACE driver (once properly installed) as follows:

connectionString = (
    f'Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={database_path};'
)
connection = odbc.connect(connectionString, autocommit = True)

The double brackets are to escape the needed brackets around the driver name.

Can't open MS ACESS Database with ADOBDAPI

1 Answers1