Ready to learn more?
Free
Estimate!
Estimate!
Tell us about your project
needs. A knowledgeable
Project Manager will
provide a free estimate.
needs. A knowledgeable
Project Manager will
provide a free estimate.
Access Database Development | Business Software Experts | Microsoft Access Technical Support
Querying Table Data Using Visual Basic |
- Receive a free estimate
- Learn about our company
- Read about our methodology
- Review our clients
Database Professional
In order to fully utilize the capabilities of MS Access, one must not only learn the Visual Basic (VB) programming
language, but should also learn Standard Query Language (SQL). Once a grasp of these two languages have been
obtained, MS Access users can begin to build faster and more efficient databases.
One tool that has proved itself very useful to me over the years is querying data from tables or queries using VB and
SQL code. A brief introduction to this process is presented in this article. To best understand this process, an example
is provided below along with an explanation of its parts.
‘*********CODE***********
Dim rstTemp As Recordset
Dim strSQL As String
Dim routeNum As Integer
strSQL = "SELECT [Route], [Main Route PM], [Intersecting Route], [IntBeginPM], [IntEndPM] “
strSQL = strSQL + “FROM Intersections_list WHERE (((CStr([Route])) = """ + cmbRouteQuery + """));"
Set rstTemp = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If (Not (rstTemp.EOF)) Then
rstTemp.MoveFirst
routeNum = rstTemp(0)
‘************************
After the initial variable declarations, the code assigns an SQL statement to the string variable strSQL. This statement
directs Access to gather all the data in the Route, Main Route PM, Intersecting Route, IntBeginPM, and IntEndPM
fields of the table named Intersections_list. Furthermore, it directs Access to only gather information from these fields
where the Route field is equal to a value held in the combo box cmbRouteQuery.
Once the SQL statement has been set, it is passed to the next line of code which executes it. It should be noted that
the dbOpenDynaset variable is built into Access and holds an integer value that changes the type of recordset to
open. For most general purposes, using dbOpenDynaset will work just fine.
The “if statement” in the code example verifies that the recordset just created contains information. If information is
present, the code directs Access to move to the first record in the recordset. The code then stores the route in the
first record (routeNum = rstTemp(0)) in the variable routeNum to be used for later use.
language, but should also learn Standard Query Language (SQL). Once a grasp of these two languages have been
obtained, MS Access users can begin to build faster and more efficient databases.
One tool that has proved itself very useful to me over the years is querying data from tables or queries using VB and
SQL code. A brief introduction to this process is presented in this article. To best understand this process, an example
is provided below along with an explanation of its parts.
‘*********CODE***********
Dim rstTemp As Recordset
Dim strSQL As String
Dim routeNum As Integer
strSQL = "SELECT [Route], [Main Route PM], [Intersecting Route], [IntBeginPM], [IntEndPM] “
strSQL = strSQL + “FROM Intersections_list WHERE (((CStr([Route])) = """ + cmbRouteQuery + """));"
Set rstTemp = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If (Not (rstTemp.EOF)) Then
rstTemp.MoveFirst
routeNum = rstTemp(0)
‘************************
After the initial variable declarations, the code assigns an SQL statement to the string variable strSQL. This statement
directs Access to gather all the data in the Route, Main Route PM, Intersecting Route, IntBeginPM, and IntEndPM
fields of the table named Intersections_list. Furthermore, it directs Access to only gather information from these fields
where the Route field is equal to a value held in the combo box cmbRouteQuery.
Once the SQL statement has been set, it is passed to the next line of code which executes it. It should be noted that
the dbOpenDynaset variable is built into Access and holds an integer value that changes the type of recordset to
open. For most general purposes, using dbOpenDynaset will work just fine.
The “if statement” in the code example verifies that the recordset just created contains information. If information is
present, the code directs Access to move to the first record in the recordset. The code then stores the route in the
first record (routeNum = rstTemp(0)) in the variable routeNum to be used for later use.
Home >> Database Articles >> Querying Table Data Using Visual Basic
| Home | Company | Services | Methodology | Technologies | Clients | Contact Us | Resources | Database Help |