Access

Querying Table Data Using Visual Basic Code in MS Access

by

In order to fully utilize the capabilities of MS Access, one must learn 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)

'************************

Read more