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