How to Load Data Into ComboBox from MS-Access Database

How to Load Data Into Combo Box from MS-Access Database

First Thing you need connection String to the Ms-Access Database,
In this Example , OleDb.OleDbConnection   Object is used to connection the Ms-Access Database 2007.

Here is an example of connection string for ms-Access

• Step 1. Declare this String in Module in VB.NET Project
• Step 2. Copy the below code and paste into your module

 Public ConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\DatabaseName.accdb ;Jet OLEDB:Database Password=passwordany;"  

• Step 3: Create a Method in the same modules as follows

  Public Sub LoadDataIntoComboBox(ByVal SQLStr As String, ByVal cob As ComboBox, ByVal FieldName As String, Optional ByVal AnyExtraItem As String = "")  
       Dim SqlConn As New OleDb.OleDbConnection  
       Dim Sqlcmmd As New OleDb.OleDbCommand  
       Try  
         SqlConn.ConnectionString = ConnStr   
         SqlConn.Open()  
         Sqlcmmd.Connection = SqlConn  
         Sqlcmmd.CommandText = SQLStr  
         Sqlcmmd.CommandType = CommandType.Text  
         Dim Sreader As OleDb.OleDbDataReader  
         Sreader = Sqlcmmd.ExecuteReader  
         cob.Items.Clear()  
         While Sreader.Read()  
           cob.Items.Add(Sreader(FieldName).ToString.Trim)  
         End While  
         If AnyExtraItem.Trim.Length > 0 Then  
           cob.Items.Add(AnyExtraItem)  
         End If  
         Sreader.Close()  
         Sreader = Nothing  
       Catch ex As Exception  
         MsgBox(ex.Message)  
       Finally  
         SqlConn.Close()  
         SqlConn.Dispose()  
         Sqlcmmd.Connection = Nothing  
       End Try  
   End Sub  

• Step 4: How to Use

 LoadDataIntoComboBox("Select distinct department from assets ", TxtDepartment, "department")  
 

LoadDataIntoComboBox("Select distinct assetname from assets ", TxtAssetName, "assetname")  

Here TxtDepartment  and TxtAssetName are the Comboboxes , assetname and department are the fields in Ms-Access Database table assets