Import data from access including headers

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Import data from access including headers

Post by YasserKhalil »

Hello everyone
I am using the following code to import data from access file and it is working well.

Code: Select all

Sub ImportData()
    Dim a, strSQL As String, i As Long
    Application.ScreenUpdating = False
        strSQL = "SELECT * FROM StudentData ORDER BY student_code"
        With ThisWorkbook.Worksheets("Sheet1")
            .UsedRange.Offset(1).ClearContents
            ImportFromAccessTable ThisWorkbook.Path & "\filename.mdb", strSQL, .Range("A2")
            a = .Range("A1").CurrentRegion.Value
            .Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
        End With
    Application.ScreenUpdating = True
    MsgBox "Data Imported", vbInformation, "Done"
End Sub

Sub ImportFromAccessTable(sPath As String, SQL As String, rngTarget As Range)
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset
    On Error GoTo ErrHandler:
    Application.ScreenUpdating = False
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath
        Set rs = New ADODB.Recordset
        rs.Open SQL, cnn
        If rs.EOF And rs.BOF Then
            rs.Close: cnn.Close
            Set rs = Nothing: Set cnn = Nothing
            MsgBox "There Are No Records In The Recordset!", vbCritical, "No Records": Exit Sub
        End If
        rngTarget.CopyFromRecordset rs
        rs.Close: cnn.Close
        Set rs = Nothing: Set cnn = Nothing
    Application.ScreenUpdating = True
    On Error GoTo 0
    Exit Sub
ErrHandler:
    Set rs = Nothing: Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") In Procedure ImportFromAccessTable"
End Sub
The imported data doesn't include the fields names (headers). How can I implemet that point?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import data from access including headers

Post by HansV »

Change ImportFromAccessTable as follows:

Code: Select all

Sub ImportFromAccessTable(sPath As String, SQL As String, rngTarget As Range)
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset
    Dim i As Long
    On Error GoTo ErrHandler:
    Application.ScreenUpdating = False
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath
        Set rs = New ADODB.Recordset
        rs.Open SQL, cnn
        If rs.EOF And rs.BOF Then
            rs.Close: cnn.Close
            Set rs = Nothing: Set cnn = Nothing
            MsgBox "There Are No Records In The Recordset!", vbCritical, "No Records": Exit Sub
        End If
        For i = 0 To rs.Fields.Count - 1
            rngTarget.Offset(0, i).Value = rs.Fields(i).Name
        Next i
        rngTarget.Offset(1).CopyFromRecordset rs
        rs.Close: cnn.Close
        Set rs = Nothing: Set cnn = Nothing
    Application.ScreenUpdating = True
    On Error GoTo 0
    Exit Sub
ErrHandler:
    Set rs = Nothing: Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") In Procedure ImportFromAccessTable"
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Import data from access including headers

Post by YasserKhalil »

Amazing. Thank you very much.
What if I need the headers (the description part) not the names of the fields? What would I change in the code?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import data from access including headers

Post by HansV »

What do you mean by "the headers (the description part) not the names of the fields"?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Import data from access including headers

Post by YasserKhalil »

In the design view, there are fields' names and the fields' type the fields' description.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import data from access including headers

Post by HansV »

That would be complicated, do you REALLY need those? The field names are required, but the descriptions are often left blank.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Import data from access including headers

Post by YasserKhalil »

Never mind if it is complicated. The most important for me is the names of the fields in the first place.