Solved

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

Posted on 2014-04-10
5
1,891 Views
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.
0
Comment
Question by:txrussianguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 85
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.
0
 

Author Comment

by:txrussianguy
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.
0
 

Author Comment

by:txrussianguy
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?
0
 
LVL 16

Accepted Solution

by:
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
        .Open
    End With

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

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

    Set cn = Nothing
    Exit Sub
    
End Sub

Open in new window

HTH,
Jerry
0
 

Author Closing Comment

by:txrussianguy
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.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

717 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