Querying Table Data Using Visual Basic Code in Ms Access

<h1>Querying Table Data Using Visual Basic Code in Ms Access</h1>Author: <a title="Nicholas Brown" href="http://www.articlesbase.com/authors/nicholas-brown/80872.htm">Nicholas Brown</a> 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)
‘************************
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.About the Author: Nicholas Brown is a recognized authority on the subject of Access Databases. He is the founder of Database Technology Services (DTS). DTS is a leader in custom Access database development. DTS programmers create databases for corporations, small businesses and individuals. Visit <a href="http://www.dts-consultants.com" target="_blank">www.dts-consultants.com</a> to see all of the services DTS has to offer.Article Source: <a href="http://www.articlesbase.com/">ArticlesBase.com</a> - <a href="http://www.articlesbase.com/databases-articles/querying-table-data-using-visual-basic-code-in-ms-access-558135.html" title="Querying Table Data Using Visual Basic Code in Ms Access">Querying Table Data Using Visual Basic Code in Ms Access</a>

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)

‘************************

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.

Nicholas Brown is a recognized authority on the subject of Access Databases. He is the founder of Database Technology Services (DTS). DTS is a leader in custom Access database development. DTS programmers create databases for corporations, small businesses and individuals. Visit www.dts-consultants.com to see all of the services DTS has to offer.

Rate this Article:

0 / 5 stars - 0 vote(s)

Re-Publish

Article Source: http://www.articlesbase.com/databases-articles/querying-table-data-using-visual-basic-code-in-ms-access-558135.html

Article Tags: Development, Programming, Table, Oracle, Microsoft Access, Database, Visual Basic, SQL, Query, Recordset

Related Videos
Related Articles
Ask / Related Q&A

Play

How to Show the Control Centre

Play

How to Do Chair Aerobics

Play

How to use Microsoft Access - Creating Queries and Reports - part 11

Play

Microsoft Expressions Web - Looking at How Database-Driven Sites Work

Play

How to Use the Access Options in Microsoft Access 2007

Latest Databases Articles
More from Nicholas Brown

8 GB USB DRIVE

By: Phillip Roth | 29/12/2009
USB flash drive consists of a small printed circuit board carrying the circuit elements and a USB connector insulated electrically and protected inside a plastic, metal, or rubberized case which can be carried in a pocket.

Dsi Games download - How to Download The Top Dsi Games!

By: Sarah Brown | 28/12/2009
The Nintendo Dsi is one of the best Nintendo gaming Systems ever, the New Dsi games are packed with the best graphics and entertainment you can have in a gaming device. So with all the new Dsi games on the market you going to be tempted to buy all your favourite Dsi games but this will lead you to spend your rent on your gaming addiction.

Where to Download PSP Go Games, Movies & Music Revealed!

By: Sarah Brown | 27/12/2009
The new PSP Go hand held gaming system from Sony has hit the market and people have picked it up like it's candy. Now, the problem many are facing is figuring out where to download PSP Go games, movies & music. Sure, you can get downloads from the official Playstation store which you can access directly from the system, but it can be quite expensive. Games can run up to $40 and the music and video selection isn't that great.

PSP Go Download Center Reviw - Is PSP Go Download Center a Scam?

By: Sarah Brown | 27/12/2009
PSP has around the gaming market for a few years back, the Sony PSP has gained the popularity in hand-held gaming console. It has surpassed the Nintendo DS handheld, which no doubt can’t match with what PSP has to offer. Since the PSP is under the trademark of Sony Playstation, the handheld has earned its popularities and made the UMD games, movies and music became quite expensive.

Support Information About AA Online

By: Ed Philips | 26/12/2009
Discover how to quit drinking as revealed in Ed Philips "Alcoholics Anonymous Online" support guide, which offers tried and tested alcoholic addiction methods to quit drinking within 21 days.

DSi Download Center - Get Nintendo DS And DSi Games For Free and Legally!

By: Sarah Brown | 24/12/2009
Many people are sick of buying expensive Nintendo DS and DSi games cartridge and they want to paly games while saving their money, so I will show you where you can download Nintendo DS and Dsi games for free and legally. Not many people are aware that copyrighted Nintendo DS and DSi games can be downloaded on the internet legally. DSi Download Center is a membership based program that has more than 250,000 Downloads making it the Biggest Database for the DSI!

Know the information USB sticks

By: John Milton | 24/12/2009
Now in the market there is high demand of usb sticks among its users. Seeing this the producers of usb sticks now focussing more on its reliability.

Download Jak and Daxter The Lost Frontier PSP Game

By: Sarah Brown | 23/12/2009
PSP UMD discs are becoming quite expensive these days, many gamers are seeking for new alternatives to get the newest and their favorite PSP games for cheap. This article will expose on how and where you can download Jak and Daxter The Lost Frontier for less than $0.01. Jak and Daxter The Lost Frontier is already released on PSP handheld, so you can proceed to download the game instantly.

Product Delivery: Think Like the Customer by Nicholas Brown

By: Nicholas Brown | 15/10/2008 | Customer Service
Tips and tricks on delivering a product or service that meets all of the needs of your client.

Ms Access Databases Don't Have to Look Unprofessional by Nicholas Brown

By: Nicholas Brown | 30/09/2008 | Databases
Tips and tricks to help maximize the usability of your database by improving the user interface.

Using Comments in Your Programming Source Code by Nicholas Brown

By: Nicholas Brown | 30/09/2008 | Programming
Save time and money by adding comments to your source code.

Increasing Small Business Productivity by Nicholas Brown

By: Nicholas Brown | 27/09/2008 | Small Business
Tips and tricks to help maximize business profits by increasing project efficiency and organization.

Importing External Access Database Tables Using Visual Basic by Nicholas Brown

By: Nicholas Brown | 18/09/2008 | Programming
A brief how-to on importing external database tables using Visual Basic code. This tool can help users to automate their databases, saving time and money.

Pulling Data From Multiple Tables Into a Single Query in Ms Access by Nicholas Brown

By: Nicholas Brown | 17/09/2008 | Databases
A brief how-to on linking multiple tables within a single query. With this tool, users can develop more complex queries, reducing the need for several queries. This will also reduce the size of the database making it more efficient and easier to navigate.

Becoming an Expert Ms Access Database Programmer

By: Nicholas Brown | 11/09/2008 | Programming
Tips and tricks on becoming your own Access database expert. Follow these guidelines and you will be well on your way to becoming a leader in database development.