Access 2007 hyperlink field will not work

Posted on 2009-02-19
Last Modified: 2012-06-27
Hello.  I wrote a program in  VBA that inserts the path to several files into a hyperlink field in a Access 2007 table.  The program works, but the hyperlinks don't.  When I copy one of the paths inserted by the program to another record, it gets pasted with two hash (#) symbols at the end.  If I eliminate the symbols, the hyperlink works without a problem.  Here's an example:

Original hyperlink:


After pasting:


The funny thing is that I don't find the hashes in the paths originally inserted by the program, only after I paste them.  Anything I should implement in the code (see snippet)  that will get the hyperlinks working right away? Thanks!

Dim recSet As ADODB.Recordset

Dim value1 As String

Dim value2 As String

Dim fsObject As Object

Dim myFolder As Object

Dim strSQL As String

Dim fileObj As Object

Sub LinkFile()

    ' Gets path name

     myPath = InputBox("Enter full path.")

     value1 = InputBox("Enter criterion 1.")

     value2 = InputBox("Enter criterion 2.")

    ' Makes a new file system object.

    Set fsObject = CreateObject("Scripting.FileSystemObject")

    ' Gets the FSO Folder (directory) object.

    Set myFolder = fsObject.GetFolder(myPath)

    ' Gets a reference to the current database

    Set conDB = CurrentProject.Connection

    ' Makes the list of file names

    ReDim myFiles(1 To myFolder.Files.Count) As String

    For Each myEachfile In myFolder.Files


	' Gets record set and queries database

        Set recSet = New Recordset

        strSQL = "Select * FROM table WHERE field1='" & value1 & "' AND field2='" & value2 & "';"

        recSet.Open strSQL, conDB, adOpenDynamic, adLockOptimistic

        recSet!HyperlinkField = myEachfile






    Set recSet = Nothing

    Set conDB = Nothing

End Sub

Open in new window

Question by:taioba
    1 Comment
    LVL 58

    Accepted Solution

    A hyperlink in Access is a memo field with special treatment. The link is stored in this format:

        text to display#URL#subaddress

    So you could store this for example:

        link to Myfile.ext#file:///C:/MyFolder/Myfile.ext#

    Good luck!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now