import access query into excel

[alandorss]

Hi,

Excel 2016. Never had problems before. Import works for tables which is useless to me, I need to import an access query.

Any help would be greatly appreciated, if only the navigation pane would show the queries and not just the tables, it would have been hassle free.

If it helps, the query is read only, it is a series of union alls of select distinct subqueries

[#2]

@alandorss You can pull Access Select queries in Excel, but unfortunately, not Union queries. If you can set up a select query based off of your composite union query, it should show up in the Nav pane list.

Here's an example using the Northwind Traders db where all the select queries are listed:

[#3]

Thank you. 

I ended up doing a major work around with VBA, deleting and then appending the union query to a new table which is linked into the excel file.

For anyones future reference this is what I did:

note that you need to add a few references for this to work:

Global conDT        As New ADODB.Connection
Sub EstablishDTDatabaseConn()
    Dim dbProvider, str As String
    dbProvider = "Microsoft.ACE.OLEDB.12.0"
    str = "Data Source=" & "FULL PATH To DATABASE"
    With conDT
        .Provider = dbProvider
        .ConnectionString = str
        .CommandTimeout = 0
        .Open
    End With
End Sub
Sub CloseDatabaseConnDT()
    conDT.Close
    Set conDT = Nothing
End Sub
Sub subDisableScreenUpdating()
    With Excel.Application
        .ScreenUpdating = FALSE
        .Calculation = Excel.xlCalculationManual
        .EnableEvents = FALSE
    End With
End Sub

Sub subEnableScreenUpdating()
    With Excel.Application
        .ScreenUpdating = TRUE
        .Calculation = Excel.xlCalculationAutomatic
        .EnableEvents = TRUE
    End With
End Sub

Sub UpdateTable()
    subDisableScreenUpdating
    EstablishDTDatabaseConn
    Dim strQry      As String
    Dim rst         As ADODB.Recordset
    'Delete all data
    strQry = "DELETE * FROM tblAllDataCombined;"
    Set rst = CreateObject("adodb.Recordset")
    With rst
        .CursorType = adOpenStatic
        .LockType = adLockPessimistic
        .Open strQry, conDT
    End With
    Set rst = Nothing
    'add all data
    strQry = " INSERT INTO tblAllDataCombined Select * FROM (SELECT * from TableA union all Select * from TableB union all Select * from TableC union all Select * from TableD union all Select * from TableE ) As a;"
    Set rst = CreateObject("adodb.Recordset")
    With rst
        .CursorType = adOpenStatic
        .LockType = adLockPessimistic
        .Open strQry, conDT
    End With
    Set rst = Nothing
    
    ActiveWorkbook.RefreshAll
    
    CloseDatabaseConnDT
    subEnableScreenUpdating
End Sub

[#4 · Accepted solution]

@Smitty Smith Hi, I had to do a work around in VBA. For other users future reference, this is what I did.

Step 1: Enable these connections:

Step 2: Use the following code:

Global conDT        As New ADODB.Connection
Sub EstablishDTDatabaseConn()
    Dim dbProvider, str As String
    dbProvider = "Microsoft.ACE.OLEDB.12.0"
    str = "Data Source=" & "FULL PATH To DATABASE"
    With conDT
        .Provider = dbProvider
        .ConnectionString = str
        .CommandTimeout = 0
        .Open
    End With
End Sub
Sub CloseDatabaseConnDT()
    conDT.Close
    Set conDT = Nothing
End Sub
Sub subDisableScreenUpdating()
    With Excel.Application
        .ScreenUpdating = FALSE
        .Calculation = Excel.xlCalculationManual
        .EnableEvents = FALSE
    End With
End Sub

Sub subEnableScreenUpdating()
    With Excel.Application
        .ScreenUpdating = TRUE
        .Calculation = Excel.xlCalculationAutomatic
        .EnableEvents = TRUE
    End With
End Sub

Sub UpdateTable()
    subDisableScreenUpdating
    EstablishDTDatabaseConn
    Dim strQry      As String
    Dim rst         As ADODB.Recordset
    'Delete all data
    strQry = "DELETE * FROM tblAllDataCombined;"
    Set rst = CreateObject("adodb.Recordset")
    With rst
        .CursorType = adOpenStatic
        .LockType = adLockPessimistic
        .Open strQry, conDT
    End With
    Set rst = Nothing
    'add all data
    strQry = " INSERT INTO tblAllDataCombined Select * FROM (SELECT * from qryA union all Select * from qryB union all Select * from qryC union all Select * from qryD union all Select * from qryE ) As a;"
    Set rst = CreateObject("adodb.Recordset")
    With rst
        .CursorType = adOpenStatic
        .LockType = adLockPessimistic
        .Open strQry, conDT
    End With
    Set rst = Nothing
    
    ActiveWorkbook.RefreshAll
    
    CloseDatabaseConnDT
    subEnableScreenUpdating
End Sub