How to Access and Run a Select Query in VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Access and Run a Select Query in VBA
(Polka Dot RF/Polka Dot/Getty Images)

Microsoft Office Access allows programmers to use Visual Basic for Applications (VBA) to create applications in Access. VBA is a computer programming language used to automate routine tasks in Microsoft Office applications. Access is a relational database system commonly used for smaller databases. A select query is used to retrieve data from a database table. A Recordset is used to read the contents returned by the select query. In a few steps you can write VBA code to create a table and view its contents through a select query.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Open Microsoft Office Access, click "Blank Database," then select "Create." Click "Database Tools," then select "Visual Basic" to open the Microsoft Visual Basic window. Click the "Insert" field and select "Module."

  2. 2

    Copy and paste the following code into your new module:

    Private Sub runSelectQuery()

    Dim dB As Database

    Dim rcrdSet As Recordset

    Dim strSQL As String

    Dim Xcntr As Integer

    Set dB = CurrentDb

    strSQL = "CREATE TABLE selectQueryData (NumField NUMBER, Tenant TEXT, Apt TEXT);"

    DoCmd.RunSQL (strSQL)

    strSQL = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "

    strSQL = strSQL & "VALUES (1, 'John', 'A');"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (strSQL)

    strSQL = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "

    strSQL = strSQL & "VALUES (2, 'Susie', 'B');"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (strSQL)

    strSQL = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "

    strSQL = strSQL & "VALUES (3, 'Luis', 'C');"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (strSQL)

    strSQL = "Select selectQueryData.* from selectQueryData "

    strSQL = strSQL & "WHERE selectQueryData.Tenant = 'Luis';"

    Set rcrdSet = db.OpenRecordset(strSQL)

    rcrdSet.MoveLast

    rcrdSet.MoveFirst

    For Xcntr = 0 To rcrdSet.RecordCount - 1

    MsgBox "Tenant: " & rcrdSet.Fields("Tenant").Value & ", Lives in apt: " & _
    
                rcrdSet.Fields("Apt").Value
    

    rcrdSet.MoveNext

    Next Xcntr

    rcrdSet.Close

    db.Close

    End Sub

  3. 3

    Press "F5" to run your subroutine. The program will create a table, insert data into the table and use a select query to display data for a particular row.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.