[AccessD] Set references programmatically

Here is a quick method for use on startup.

Function DAO_OK() As Boolean
Dim varDBE As Variant
On Error Resume Next

    If Access.Application.SysCmd(acSysCmdAccessVer) = "8.0" Then
        Set varDBE = VBA.CreateObject("DAO.DBEngine.35")
    Else
        Set varDBE = VBA.CreateObject("DAO.DBEngine.36")
    End If
    DAO_OK = (Err.Number = 0)
End Function

But remember when doing this checking at startup the code must be 
disambiguated
untill you have verified all the correct references are there.
In other words all function calls must be fully expressed so that no dll 
checking
is done by the p-code because if not you will fail on a reference check
Maybe a 429 error?

For example even MsgBox must be VBA.MsgBox
 or CreateObject must be VBA.CreateObject.

You may also have to reregister DAO 3.6 as registration can be lost 
occassionally.
I have never checked but even this reregistration may cough on fully locked
down systems.

This method is useful in MDE's but you cannot add a reference in an MDE.

Re-Register DAO
http://www.trigeminal.com/code/RegisterDao.bas

Adding a reference  with VBA

Dim ref As Access.Reference
'may want to do a VBA.Dir to verify existance of file
ref.AddFromFile "C:\path to my dll ocx etc"


Some other notes and urls that might be helpful

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
http://allenbrowne.com/ser-38.html

Explanation of VBA code disambiguation
http://www.trigeminal.com/usenet/usenet026.asp


About how Access searches for reference libraries
What order access uses to search for references

http://office.microsoft.com/en-ca/assistance/HP030797131033.aspx
http://support.microsoft.com/kb/q296205/
http://support.microsoft.com/kb/q189366/



Subject: INFO: How to guarantee that references will work in your 
applications
Micheal Kaplan
http://www.trigeminal.com/usenet/usenet026.asp?1033


Sub CheckReferences()
'courtsey Paul Overway

Dim ref As Access.Reference
Dim intResponse As Integer
Dim blnMissing As Boolean
Dim strURL As String
Dim strBAD As String
Dim strMSG As String

On Error Resume Next
    For Each ref In Access.Application.References
        If ref.IsBroken Then
            strBAD = vbCrLf & vbTab & ref.FullPath & "   "
            blnMissing = True
        End If
    Next
    If DAO_OK() = False Then
     VBA.MsgBox "One or more DAO files is missing, corrupt, or not 
registered." _
                 & vbCrLf & vbCrLf & _
                 "This error may result from an incomplete or failed 
installation" & _
                 " of Microsoft Access and/or its components " & _
                 "and will prevent the software from functioning 
properly." & _
                  vbCrLf & vbCrLf & "Please see articles 296205, 319841, 
or " & _
                 "319844 in Microsoft's Knowledge Base at 
support.microsoft.com." _
                 , vbApplicationModal + vbCritical + vbOKOnly, _
                 " DAO Reference Error"
        Access.Application.DoCmd.Quit
    End If


    If blnMissing Then
        strMSG = "The following referenced files are missing or corrupt 
and " & _
                 "will prevent the software from functioning properly:" _
                 & vbCrLf & strBAD
        Access.Application.DoCmd.Quit
    Else
      'Refs OK...safe to run other code
       Access.Application.Run "NextStartUpRoutine"
    End If
End Sub

Function DAO_OK() As Boolean
Dim varDBE As Variant
On Error Resume Next

    If Access.Application.SysCmd(acSysCmdAccessVer) = "8.0" Then
        Set varDBE = VBA.CreateObject("DAO.DBEngine.35")
    Else
        Set varDBE = VBA.CreateObject("DAO.DBEngine.36")
    End If
    DAO_OK = (Err.Number = 0)
End Function

' This is useful as you cannot always see the full path name
' of a reference in the Access add reference window
' One day they might make it scrollable
' hasn't happened in 4 versions.
Public Function GetRefs()
 '====================================================================
 ' Name:    GetRefs
 ' Purpose:    Get a list of the current database references
 '
 ' Author:    Arvin Meyer
 ' Date:    April 10, 1999
 ' Comment:   
 '
 '====================================================================
On Error GoTo Err_GetRefs
Dim i As Integer

For i = 1 To Application.References.Count

    Debug.Print Application.References(i).FullPath

Next i

Exit_GetRefs:
    Exit Function

Err_GetRefs:

    Debug.Print "Missing Reference"
    Resume Next

End Function



ewaldt at gdls.com wrote:

>I create smaller databases from a main one so that I can send out the
>smaller ones to users. I export forms, queries, and appropriate subsets of
>tables. I also export a startup macro and a VBA function for the startup
>macro to run. Now for the problem.
>
>The VBA function uses DAO, and I need to make sure the user will have DAO
>referred to. How do I do that programmatically? Specifically, I am looking
>for "Microsoft DAO 3.6 Object Library".
>
>TIA,
>
>Thomas F. Ewald
>FCS Database Manager
>General Dynamics Land Systems
>(586) 276-1256
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada