'Get path to Access database directory from Access SysCmd function.
    Set objAccess = CreateObject("Access.Application")
    strAccessDir = objAccess.SysCmd(9)
    strDBName = strAccessDir & "Samples\Northwind.mdb"
    Debug.Print "DBName: " & strDBName
    objAccess.Quit
 
    'Set up reference to Access database.
    Set dao = CreateObject("DAO.DBEngine.35")

'Code Modified from OZ Grid
' http://www.ozgrid.com/forum/archive/index.php/t-22483.html

Private Sub Workbook_Open()

Dim refAdd
Dim RefId(3) As String, MajorId(3) As Integer, MinorId(3) As Integer

    On Error GoTo ErrorHandler
    'Reference Object Library using Major / Minor GUID
    
    'Microsoft ActiveX Data Objects 2.6 Library
    RefId(1) = "{00000206-0000-0010-8000-00AA006D2EA4}": MajorId(1) = 2: MinorId(1) = 6
    'Microsoft DAO 3.6 Object Library
    RefId(2) = "{00025E01-0000-0000-C000-000000000046}": MajorId(2) = 5: MinorId(2) = 0
    'Microsoft ADO Ext. 2.6 for DDL and Security
    RefId(3) = "{00000600-0000-0010-8000-00AA006D2EA4}": MajorId(3) = 2: MinorId(3) = 6
    
    
    'add the references if they arn't there already
    For I = 1 To 3
        Set refAdd = ThisWorkbook.VBProject.References
        refAdd.AddFromGuid RefId(I), MajorId(I), MinorId(I)
    Next I
    
    
    Exit Sub
    
ErrorHandler:
    'Check the State of the Error number and respond acordingly
    Select Case Err.Number
        Case 32813
            'Module Already open and Exsits ignore and continue
            Err.Number = 0
            Resume Next
        Case -2147319779
            'The Object Library not Registered
            MsgBox "Object Library Not Registered," & Chr(10) & _
                        "Macro Unusable with Current Version of Excel"
            Exit Sub
        Case Else
            MsgBox "Unhandled Error" & Chr(10) & _
                Err.Number & " " & Err.Description & Chr(10) & _
                "Please Take a note of this error and Contact David"
    End Select
End Sub