CODEWITHSUNDEEP
python-2.7ms-accesshyperlinkms-access-2003pyodbc
trumanhughes
trumanhughes

Reputation: 23

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

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()

Upvotes: 2

Views: 1078

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123809

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()

Upvotes: 1

Related Questions