Using Pyodbc to connect to an MS Access file with a User-Defined Function

Question

I am trying to connect python using pyodbc to an MS Access Database that has a User-Defined Function(UDF).

The Database has many queries in it, which takes advantage of the UDF. I wanted the results of one such query in python so I went ahead using pyodbc. Table1_Q2 is a query in the Access Database and Arc is a UDF in the Access Database.

I used pyodbc to get all the values in from a query present in Access DB. So, I used this SQL Query in Python to Select all the values from the Query (Table1_Q2) in the Access DB. I get the following error

Execution failed on sql 'SELECT * FROM Table1_Q2': ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Arc' in expression. (-3102) (SQLExecDirectW)")

The query seems to be working fine when I ran it from Access DB. But when I use Pyodbc to connect, it fails to recognize those queries that take advantage of the UDF. I am able to access other Tables that don't depend on the UDF using Pyodbc.

Here is a code snippet:

filepath = os.path.abspath('')+'\Database1.accdb'
myDataSources = pyodbc.dataSources()
# Establishing connection to Access DB
driver = myDataSources['MS Access Database']
cnxn = pyodbc.connect(driver=driver, dbq = filepath, autocommit=True)
crsr = cnxn.cursor()
table_name = 'Table1_Q2'
query = "SELECT * FROM {}".format(table_name)
source_df2 = pandas.read_sql(query, cnxn)
cnxn.close()

Is there something to be added to the code so as to include the UDF in the Access DB as well.

Answer 1

You cannot use pyodbc and Access ODBC to run queries that involve user-defined functions. However, if you have the full Microsoft Access application installed then your Python app can use COM automation to spin up an instance of Access and run the query that way.

Example: For an Access database containing a table named Table1

id  txt
--  -------
 1  awesome

and a VBA module containing the function

Public Function ultra(s As String) As String
    ultra = "ultra_" & s
End Function

we can run a query like SELECT ultra(txt) AS u_text FROM Table1 … as follows:

import win32com.client  # needs `pip install pywin32`

# ACE.DAO constants
dbOpenDynaset = 2

db_path = r"C:\Users\Public\database1.accdb"
sql = "SELECT ultra(txt) AS u_text FROM Table1 WHERE id = 1"

obj_access = win32com.client.gencache.EnsureDispatch("Access.Application")
obj_access.OpenCurrentDatabase(db_path)
db = obj_access.CurrentDb()
try:
    rs = db.OpenRecordset(sql, dbOpenDynaset)
    print(rs.Fields["u_text"].Value)  # ultra_awesome
except Exception as e:
    print(e)
finally:
    rs.Close()
    obj_access.Quit()

Notes:

1. This does not work with the Microsoft Access Runtime, only with the full Microsoft Access product.
2. This has not been tested with "click to run" installs of Office/Access.

Answer 2

MS Access is a multi-faceted software. While it is a GUI .exe application with a host of components including forms, reports, macros, and modules, it is also a database (or data store) of tables and saved queries accessible via ODBC or OLEDB in other programming languages. Over the years, by convenience, "MS Access" has been conflated to mean both the application and database.

When connecting Python (or other language) to an Access database via ODBC, you only access the underlying database of tables and stored queries. In fact, you do not even need the full .exe installed to connect just have the ODBC driver (or OLEDB provider) installed. Therefore, a user-defined function saved in a standalone VBA module is not accessible. So attempting to use it in an SQL statement or stored query will raise errors. The counterpart of UDF in databases would be stored procedures or functions, currently not available in Access databases.

As mentioned, an alternative technology, Component Object Model (COM) exposes the .exe version of MS Access to use its object library which includes all components (tables, queries, forms, reports, macros, and modules). In fact, VBA and the Access object library are two default external references in Access .mdb and .accdb projects! Maybe in future versions, VBA can be swapped out to write modules in Python (even then still not accessible by ODBC)!

Answer 3

The Microsoft Access database engines are linked to VB, and can use VB words in queries. VB was designed as an extensible language, and when it is loaded it hooks into the parent application, extending the language to include words from the parent.

When Microsoft Access is the parent, it extends VB by loading UDF's as extensions to VB, and the UDF's can be used in queries.

When Python/ODBC is the parent, it does not extend VB by loading UDF's.

There was, at one time, a development kit for VBA, that allowed you to build a parent VB application, but that was withdrawn years ago: it hasn't been sold for ~ 20 years. The storage format for UDF's in a database is sort of indirectly documented: it's a OLE object stored in the database. But without the Visual Basic for Applications extensibility API, there is no way to link the UDF's into Python or ODBC.

ODBC ('Open DataBase Connectivity') is a common generic SQL standard and driver interface that doesn't include VBA, so even in Microsoft Access, queries that include VBA are handled first and last by Microsoft Access, with ODBC SQL fixed up by Microsoft Access, sent off to an ODBC driver, then fixed up again with any UDF calls, before the data is returned to you.