Data Management

Storing URLs in your SQL database

If you want to store URLs in your SQL database, do you know how you treat them so they deliver the same functionality? The functionality you need to do this resides in the Windows library shell32.dll.

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 upsize an Access database that contains them. But even if you aren't upsizing 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 )

This tip is of primary interest to Access developers who are upsizing an application to SQL Server. However, you can use the same code from any VBA-compliant application.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox