Simple ADO connection to Access

To create an ActiveX Data Objects (ADO) connection to the Access database which is currently open, you can use the Connection or AccessConnection properties of the CurrentProject object.

AccessConnection is only available in Access 2002 or later and is only used to connect to Jet databases (.MDB files). It's primary use is to bind Access forms to updatable recordsets. The Connection property can be used to connect to Jet databases or Access projects (SQL Server/ADP). An detailed article describing the difference between Connection and AccessConnection can be found in the Microsoft Knowledgebase here.

Example:

Sub Main()
    Dim adoConn as ADODB.Connection
    Dim rstRecordset as ADODB.Recordset

    Set adoConn = CurrentProject.AccessConnection
    Set rstRecordset = New ADODB.Recordset
    rstRecordset.Open "SELECT * FROM t_mytable", adoConn

    ...

    rstRecordset.Close
    Set rstRecordset = Nothing
    adoConn.Close
    Set adoConn = Nothing
End Sub

Author: ASAK
Created: Jan 17 2007
Categories: Access
TechByte #148

Warning: By visiting this site and/or by using any information contained herein, you agree to the Techbytes.ca terms of use.