Text inserted into Hyperlink field shows up but does not act as a link

Question

I'm having difficulty getting my pyodbc inserted hyperlinks to work in my Access 2003 database. It appears to look like a hyperlink but does nothing when clicked on. For it to work, I have to edit it in Access and only then does it recognize that, "oh yeah that is a hyperlink".

import pyodbc

cnxn = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb)};DBQ= C:\\Users\\multidata\\Documents\\db1.mdb;")

cur = cnxn.cursor()
#hyperlink is the text file. table1 is hyperlink column in ms access
cur.execute("INSERT INTO test(table1, table2) values ('C:\\Users\\multidata\\Desktop\\MC1\\7-31-14_711_EX_2153.txt ', 'y')")
cnxn.commit()
cnxn.close()

Answer 1

A Hyperlink field in Access is a text field containing a number of "parts" separated by hash marks (#). Those various parts are described in the MSDN article here.

If we want to insert a bare URL or file_path into a Hyperlink field we need to enclose it in hash marks, e.g.

import pyodbc
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb)};'
    r'DBQ=C:\Users\Public\a2003test.mdb;'
)
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
hyperlink = r'C:\Users\Gord\Desktop\foo.txt'
sql = "UPDATE Table1 SET docLink=? WHERE ID=1"
crsr.execute(sql, ['#'+hyperlink+'#'])
cnxn.commit()
crsr.close()
cnxn.close()