Access - Conexión a BD access con ADODB

' Paso 1:Asignación de bd, recordset y querydef

Dim MyDatabase As DAO.database

Dim MyQueryDef As DAO.Querydef

Dim MyRecordset As DAO.Recordset

' Paso 2: Identificar la base de datos y la consulta.

Set MyDatabase = DBEngine.OpenDatabase ("C:\Mis Documentos\Genera datos.mdb")

Set MyQueryDef = MyDatabase.QueryDefs("Filtra UltDatos")      'esta query esta en la BD

'Paso 3: Abrir la consulta. Los resultados de la consulta son archivadas dentro de MyRecordset.

Set MyRecordset = MyQueryDef.OpenRecordset

'Paso 4: Se copian los datos a Excel, para este caso a la celda "I4"

ActiveSheet.Range("I4").CopyFromRecordset MyRecordset

Dim cs As String

Dim sPath As String

Dim sql As String

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

sPath = ThisWorkbook.Path & "\Genera datos.mdb"

cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"

Set cn = New ADODB.Connection

cn.Open cs

Set rs = New ADODB.Recordset

With rs

        .CursorLocation = adUseClient

        .CursorType = adOpenStatic

        .LockType = adLockOptimistic

End With

sql = "SELECT [xxx].campo1, [yyy].campo2, Sum([campo3]+[campo4]) AS Total " & _

        "FROM [xxx] LEFT JOIN [yyy] ON [xxx].campo1 = [yyy].campo5 GROUP BY [xxx].campo1"

rs.Open sql, cn

ActiveSheet.Range("I4").CopyFromRecordset rs

rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing