Inner Join using VB programming – Inner join is used to combine two or more tables in a single output table in database programming. Inner join is very helpful when a join is required to merge some table in SQL query. In this tutorial we are going to join two columns of MS Access with VB programming.

Inner Join using VB programming

Before using Inner Join using VB programming, it is assumed that you have connected with your database file of MS Access. If you don’t know how to connect MS Access database file, you can visit the tutorial to connect MS Access database file using VB programming.

Connect to Database with VB Programming

Create a module.vb and rename it with “connectDatabase”, copy and paste the following code into the module

Imports System.Data.OleDb

Module connectDatabase

Public accessConnection As New OleDbConnection
Public accessCommand As New OleDb.OleDbCommand
Public accessAdopter As New OleDbDataAdapter
Public accessDataReader As OleDbDataReader
Public strSQL As String

Sub connect()
Try
If accessConnection.State = ConnectionState.Closed Then
accessConnection.ConnectionString = "Provider=microsoft.ace.oledb.12.0; data source=" & Application.StartupPath & "\Database1.accdb" & " ;"
accessConnection.Open()
Form1.Text = " Connected"
Else
Form1.Text = "Connection Is already Opend"
End If
Catch ex As Exception
Form1.Text = ex.Message.ToString
End Try
End Sub

End Module

After it, copy the following code, and paste it at form load, to connect with database

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
connectDatabase.connect()
End Sub

Next First of all for Inner Join using VB programming, we require two table with same table of ITEMID. The ITEMID column will help us to identify the matching contents between two tables. For the Inner Join I have the following table.

Table1 for Inner Join: The table1 has two columns which are ITEMID and ITEMNAME. The ITEMID has unique records to identify the ITEMNAME.

Table2 for Inner Join: The table2 has three columns with ITEMID (same as Table1, for relation between two tables), PRICE, and QTY columns.

Understanding with Inner Join

The following is strSQL which is used to join above two tables, the strSQL is define in next table which its parameters. The strSQL is

strSQL = “select Table2.ITEMID, Table1.ITEMNAME, Table2.PRICE, Table2.QTY From Table1 INNER Join Table2 ON Table1.ITEMID = Table2.ITEMID”

        strSQL = "select Table2.ITEMID, Table1.ITEMNAME, Table2.PRICE, Table2.QTY From Table1 INNER Join Table2 ON Table1.ITEMID = Table2.ITEMID"
        accessCommand.CommandText = strSQL
        accessCommand.Connection = accessConnection
        accessAdopter.SelectCommand = accessCommand
        accessDataReader = accessCommand.ExecuteReader
        ListView1.Items.Clear()
        While (accessDataReader.Read())
            With ListView1.Items.Add(accessDataReader("ITEMID")) 'Table2.ITEMID
                .subitems.add(accessDataReader("ITEMNAME")) 'Table1.ITEMNAME
                .subitems.add(accessDataReader("PRICE")) 'Table2.PRICE
                .subitems.add(accessDataReader("QTY")) ' Table2.QTY
            End With
        End While

Result of Inner Join

Inner Join using VB programming – after the code running of Inner Join using VB programming, the output Join operation on table1 and table2 is displayed.