O365 64bit Excel VBA - need to query data in Access DB (64bit) - how do I set this up?

[ric60]

Hi I would like to see example VBA code to connect to Access from Excel - to return queried data - all 64bit. all Office 365.

Thanks,
Rick

[romperstomper]

Here you go:

Sub GetAccessData()
' Sample demonstrating how to return a recordset from an Access db

   Const adOpenStatic As Long = 3
   Const adLockPessimistic As Long = 2
   Const adCmdText As Long = 1

    '    sheet for output
   Dim wks As Excel.Worksheet
   Set wks = ActiveSheet

    '    Path to database
   Dim PathToDB As String
   PathToDB = "C:\test\database1.accdb"

   Dim cn As Object
   Set cn = CreateObject("ADODB.Connection")
    '    open connection to database
    With cn
        .ConnectionTimeout = 500
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & PathToDB & ";"
        .Open
        .CommandTimeout = 500
    End With

    '    SQL query string - change to suit
   Dim commandText As String
   commandText = "SELECT tblGSD.Day, tblGSD.[Due Calls] "
    commandText = commandText & "FROM tblGSD "
    commandText = commandText & "WHERE (((tblGSD.Day)=#2/21/2019#)) "
    commandText = commandText & "GROUP BY tblGSD.Day, tblGSD.[Due Calls] "
    '    Create New Recordset
   Dim rs As Object
   Set rs = CreateObject("ADODB.Recordset")

    ' open recordset using query string and connection
    With rs
        .Open commandText, cn, adOpenStatic, adLockPessimistic, adCmdText
        ' check for records returned
        If Not .EOF Then
            'Populate field names
            Dim i As Long
            For i = 1 To .Fields.Count
                wks.Cells(1, i).Value = .Fields(i - 1).Name
            Next i

            ' Copy data to sheet starting from A2
            wks.Cells(2, 1).CopyFromRecordset rs

        End If
        .Close
    End With

    ' clean up
    cn.Close
End Sub