Improve company productivity with a Business Account.Sign Up


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

Posted on 2014-04-10
Medium Priority
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 86
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 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

606 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