How to Read a Value From a Query in MS Access 2007

A Recordset object can be a powerful tool to use in Microsoft Office Access when you need to query a table using Visual Basic for Applications (VBA). The quickest way to retrieve a value from a query is by using VBA. In VBA you can use the object to query a table in a database and retrieve values stored in the table. A Recordset object is used to manipulate data in a database at a record level.

Instructions

1. Open the Northwind database and click the "Database Tools" tab. Click "Visual Basic" to open the Visual Basic Editor Window. 

 

 2. Start by typing the following code to create a new subprocedure:

Private Sub readQueryValue()
 

Sponsored Links

 

Data Extraction Software

ABBYY FlexiCapture is the powerful data and document capture system 

www.abbyy.com 

 

3. Copy and paste the following VBA code to declare variables to use to read a query:

Dim nwDBS As Database
Dim nwRST As Recordset
Dim nwSQL As String
Set nwDBS = CurrentDb

4. Create an SQL query by typing the following code:
nwSQL = "SELECT Employees.[Last Name], Employees.[First Name] "
nwSQL = nwSQL & "FROM Employees;"
 

5. Open a new Recordset by using the SQL query created in the previous step:
Set nwRST = nwDBS.OpenRecordset(nwSQL)

 6. Move the Recordset to the first row of the query results and then move the Recordset forward two times to read the "Last Name" of the third row by typing the following code:

nwRST.MoveFirst
nwRST.MoveNext
nwRST.MoveNext
Debug.Print nwRST.Fields("[Last Name]").Value

7. End the procedure by typing the following code:

nwRST.Close
nwDBS.Close
End Sub

8. Press "Ctrl" and "G" to open the Immediate Window. Press "F5" to run your procedure and view the results through the Immediate Window.

Sponsored Links

• Bulk Email--Free Trial
  Email marketing to fit your needs. Start a Free 30-Day Trial Today.
  www.iContact.com
• PowerBasic vs VisualBasic
  Faster. No Run-Times. No Bloat! CGI, Macros, ASM, Reg Expressions
  www.powerbasic.com
• Visual SQL to XML
  Easy to use Data Mapping environment. Try now!
  www.ecrion.com
• 100% Free DataSheet (PDF)
  Over 20000000 DataSheet. It's Free. Multi Fast Search System.
  www.AllDataSheet.com