This is a brief post about using Application.FollowHyperlink in your databases.

The Problem With FollowHyperlink

I used to use Application.FollowHyperlink because it was so very simple to use to start e-mails, open files, launch a URL, …

Private Sub cmd_StartEmail_Click()
    If IsNull(Me.EmailAddress) = False Then Application.FollowHyperlink Me.EmailAddress
End Sub

until I started getting the dreaded security prompts

Continue reading →

In my previous post

Advanced SQL To Connect With External Databases

Let’s look at how we can use plain SQL Statements (SELECT, INSERT, UPDATE, DELETE & Make Table) to retrieve data from external Access databases.

I demonstrated how you can build queries that directly query with external databases. (queries that query, I know so eloquent of me)

Today however, I thought I’d turn my attention to using VBA to interact with external databases.

VBA Functions

Does a Table Exist

Let’s look at a typical example of checking whether a table exists within a database.

The typical code for working with a local table would be something along the lines of:

Public Function Table_Exist(ByVal sTable As String) As Boolean
    On Error GoTo Error_Handler
    Dim sName                 As String

    sName = CurrentDb.TableDefs(sTable).Name
    Table_Exist = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number <> 3265 Then 'Item not found in this collection. / Table doesn't exist
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Table_Exist" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

And we can use it by simply doing:

Debug.Print Table_Exist("Contacts")

So the question therefore becomes

How can I adapt this code to work with an external database?

Continue reading →