Saturday, March 21, 2015

Import Data from Access Table

Macro to import data from access table using Excel VBA

Option Explicit

Sub import_data()
    ' Tools Refrences set microsoft active x object
    ' clear exiting data
    ' call sub proc to import data pass three parameters
    ' 1st query
    ' 2nd range/location for import
    ' 3rd True, False to import column/field names
    Call get_data("select * from tbl_sample", Sheet1.Range("a1"), 1)
End Sub

Sub get_data(strQry As String, rng_to_paste As Range, fld_name As Boolean)
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim i As Long
    Dim dbpath As String
    Set rs = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    dbpath = ThisWorkbook.Path & "\database.accdb"
    ' create the connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open dbpath
    End With
    rs.CursorLocation = adUseClient
    rs.cursortype = adOpenDynamic
    rs.locktype = adLockOptimistic
    rs.Open strQry, cnn
        If fld_name = True Then
        For i = 1 To rs.Fields.Count
            rng_to_paste.Offset(0, i - 1).Value = rs.Fields(i - 1).Name
            rng_to_paste.Offset(1, 0).CopyFromRecordset rs
            rng_to_paste.CopyFromRecordset rs
        End If
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...