Computer Aided Automation

Full Version: Sample VBA code to get data from access database to Excel
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
A sample code (downloaded from public domain) to get data from access database and use it in excel

    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

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

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

End Sub

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