Can MS-Access auto-hyperlink words ala a Wiki?

Factual Questions
1

I have an Access database that includes some large(ish) text fields. In a Report I’d like Access to scan the text and hyperlink any word(s) that we have an entry for in another Table.

So say it says, “The Financial Services Authority (FSA) said that it could veto American efforts to impose regulation on the London oil market, as the City took center stage in an escalating row over the role of speculators in driving up the global price of crude oil.”

I’d like Access to output, "The Financial Services Authority (FSA) said that it could veto American efforts to impose regulation on the London oil market, as the City took center stage in an escalating row over the role of speculators in driving up the global price of crude oil. " (note the hyperlink)

Is there any way to do this?

If the answer is too complex for here if someone could point me to a website that explains it or a Google search that will get me there I’d appreciate it (my Google-Fu is failing me on this particular problem).

2

I’d be extremely surprised if this feature exists. Note that Wikipedia links are generated by users, not any program on their end.

3

Hmm…I am not very good with Access but I am good with VBScript/ASP so maybe I can get you started?

I would do it something like this…




ReportText = ReportTable.TextField

For Each Row In PhrasesTable.GetRows()
        ReportText = Replace(ReportText, [Phrase], "<a href=" &  & ">" & [Phrase] & "</a>"
MoveNext()
Loop

Oh man…it seems that I’ve been doing so much C# lately that I have completely lost my mind when it comes to VB. Does that help at all? The syntax is horrible but the general idea is there (Replace(string, original, new) IS a real VB function…)

4

I am not good enough at VB to know just looking at that if it’d work but it certainly helps as a starting place. Thanks!

5

Yeah sorry about that.

Basically the idea is…

  1. Get your text field (the one with the text you want to hyperlink) and set it into a variable.
  2. Get a recordset that contains rows of all the possible phrases and their corresponding links (2 columns)
  3. Loop through the recordset and, for each record, run Replace(TextField,Phrase,FormattedPhraseWithHyperlink) on your TextField variable.

Here’s the W3Schools explanation of the Replace Function. You can also learn a little more about VBScript from the links on the left.