Sample VBA code to get data from access database to Excel

A sample code (downloaded from public domain) to get data from access database and use it in excel

Sub GET_FROM_ACCESS_DATABASE()
    Dim conn As Object, recordset As Object
    Dim intColIndex As Integer
    Dim DBName As String
    Dim TargetRange As Range
    'name and path
    DBName = "C:\Database_name.mdb"

    'On Error GoTo Whoa

    Application.ScreenUpdating = False

    Set TargetRange = Sheets("Select").Range("A1")

    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBName & ";"

    Set recordset = CreateObject("ADODB.Recordset")
    recordset.Open "SELECT * FROM [NAME] WHERE [ID] = 10", conn, , , adCmdText

    ' Write the field names
    For intColIndex = 0 To recordset.Fields.Count - 1
    TargetRange.Offset(1, intColIndex).Value = recordset.Fields(intColIndex).Name
    Next

    ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset recordset

    Application.ScreenUpdating = True
    On Error Resume Next
    recordset.Close
    Set recordset = Nothing
    conn.Close
    Set conn = Nothing
    On Error GoTo 0
    Exit Sub

End Sub

DOWNLOAD PDF (sample vba code to get access data inside excel)

  vba code get data from access database to excel.pdf (Size: 53.34 KB / Downloads: 1)