Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query (Excel 2002 (xp) SP2)

    Is it possible to import data from a parameter query in a Access database using MS Query. I keep getting an error stating that "Too few parameters. N expected. How do I pass them on from MSQ or Excel?? The Access query gets its parameters from a form. I've tried leaving that Access form open and populated during the import to Excel, but no go. Thanks for any ideas you might have.

    Randy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query (Excel 2002 (xp) SP2)

    MS Query isn't aware of the existence of Access forms. Is it possible for you to export from Access to Excel instead?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002 (xp) SP2)

    If you need to control everything from Excel then you're going to have to write some VBA. Probably the most straightforward approach would be to challenge the user for the parameters using InputBoxes or a form, contruct an SQL string and then pass it to Access using ADO.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002 (xp) SP2)

    Hans--

    Thanks for the suggestion. I looked into transferring the recordset to Excel through TransferDatabase method, but that will create a new spreadsheet file each time. I have several charts already formated that I just need to update data to. If need be, I could create a chart in Excel using automation from Access, but that is very advanced for my skills and I'm not sure I'd be able to get it done. Is there code already written for this sort of automation that I could use as a template? Again, thanks for your help.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002 (xp) SP2)

    Actually, the solution I'm creating is in Access. I can never get Access Charts to do what I want them to do, so I have created an Excel spreadsheet with charts formated as I want them. The end result of this Access solution is a report. When the report is run, one of its actions displays a chart that is linked from Excel. it is included in my report using Paste Special... . When the report is opened in Access, the Excel Chart updates in data prior to being displayed. Its datasource is a linked query from the Access solution I'm creating. So, when I open the report in Access, it pulls in a chart from Excel, but before the chart is displayed, the chart updates it datasource by re-querying Access. Neat little circular logic, I think. This worked well when the Access query did not include parameters. I was just using static criteria in the query grid. It worked fine. When I changed the criteria to use parameters, that is when it broke.

    So, Adam, while your solution doesn't quite fit my design, you got me thinking. Is it possible to construct an SQL statement in Access and present it to Excel to be used in MSQ? I know how to construct the SQL statement, but I wouldn't know where to begin to get MSQ to use it.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query (Excel 2002 (xp) SP2)

    If you use TransferSpreadsheet to export a query to an existing workbook, it will export the query to a worksheet with the same name as the query within that workbook; if the worksheet already exists it will be overwritten.

    Alternatively, you could use ralphad's suggestion: prompt the user for the values in Excel, construct a SQL statement using the values provided by the user, then open a recordset (ADO or DAO) from this SQL and use CopyFromRecordset to import the data into a worksheet. You can look up CopyFromRecordset in the Excel VBA help

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002 (xp) SP2)

    You can pass the SQL string to the query in Excel as follows:-

    <pre>shtData.QueryTables(1).CommandText=sSQL</pre>


    Where <pre>shtData</pre>

    is a reference to the worksheet object containing the query and <pre>sSQL</pre>

    is a String containing your SQL statement. If you have more than one query on the worksheet then you'll have to adjust <pre>QueryTables(1)</pre>

    accordingly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •