Learn Macros‎ > ‎VBA Macros‎ > ‎

VBA code to import the MS Access query data into MS Excel

posted Jun 2, 2016, 9:55 AM by Ayush Jain

Sub importAccessdata(strDBPath As String, strTableName As String)

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
    Dim strFilePath As String

    strFilePath = strDBPath
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strFilePath & ";"
    sQRY = "SELECT * FROM " & strTableName

    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    Application.ScreenUpdating = False
    Worksheets("Summary").Range("A2").CopyFromRecordset rs

    Set rs = Nothing
    Set cnn = Nothing
    Exit Sub

End Sub