Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1915
  • Last Modified:

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

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
txrussianguy
Asked:
txrussianguy
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
txrussianguyAuthor Commented:
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
 
txrussianguyAuthor Commented:
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
 
Jerry PaladinoCommented:
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
 
txrussianguyAuthor Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now