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: 
MS Access is a [multi-faceted software](https://meta.stackexchange.com/q/33216). 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](https://learn.microsoft.com/en-us/windows/win32/com/the-component-object-model) (COM) exposes the .exe version of MS Access to use its [object library](https://learn.microsoft.com/en-us/office/vba/api/overview/access/object-model) 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)!
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.