Storing URLs

from the Artful SQL Server & Access Tips List

Access 2000 and later versions provide a new data type called Hyperlink, which stores URLs. These look a little different than normal fields in a table, as well as those on Access forms.

Access recognizes Hyperlink fields and treats them differently. Double-clicking a Hyperlink field opens your browser automatically and navigates to the specified URL.

SQL Server doesn't provide such a data type, which poses a minor problem when you upgrade an Access database that contains them. But even if you aren't upgrading an Access database, you might want to store URLs in your SQL database. So, how do you treat them so they deliver the same functionality?

The functionality you need to do this resides in the Windows library shell32.dll. To call this function, you need to write a function declaration for it so that Access (or any other application that uses VBA) knows where it lives. Paste the following code into a module:

Public Declare Function ShellExecute Lib "shell32.dll"  Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
     ByVal lpOperation As String, _
     ByVal lpFile As String, _
     ByVal lpParameters As String, _
     ByVal lpDirectory As String, _
     ByVal nShowCmd As Long) _
As Long

Now you have a function called ShellExecute() that you can call anytime you need it. To simplify calling it, I wrote this wrapper function called Connect():

Public Sub Connect( strURL as String )
On Error GoTo Connect_Error
    Dim StartDoc As Long
    If Not IsNull( strURL ) Then
        StartDoc = ShellExecute(Me.hwnd, "open", Me.URL, _
            "", "C:\", SW_SHOWNORMAL)
    End If

    Exit Sub

Connect_Error:
   MsgBox "Error: " & Err & " " & Error
   Exit Sub
End Sub

From any VBA application, you should pass the URL from SQL Server to the function Connect(). For example, if you have an Access form that displays the URL values from the SQL database, simply call the function from the OnDblClick event of the control. Open the form in design mode, double-click the control to open its property sheet, select the Events tab and the OnDblClick event, and type this (assuming that the column of interest is named URL):

=Connect( Me.URL )

You can use the same code from any VBA-compliant application.
Return to the Artful SQL Server & Access tips page