• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Access 2010, 2013 VBA Run SQL Return Results

March 3, 2014 by Ryan McCormick 6 Comments

Sometimes when developing automation with VBA in Access, it is important to return results from a SQL query string. For simplicity, all examples have been written as Functions. Please feel free to modify as needed.

Recordset Selection in VBA | Select One Record

There are many reasons why a developer would want to return only one result, for instance a count query. Here is an example for returning a single result:

Function getFirstRecord() 
Dim db AS DAO.Database, rs AS DAO.Recordset, strSQL as String

Set db = Currentdb

'Define SQL Query
strSQL = "SELECT COUNT(id) FROM table"

Set rs = db.OpenRecordset(strSQL)

'Return Result
getFirstRecord = rs.Fields(0)

'Close the Database
db.Close
set db = Nothing
set rs = Nothing

End Function

Recordset Selection in VBA | Return All Records

If you notice “rs.Fields(0)” in the result above, columns are returned based on “0” index. For example, if you were to run a SQL Query:

SELECT firstname, lastname, phone
FROM table

Your result column index would look like:

  • firstname = Fields(0)
  • lastname = Fields(1)
  • phone = Fields(2)

Here is an example for returning multiple results:


Function getAllRecords() 
Dim db AS DAO.Database, rs AS DAO.Recordset, strSQL as String

Set db = Currentdb

'Define SQL Query
strSQL = "SELECT firstname, lastname, phone FROM table"

Set rs = db.OpenRecordset(strSQL)

'Return Result in Loop until end of results
Do While NOT rs.EOF
'Insert code here to list elements
'We will loop through and popup a message box
'for this example

MsgBox "Contact: " & rs.Fields(0) & " " & rs.Fields(1) & ": " & rs.Fields(2)
'This would pop a message box for each loop
'and display each contact called from the
'SQL query

'move to the next record for the next loop
rs.MoveNext
Loop

'Close the Database
db.Close
set db = Nothing
set rs = Nothing

End Function

VBA Run SQL Without Results

Most of the time it is not necessary to return results when running UPDATE, INSERT and DELETE queries. Here is how to run a SQL query in Access with VBA without returning results:

Function deleteRecord()

'disable warnings (optional)
DoCmd.SetWarnings = False
DoCmd.RunSQL "DELETE FROM table WHERE id=1"
DoCmd.SetWarnings = True

End Function

Please comment with questions, suggestions or improvements.

Related

Filed Under: Microsoft Access Tagged With: access 2010, access 2013, SQL, vba

Reader Interactions

Comments

  1. Matt says

    September 23, 2014 at 12:40 am

    This is great for me as i’m a novice in vba, perfect!

    May i ask if there’s any article like CRUD in vba?, Would be much appreciated, Thanks

    Reply
  2. Ryan McCormick says

    September 23, 2014 at 9:17 am

    I am happy that you like the article!

    There are many ways to CRUD in Access depending on the project that you are working on. If you are just getting started with VBA and need to run actions with little setup, I suggest using DAO for reading and the DoCmd.RunSQL for INSERT, UPDATE, DELETE SQL actions.

    If you are super novice with SQL as well, I recommend picking up a good primer book. Access has it’s own nuances of SQL, but most SQL functionality is universal across all relational database systems. If you are looking for a suggestion, I have reviewed and recommend this one: http://www.headfirstlabs.com/books/hfsql/

    Reply
  3. Jos Vermeulen says

    December 12, 2014 at 4:46 am

    You’re missing a rather important Not statement in your second example 😉

    Reply
    • Ryan McCormick says

      January 15, 2015 at 7:31 am

      Crap! Thanks for the heads up! Updated.

      Reply
  4. Marcus Black says

    July 6, 2016 at 2:17 am

    I am trying to create a message box returning all of the values of a query which tells me which courses people need to re-sit.

    I have a query called REPORT_INFO which returns the list of courses. There could be 1 or 20 or anywhere in between. The column name in the query is courses but I want the message box to read:

    “Please be advised that the following courses have expired and must be re-taken:

    [Course list here]” +signatures etc.

    This has been something I have worked on for a few days now and I just haven’t been able to get my head around it! (Might be for a different thread but if you can point me in the right direction I’d be eternally grateful)

    Reply
    • Ryan McCormick says

      March 19, 2017 at 4:17 pm

      Sorry this is so late, everything has been super busy and my blog has not been updated in awhile. If this is still a problem, it sounds like you are attempting to run the message box results on the collection itself in the same function. You should extract this logic to it’s own function – within, define an empty string and loop through your recordset of results. Add the course from each row to the empty string with linebreak(or whatever your seperation delimiter is) and then return the string once your loop has completed.

      Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in