How to Connect to the Access Database in VB.net

Query the Northwind employees table in Visual Basic.

Connecting to a Microsoft Access database using Visual Basic.NET can make your VB application more dynamic by using database data directly from your application. The quickest way to connect to your Access database is by using ActiveX Data Objects (ADO). ADO provides a COM-based application-level interface for OLE DB data providers. Once you establish the connection, you can query database tables through the use of "IDbDataAdapter" and "DataSet" objects.

Things You'll Need

• Microsoft Visual Studio
• Northwind Access database

Instructions

1. Launch Microsoft Visual Studio, click "New Project" from the left pane of your computer screen, and expand "Visual Basic" below "Installed Templates." Click "Windows" and double-click "Windows Forms Application" from the center of the dialog window to create a new project.

2. Double-click "Button" to add a new button to your form. Double-click "Button1" to create a new click event for this button.

3. Define the connection string to the Northwind Access database:

   Dim dbConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Northwind 2007.accdb;"

   Dim dbCon As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(dbConString)

4. Define a SQL query statement to query the "Employees" table:

   Dim qryStr As String = "SELECT Employees.* FROM Employees"

   Dim dbCmd As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand

   dbCmd.CommandText = qryStr

   dbCmd.Connection = dbCon

5. Create a data adapter and fill a "DataSet" with the query results:

   Dim da As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter

   da.SelectCommand = dbCmd

   Dim ds As System.Data.DataSet = New System.Data.DataSet

   da.Fill(ds)

6. Display the results in the "DataSet" using a "DataGridView" control:

   Me.DataGridView1.DataSource = ds.Tables(0)

7. Press "F5" to run the program and click "Button1."

References

• Microsoft: Data Programming with Access 2010