Import to Excel from Access via VBA with lookup columns

Question

I am working on importing data from an Access table into Excel. The import code that I have works in terms of pulling in data, but I have an issue with the data that is pulled in when the column in the access table is a looked up value from another table. For instance, I have EmployeeID stored in a separate table which is looked up in the table that I am extracting. The extract pulls the data, but it only pulls the autonumber that's assigned to the employee on the employee table rather than the employee name. The employee name is stored in the third column of the employee table and I need that value when the extract runs, not the autonumber. However, I don't know how to specify the column that extracts in SQL via VBA. Can someone please help? Here's what I have so far:

Sub getAccessData()

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim lngLastColumn As Long
Dim lngLastRow As Long
Dim OXLSheet As Worksheet

Set OXLSheet = Worksheets("WorksheetName")

Worksheets("WorksheetName").Cells.Clear

'Datebase path info
DBFullName = "C:\Users\myname\Desktop\Database Backups\database.accdb"

'Open the connection for the database
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect


'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset

    'Data Filter
    Source = "SELECT EmployeeID FROM tblRetirements WHERE AllowEnteredInPayroll]Is Null AND ApplicationCancelled = 'No'"
    .Open Source:=Source, ActiveConnection:=Connection


    'Write field Names
    For Col = 0 To Recordset.Fields.Count - 1
        Worksheets("WorksheetName").Range("A5").Offset(0, Col).Value = Recordset.Fields(Col).Name
    Next

    'Write Recordset
    Worksheets("WorksheetName").Range("A5").Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing



With OXLSheet
    lngLastColumn = .Cells(5, .Columns.Count).End(xlToLeft).Column
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .ListObjects.Add(xlSrcRange, .Range(.Cells(5, 1), .Cells(lngLastRow, lngLastColumn)), , xlYes).Name = "Table1"

    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"
End With

End Sub

have a look at this: https://stackoverflow.com/questions/21269387/lookup-field-appears-as-numerical-values-instead-of-text-on-access-report — Ibo, Sep 04 '18 at 17:05
Yes, but it's not really what I am looking for. I am extracting data from a table in Access to Excel so that I can use pivot tables, graphs, and manipulate the data. The extract works fine, but I can't seem to get the data when a column in my Access table is looked up from another table in access. I need a way to change the bound column number via SQL on the extract. — JosephD, Sep 04 '18 at 17:10
then you need to create a join query and export the result of that to Excel, that would work 100% — Ibo, Sep 04 '18 at 17:13
You need to look at SQL joins eg. `from join tblEmployees on tblRetirements.EmployeeID =tblEmployees.EmployeeID` — Nathan_Sav, Sep 04 '18 at 17:13
@JosephD not sure what you mean by joining one column, but here you have one table that is you main table = `tblRetirements` and another table that you keep the values for your lookup column. In this case you need to create a `LEFT JOIN` query where your main table is the left table and the other one is the right table. create a query and click on SQL and paste this query statement and see the results, then you can play around to get what you want `select * from tblRetirements left join tblEmployees on tblRetirements.EmployeeID=tblEmployees.EmployeeID` — Ibo, Sep 04 '18 at 17:29
Got it figured out. I added an INNER JOIN to the SQL query and everything works as intended now. Thank you for the advice. I'll repost the revised code below. — JosephD, Sep 04 '18 at 17:50
Hans, the 'WHERE [AllowEnteredInPayroll] Is Null' is used as a filter and it works exactly as intended. I don't see what the problem is with it. — JosephD, Sep 04 '18 at 17:55
I was missing a bracket and spaced out the 'Is Null' statement. That should work fine. — JosephD, Sep 04 '18 at 18:21

score 1 · Answer 1 · answered Sep 04 '18 at 21:00

What you see in a lookup column is actually the product of a join so to get the names instead of the IDs you need to define a SQL query and export its result instead of the table itself. To include all of the records from you main table, you need to use the LEFT JOIN. If you use INNER JOIN then you will get the same result unless you have records in your main table whose related record in the employee table has been deleted:

Sub getAccessData()

    Dim DBFullName As String
    Dim Connect As String, Source As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer
    Dim lngLastColumn As Long
    Dim lngLastRow As Long
    Dim OXLSheet As Worksheet

    Set OXLSheet = Worksheets("WorksheetName")

    Worksheets("WorksheetName").Cells.Clear

    'Datebase path info
    DBFullName = "C:\Users\myname\Desktop\Database Backups\database.accdb"

    'Open the connection for the database
    Set Connection = New ADODB.Connection
    Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
    Connect = Connect & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Connect


    'Create RecordSet
    Set Recordset = New ADODB.Recordset
    With Recordset

        'Data Filter
        Source = "SELECT tblEmployeeID.Name FROM tblRetirements " & _
        "LEFT JOIN tblEmployeeID on tblRetirements.EmployeeID = tblEmployeeID.Name " & _
        "WHERE [AllowEnteredInPayroll] Is Null AND ApplicationCancelled = 'No'"
        .Open Source:=Source, ActiveConnection:=Connection


        'Write field Names
        For Col = 0 To Recordset.Fields.Count - 1
            Worksheets("WorksheetName").Range("A5").Offset(0, Col).Value = Recordset.Fields(Col).Name
        Next

        'Write Recordset
        Worksheets("WorksheetName").Range("A5").Offset(1, 0).CopyFromRecordset Recordset
    End With
    ActiveSheet.Columns.AutoFit
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing



    With OXLSheet
        lngLastColumn = .Cells(5, .Columns.Count).End(xlToLeft).Column
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .ListObjects.Add(xlSrcRange, .Range(.Cells(5, 1), .Cells(lngLastRow, lngLastColumn)), , xlYes).Name = "Table1"

        ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"
    End With

End Sub

JosephD · Answer 2 · 2018-09-04T18:20:38.163

Revised Code:

Sub getAccessData()

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim lngLastColumn As Long
Dim lngLastRow As Long
Dim OXLSheet As Worksheet

Set OXLSheet = Worksheets("WorksheetName")

Worksheets("WorksheetName").Cells.Clear

'Datebase path info
DBFullName = "C:\Users\myname\Desktop\Database Backups\database.accdb"

'Open the connection for the database
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect


'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset

    'Data Filter
    Source = "SELECT tblEmployeeID.Name FROM tblRetirements " & _
    "INNER JOIN tblEmployeeID on tblRetirements.EmployeeID = tblEmployeeID.Name " & _
    "WHERE [AllowEnteredInPayroll] Is Null AND ApplicationCancelled = 'No'"
    .Open Source:=Source, ActiveConnection:=Connection


    'Write field Names
    For Col = 0 To Recordset.Fields.Count - 1
        Worksheets("WorksheetName").Range("A5").Offset(0, Col).Value = Recordset.Fields(Col).Name
    Next

    'Write Recordset
    Worksheets("WorksheetName").Range("A5").Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing



With OXLSheet
    lngLastColumn = .Cells(5, .Columns.Count).End(xlToLeft).Column
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .ListObjects.Add(xlSrcRange, .Range(.Cells(5, 1), .Cells(lngLastRow, lngLastColumn)), , xlYes).Name = "Table1"

    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"
End With

End Sub

can this be true `tblRetirements.EmployeeID = tblEmployeeID.Name`? — Nathan_Sav, Sep 04 '18 at 18:17
@Nathan_Sav it cannot be true, meaning that the query will return 0 records — Ibo, Sep 04 '18 at 20:45

Import to Excel from Access via VBA with lookup columns

2 Answers2

Linked