Generating Sharepoint-friendly URLs (links) using Microsoft Access

This is damned obscure, but it's not documented and I figured it out, so I'll stick it up here.

Maybe it will help someone.

Microsoft Excel, Access, and Sharepoint  2007 (latter is really SQL Server dressed up) all have an implied datatype called the "hyperlink". It's not well documented, I don't think Excel 2007 and Access 2007 implement the hyperlink quite the same way.

Excel has a "hyperlink" function that will generate a hyperlink from a string and a URL, but when I pasted that into a hyperlink field in SP 2007 bad things happened and I had to power cycle by XP box. (typical XP behavior)

On the other hand I couldn't figure out how to generate hyperlinks in Access 2003 or 2007. Here's the trick, and they do work in SP 2007. I assume one column holds the URL and the other the text displayed in the UI.

1. Concatenate URL and text fields (+ operator in Access) to a string of this sort: "friendly name#http://myserver.org/friend.html#"

2. Write out the results to a table.

3. Change the datatype of the column containing "friendly name#http://myserver.org/friend.html#" to "HYPERTEXT".

Now you have the hypertext URLs.

Link to your target SP 2007 list (table really), and join on your identifier, then do an Update query to stuff your hypertext URLs into the appropriate Sharepoint field.

I'm sure there's a better way using VisualBasic for Access, but Microsoft's approach to VBA gives me hives. It's a total mess, I'd much rather spend time learning Objective C or Python.