How to Use a VB to Call Access a Query

Knowing how to query an Access database table using Microsoft Visual Basic.NET can make your Windows application more dynamic. Access is a relational database management system included in the Microsoft Office suite. VB.NET is a computer programming language used to create Windows applications. In a few steps, you can create a VB.NET application to access the Northwind database included with MS Office and query the customers table.

• 1

  Open Microsoft Visual Basic Express and click "New Project..." on the left pane of your screen. Double-click "Console Application."

• 2

  Type the following in the very first line of "Module1.vb":

  Imports System.Data.OleDb

• 3

  Type the following below "Sub Main()" to declare your variables:

  Dim conn As OleDbConnection

  Dim strSQL As String

  Dim ds As DataSet

  Dim tmpStr As String

• 4

  Type the following to create a new select query to query the "Customers" table:

  strSQL = "SELECT Customers.* FROM Customers;"

  conn = New OleDbConnection _

  ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\DemandStudios\Archive\Northwind 2007.accdb;")

• 5

  Type the following to create a new connection to your database:

  Dim myConnection As OleDbConnection = New OleDbConnection

  Dim da As OleDbDataAdapter

  da = New OleDbDataAdapter(strSQL, conn)

  ds = New DataSet

• 6

  Type the following to loop through all the records returned by the query and save them to a string variable:

  da.Fill(ds, "Customers")

  For Each rowCust In ds.Tables("Customers").Rows

  tmpStr = tmpStr & rowCust("Company").ToString() & "|"

  tmpStr = tmpStr & rowCust("Last Name").ToString() & "|"

  tmpStr = tmpStr & rowCust("First Name").ToString() & "|" & vbLf

  Next

• 7

  Type the following to output the results to the Console Window:

  System.Console.Write(tmpStr)

  MsgBox("Done!")

• 8

  Type the following to close your database connection:

  myConnection.Close()

  Press "F5" to run your program.