Results 1 to 10 of 10
  1. #1
    nirvana is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    5

    Question pass paramaters to query in saved imports

    Hi, any ideas on the following appreciated, thanks.

    background info:
    I have one database (adminDB) that needs to gather data from 7 identicle databases(subDBs). My plan is to import query output from each of the subDBs as tables into the adminDB

    so far this works to an extent
    ' delete exisiting tables if there
    DoCmd.RunSavedImportExport "db1Stats" ' runs various queries in db1
    DoCmd.Rename "DB1AttNos", acTable, "Ann Rep - Attendance Nos"
    ... 'rename all other tables

    however a several of the queries in the saved import have parameter (from and to dates - example below) and I currently have to type in the same info multiple times.

    SELECT DISTINCTROW AttenanceGroups.attendanceType, Sum([Let Bookings].AttNosAdult) AS [Sum Of AttNosAdult], Sum([Let Bookings].AttNosYouth) AS [Sum Of AttNosYouth], Sum([Let Bookings].AttNosChildren) AS [Sum Of AttNosChildren], Sum([Let Bookings].AttNosPre5) AS [Sum Of AttNosPre5], Count(*) AS [Count Of Let Bookings]
    FROM AttenanceGroups INNER JOIN [Let Bookings] ON AttenanceGroups.[ID] = [Let Bookings].[Attendance Type]
    WHERE ((([Let Bookings].bookDate)>=[Start Date:] And ([Let Bookings].bookDate)<=[End Date:]))
    GROUP BY AttenanceGroups.attendanceType;

    is there any way I can automate the passing of the from/ to dates to the queries (dates canbe lifted from a form for example)? The dates will be the same for each query.



    - Angie

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,813
    Sure, replace the bracketed parameter with:

    Forms!FormName.TextboxName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    nirvana is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    5
    Thanks Paul, that won't work in this case though as the queries aren't in the sub-databases (and they need to be able to work both in the sub database and in the Admin one). the admin database is to be used to pull a bunch of stats (booking figures) together from each of the other databases.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,813
    You asked:

    dates can be lifted from a form for example?

    But now you say it won't work?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    nirvana is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    5
    yeah, sorry if I wasn't very clear - I thought maybe there would be some way to get dates from a form on the admin database passed into the queries in the other databases but have no idea how to do it. Failing that I thought meybe there would be a solution obvious to someone else other than what I'm trying.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,813
    I would simply link the tables in the other databases in the admin database and run it all from there. Is that not an option?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    nirvana is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    5
    That was my first though - however it's linking to 7 identical databases (i.e. table names are the same) - unless there is a way to link and unlink them via code perhaps?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,813
    The linked name can be different than the actual name, so I'd link to them all and name them appropriately, like "TableNameDB1".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    nirvana is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    5
    *doh* that's much easier (and works). Thanks.
    - Angie

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,813
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to run saved query in background
    By shabar in forum Programming
    Replies: 3
    Last Post: 03-03-2013, 11:49 PM
  2. Changing File Location in saved imports using VBA
    By akshatagarwal93 in forum Import/Export Data
    Replies: 3
    Last Post: 07-23-2012, 10:47 AM
  3. Linking Info/Passing Paramaters?
    By hithere in forum Programming
    Replies: 17
    Last Post: 12-13-2011, 05:30 PM
  4. Access 2010 Saved Imports
    By Tomfernandez1 in forum Access
    Replies: 1
    Last Post: 09-27-2011, 12:27 PM
  5. Saved Import in Query
    By Kimbertha in forum Queries
    Replies: 1
    Last Post: 10-21-2010, 07:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums