Results 1 to 9 of 9
  1. #1
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22

    RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute

    Hi,

    I am new to the forum as well as new to MS Access / VBA. So I found out that there are 3 different ways to run a query in VBA. I know that RunSQL should only be used for action queries (no SELECT) and plus the error handling there is troublesome. Plus it's a tad slower. With QueryDef and CurrentDB execute method, we can control the errorhandling, etc. But what's the rule of thumb on when to use which one? This question has probably been asked before. I did a quick keywords search but couldn't find any posts. If there's an old thread, please share a link and I can just read through it. Otherwise, new replies are welcome.



    To give a perspective of what I am trying to accomplish. I am creating a form with a dropdown list and a command button. Upon clicking the button, I will want to execute a query + export to .csv / .xlsx based on the user selection from the dropdown list.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,877
    CurrentDb.Execute also only for action SQL. I have used QueryDef to modify a query object structure. I have only ever used this once.

    If you want to export a query with Import/Export Wizard then what you need to do is Open (not Run) SELECT query object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    Thanks for your response. I am looking to automate the process via VBA only. Not using any wizards, etc. Basically, click the button and magic happens. So will be write a query for each dropdown selection and then export it.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,877
    You can build an Import or Export Specification with the wizard. Do this one time with Wizard to build the Specification. Then VBA can reference the Specification. I have never used this.

    Otherwise, review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    Dropdown selection of what?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    jsut something like
    Report1, Report2, Report3, Report4
    Not trying specify any parameters or anything. At least not yet. Just a list of names for the reports a user can select from.
    I will have a different query for each report, I guess.


    Reading about Dynamic SQL but not sure if I will need to do that.

    There's so many things! A lot of reading!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,877
    Why different queries and reports? What is different? If it is only filter criteria then I would use 1 query and 1 report and apply filter when report opens. I don't like dynamic parameterized queries. I prefer VBA to build filter criteria. Review http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    I am not exactly sure what queries or how many we will have. Didn't get that far and will need more discussion with my boss.

    I guess the difference is that, e.g. Report1 + Query1 will be from Table1 while Report2 + Query2 will be from Table2. Or something like that.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,877
    If the source data and/or report design are different then yes, multiple queries/reports.

    Good Luck with project.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713
    Sort out what you want to do first. You will spend a lot time going in circles frustrated, if you are trying to implement(develop and design) before you get some clear specification of what you are trying to implement.

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

Similar Threads

  1. CurrentDB.Execute insert working just once
    By krausr79 in forum Access
    Replies: 4
    Last Post: 10-07-2015, 12:04 PM
  2. Error in CurrentDb.Execute
    By Stefan Moser in forum Access
    Replies: 5
    Last Post: 04-17-2015, 01:38 PM
  3. CurrentDB.Execute Insert Into with date variable
    By ketbdnetbp in forum Programming
    Replies: 2
    Last Post: 04-15-2015, 02:44 PM
  4. vba code help - Currentdb.execute
    By joycesolomon in forum Programming
    Replies: 5
    Last Post: 08-11-2014, 11:25 AM
  5. Update sql using currentdb.execute
    By laavista in forum Access
    Replies: 13
    Last Post: 08-15-2011, 03:51 PM

Posting Permissions

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