Excel - Leer datos de access desde excel usando 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

' estas dos lineas a inicio del modulo

Public MiConexion As New ADODB.Connection

Public Rs As New ADODB.Recordset

Sub Conecta()

Set MiConexion = New ADODB.Connection

    With MiConexion

        .provider = "Microsoft.ACE.OLEDB.11.0"

        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\Genera datos.mdb"

        .Open

    End With

End Sub

Sub TraeDatosAccess()

Dim MiQuery As String

Call Conecta

Set Rs = New ADODB.Recordset

MiQuery = "SELECT [Filtra UltFecha].campo1, [Historico].campo2, Sum([DfPtCtr]+[DfAgCtr]) AS DifTotal " & _

          "FROM [Filtra UltFecha] LEFT JOIN [Historico] ON [Filtra UltFecha].campo1 = [Historico].campo1 " & _

          "GROUP BY [Filtra UltFecha].campo1, [Historicol].campo2"

Rs.Open MiQuery, MiConexion, adOpenKeyset, adLockOptimistic, adCmdText

' La siguiente linea Copiaba el contenido del recordset en su totalidad en la fila y columna indicada

 Worksheets(3).Cells(4, 14).CopyFromRecordset Rs

    Rs.Close

    MiConexion.Close

Rs.Open SQL, conexion

    If Rs.EOF = True And Rs.BOF = True Then

    MsgBox "No existen Resultados para mostrar", vbInformation

    Rs.Close

    Exit Sub

    End If

j = Rs.Fields.Count  - 1

    While Not Rs.EOF

        i = i + 1

        k = 0

        While k < j

                    variable = Rs(k)

            k = k + 1

        Wend

        Rs.MoveNext 'avanza al siguiente registro del rs.

    Wend