Saving VBA tools/references settings RRS feed

  • Question

  • I want to use ADODB connections to manipulate tables in my Access database.

    It appears I need to select some of the Active X references from the tools/references menu in the Code window that are unselected by default.

    Having selected them and clicked OK, when I restart Access they are no longer selected.

    Which versions of which references should I be selecting for current version of Access and how do I save the settings please?

    • Edited by Mick Trist Thursday, June 27, 2019 3:45 PM
    Thursday, June 27, 2019 3:44 PM

All replies

  • Hi Mick

    According to what you wrote, you seem to be using more than one version of Access,
    A more efficient approach is to use a so-called late binding and not hard reference it in VBE reference window.

    for example:

    Dim cn As Object ' ADODB.Connection
      Set cn = CreateObject("ADODB.Connection")
      cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    '--------------------  or ---------------------------------
    Function ShowResult()
        Dim rs As Object
      Set rs = CreateObject("ADODB.Recordset")
        rs.Open "SELECT ID AS Result,* FROM crm;", CurrentProject.Connection
        ShowResult = rs!Result
        Set rs = Nothing
    End Function


    Friday, June 28, 2019 7:12 AM
  • Once you add a reference to a database and save the code it should stay there, or, if using a different version the next time you open the database, they should appear in the list of references as MISSING if they are not available. If that is not happening you need to check if you can actually use those libraries. Not every library is properly registered for use in Access.

    By the way, most of the developers I know shy away from ActiveX controls as they are unreliable across different operating systems and versions of Office.

    Bill Mosca

    Monday, July 1, 2019 2:51 PM