+ Reply to Thread
Results 1 to 5 of 5

Running an access append query with parameters from Excel. Access version 2010

  1. #1
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    106

    Running an access append query with parameters from Excel. Access version 2010

    Hey everyone,

    I have this challenge that I'm hoping someone can help me crack. I'm able to run access queries from excel using the following code. How could I adapt this to include a parameter? Any thoughts? The version of Access I'm using is Access 2010.


    Set AC = CreateObject("Access.Application")
    With AC
    .OpenCurrentDatabase ("I:\clt\Analysts\AnthonyP\DatabaseBackup\DataWareHousBackend.accdb")
    .CurrentDb.Execute "qryContractorHoursAppend"
    .Quit
    End With

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Running an access append query with parameters from Excel. Access version 2010

    Write the query in Access but don't prompt for any parameters.

    Then in Excel Go to Data -> From Other Sources _ >MS-Query

    This will launch the Query Wizard that I find more of a pain than a help. Click off the box that says Use the Query Wizard to create / edit query.

    Select the data source - one of the options is MS Access Database. You will get a dialog box to navigate to where you have the database. Select the database and then select the query and click close.

    You now should have an interface similar to the MS-Access query builder. Drag and drop the fields into the query area (big window) or just drag *.

    Now click on the eyeglasses and funnel icon. This opens up the criteria field area. You can either drag a field into the criteria field or use the dropdown.

    Let's say that you are prompting for a specific date, in the value box type =[Date]. Now when you run the query, you will be prompted for a date using an input box.

    Enter the date and select the closing door icon to return the data to the worksheet.

    Now whenever you run the query you will be prompted to enter a date.

    But wait! It gets better. If you right click in the data area, you get a menu. Select Table -> Parameters. One of the options is Get the value from the following cell. This means that the query will run using the value in that cell as the parameter. So if you are always running the report for yesterday, you can have the formula =TODAY()-1 in this cell. Or perhaps a list of values from a dropdown list, etc.

    P.S. Go to Data -> Connections - you'll see the connection. You will also see options for background refresh (I advise to turn this off), to run the query on file open and to refresh it every x minutes. You can also rename the query to something more meaningful. Likewise, the table created by the query has an odd name. Rename it to something that makes sense to you.
    Last edited by dflak; 04-14-2017 at 04:07 PM. Reason: Add PS
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Running an access append query with parameters from Excel. Access version 2010

    Disregard. I misread the requirement. You'll have to build the query string in VBA.

  4. #4
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Running an access append query with parameters from Excel. Access version 2010

    Your suggestion to write out the query in Access put me on the right track. I'd like to share my solution but the site won't accept it. How do you do that?

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Running an access append query with parameters from Excel. Access version 2010

    I think you should be able to post it in tips and tutorials - it does seem like the kind of thing a lot of people would be interested in doing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Connection Issues, Excel To Access Query, Access 2010
    By onechief in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2015, 07:09 PM
  2. Access form works with access Viewer but not full version of Access?
    By Shanyn in forum Access Tables & Databases
    Replies: 1
    Last Post: 10-22-2014, 01:14 PM
  3. parameters in Excel for a query access
    By SubZero99 in forum Access Tables & Databases
    Replies: 1
    Last Post: 02-05-2014, 08:17 AM
  4. Too Few Parameters Error Importing Access Query Results into Excel
    By dash11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-06-2012, 04:42 PM
  5. Pull Access Append Query into Excel from Excel
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 10:22 AM
  6. Can I use MS Query in Excel like an Append Query in Access
    By Sam Wardill in forum Excel General
    Replies: 0
    Last Post: 04-11-2006, 09:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1