Uncertain what Access query type to use for Excel external data pull

Posted on 2014-04-10
Last Modified: 2014-05-04
all '10 products.

I need my Excel to pull into a pivot table the results of a query in Access.  I'd rather not have to run the query and make a table, but rather have the query set up, and when needed by Excel, get the information pulled.  been a while since I've used Access or external data in Excel.  Here is where I'm falling:

- what type of query should I be setting up in Access (seems there are many more than when I used to used it)
- in Excel, I'm not finding the ability to pick a query name when opening my external connection to Access...does this mean I can ONLY run a query to make a table, have it stored in Access, and then reference that table with Excel?  

Most importantly, I noticed the ability to enter parameters into Access queries.  Is there a way to drive that parameter with a cell in Excel?  That's what I ultimately need.  I have a large DB of financial results, with project codes.  I have a list of project code owners.  I need to have an Excel file which references that data and only pulls in the data that's relevant for the person for whom that file is being created (and put it into a PT format, for manipulating the view).  

Thanks for what hopefully doesn't come across as too newbie in this forum.
Question by:txrussianguy
  • 3
LVL 84
ID: 39991395
Excel would expect only the data needed to build your pivot table, so you'd use a SELECT query to get that data. You can link a query from Access using the Excel data connections. From there you'd use Excel to create the pivot table and  such.

I don't think you could enter parameters using the data connection in this manner. You'd instead have to link your excel workbook in Access, and then your query could refer to cells  in that "table". But I don't know if a linked Excel workbook would do what you want (i.e. build the pivot in Excel).

Access can create Pivot queries - they're known as Crosstab queries. Perhaps you could just link the Excel workbook, and create the pivot in Access? You could then refer to cells in  that linked workbook in your query.

Author Comment

ID: 39991641
Unfortunately pivots much more limited/cumbersome in Access, I've found.  pivots/Excel are the few things I know extremely well (albeit with this exception of external pulls from Access in '10 (did it years ago, but forgot).  So I tried the pivot-in-Access route.  Just not the flexibility I need (in Excel, I'll have several formats of the pivot, ready for mgrs to use in review of their numbers).  

So bottom line is I need to get the data from Access, in relatively raw format, to Excel.  I was hoping that I could accomplish the data limitation (not all managers should inherit into their file the full data set..just their portion) by using parameters, but sounds like that's not possible.  My next option is to build queries for each person, and then each mgr's excel file will simply go to their individualized query.  Just means having about 60 queries (i have Sr.Mgrs, Mgrs that own a subset of the Sr.Mgr's projects, and staff that own a subset of the Mgr's file).   I was trying to avoid that, and control, with some trigger in Excel, the name of the staff/mgr, so that the pull would be specific to them, and with only one query.

I tried the simple query, but for some reason it wasn't working.  Will try again and report back on what exactly is not working.  Maybe you could help me resolve at that point.

Author Comment

ID: 39991799
I realized what the problem was (tried to state in original question, but may have gotten confused)...I can't, from Excel, see the names of queries...just tables.  If I could see the simple query name, then I'd pull that.  Is there some special magic for how to get the query names to appear as options for data source?
LVL 16

Accepted Solution

Jerry Paladino earned 500 total points
ID: 39993793
Menu – DATA / Get External Data / From Access
This returns the visible tables and queries from the Access database you select.  Some items like Linked Table and Union queries will not show in the dialog box that returns for you to select from.   If you are not seeing the query you want to use, select any Table or Query in the list and press OK.   Then, when the Import Data dialog box displays, select the "Properties" button in the lower left corner.  This will display the "Connection Properties" dialog box.   Select the "Definition" tab in that dialog.  In the "Command Text" box, overtype the Table or Query name you just selected with the name of the Query you want to run.  Press OK and the result set will be returned to the Excel worksheet.

To use parameters for an access query, I pass them from Excel to Access with ADO into an Access Table with a VBA routine.  Once they are in Access you can use them in any of your queries.   The sample below, pulls the Start and End Dates from Defined Names in an Excel sheet and pushes them to Access Table called DATERANGE with an INSERT INTO SQL statement.  You will need to create the table in Access once before you use this technique.   The table for this example has one row with StartDate and EndDate defined as Dates.  The process below clears any existing values in the table and then inserts the new values into the table.

 In VBA, requires reference to Microsoft ActiveX Data Objects Library  -  Menu – Tools / Reference / Microsoft ActiveX Data Objects Library
Sub ExportDateRangeToAccess()
' Macro ExportDateRangeToAccess
' Written by ProdOps - 21-Feb-2010
' Reads the Defined Names, StartDate and EndDate from the xxxxxx worksheet
' and pushes them to the Access Db table [DateRange] to use as
' date parameters for the Access queries.
' In VBA, requires reference to Microsoft ActiveX Data Objects Library
' Menu – Tools / Reference / Microsoft ActiveX Data Objects Library

    Dim cn As Object
    Dim strQuery As String
    Dim StartDate As String
    Dim EndDate As String
    Dim myDB As String

    'Initialize Variables
    StartDate = Range("StartDate").Value   ' Start Dates stored in Defined Name "StartDate"  
    EndDate = Range("EndDate").Value       ' End Dates stored in Defined Name "EndDate"
    myDB = Range("AccessDb").Value         ' Path to Access Db stored in Defined Name "myDb" – like -  "C:\ProdOps\Sample.accdb"

    Set cn = CreateObject("ADODB.Connection")

    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
        .ConnectionString = myDB
    End With

    strQuery = "INSERT INTO DateRange (StartDate, EndDate) " & _
               "VALUES (#" & StartDate & "#, #" & EndDate & "#); "

    cn.Execute "DELETE FROM DateRange"
    cn.Execute strQuery

    Set cn = Nothing
    Exit Sub
End Sub

Open in new window


Author Closing Comment

ID: 40040934
have not attempted.  More complex than I'd hoped (no fault of responder), so giving great grade, even though I was hoping simpler (built in) approach existed.

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question