Saving VBA tools/references settings

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?

Answer 1

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
    rs.Close
    Set rs = Nothing
End Function

Asaf

Answer 2

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
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals