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: 
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

```none
id  txt
--  -------
 1  awesome

```

and a VBA module containing the function

```vb
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:

```py
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.
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)!