Private Sub Command1_Click(Index As Integer)

If NameText.Text = "" Or PhText.Text = "" Or AgeText.Text = "" Then

MsgBox "Please Enter All Field", vbInformation, "Test Database"

Exit Sub

End If

Dim strSql As String

Dim Constr As String

Dim con As New ADODB.Connection 'Declare variable to set new ADODB connection

Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False" 'This is a string which are used to set path and security of access database

strSql = "insert into Test (Name,Phoneno,Age) " & _

" values('" & NameText.Text & "','" & PhText.Text & "','" & AgeText.Text & "')" 'This is the column of access database and its value respectively which we want to store in the database

con.Open Constr 'This code is used to open database for inserting value

Debug.Print strSql

con.Execute (strSql) 'This is code for inserting all the value respectively it execute the strsql string

con.Close 'Database should be closed after completing our operation and re-open to perform new operation

MsgBox "Record Inserted into Database", vbInformation, "Test Database Connection"

NameText.Text = ""

PhText.Text = ""

AgeText.Text = ""

End Sub

 

 

Private Sub Command3_Click(Index As Integer)

Dim con As New ADODB.Connection 'Declare variable to set new ADODB connection

Dim rs As New ADODB.Recordset 'Declare variable to set new ADODB Recordset

Dim sn As String

Dim Constr As String

Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"

con.Open Constr 'First we need to open Constr means path of access database

rs.Open "Select * from Test order by sn asc", con, adOpenStatic, adLockReadOnly 'If we need to delete value from database, then we need adOpenStatic, adLockReadOnly property

For a = 1 To ListView1.ListItems.Count 'This code used to get all data from database and show in list one by one using loop

If ListView1.ListItems.Item(a).Checked = True Then ' Delete only checked item

sn = rs("SN") 'To get current column Serial Number to delete database

sql = "delete from Test where SN=" & sn 'This code only delete those database that we provide serial number using variable _

'sn = rs("SN") here sn is variable and rs("SN" is column in our database which are AutoNumber properties

con.Execute (sql)

End If

rs.MoveNext

Next

con.Close

Call ViewDatabase 'For refresh List View

End Sub

 

 

Private Sub Command4_Click()

Call ViewDatabase

End Sub

 

 

Private Sub Command5_Click(Index As Integer)

Unload Me

End Sub

 

 

Private Sub Command6_Click(Index As Integer)

Dim rs As New ADODB.Recordset 'Declare variable to set new ADODB Recordset

Dim sn As Integer

Dim Constr As String

Dim con As New ADODB.Connection

Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"

con.Open Constr

rs.Open "Select * from Test order by sn asc", con, adOpenStatic, adLockReadOnly 'If we need to update value from database, then we need adOpenStatic, adLockReadOnly property

For a = 1 To .ListView1.ListItems.Count 'This code used to get all data from database and show in list one by one using loop

If .ListView1.ListItems.Item(a).Selected = True Then ' Update only checked item

sn = rs("SN") 'To get current column Serial Number to update database

End If

rs.MoveNext

Next

con.Close

strSql = "update Test " & _

" set Name='" & NameText.Text & "',Phoneno='" & PhText.Text & "',Age='" & AgeText.Text & "' where SN=" & sn

'-- Open the Connection

'

con.Open Constr

con.Execute (strSql)

con.Close

MsgBox "Database update successfully!", vbInformation, "Test Database"

Unload Me

End Sub

 

 

Private Sub Forms_Activate()

NameText.SetFocus

ViewDatabase

End Sub

 

 

Public Sub ViewDatabase()

Dim con As New ADODB.Connection

Dim Constr As String

Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"

con.Open Constr

Dim rs As New ADODB.Recordset

rs.Open "Select * from Test order by SN asc", con, adOpenStatic, adLockReadOnly

Dim plist As ListView 'Set new object as plist

Set plist = ListView1

Dim oCH As ColumnHeader 'Declare variable for column header in list view

Dim oLI As ListItem 'Declare variable to list Item

Dim oSI As ListSubItem 'Declare variable to list SubItem

With plist

.ListItems.Clear

If rs.RecordCount <> 0 Then

Dim Row

For Row = 1 To rs.RecordCount 'Here for loop is used to get first database to last database to list view

Set oLI = .ListItems.Add() 'This code is used to insert value into First column of list view

oLI.Text = rs("Name") 'This code is used to get values from database

Set oSI = oLI.ListSubItems.Add() 'This code is used to insert value into second column of list view

oSI.Text = rs("Phoneno") 'This code is used to get values from database

Set oSI = Nothing 'This is most required in loop neither List view can't show proper list

Set oSI = oLI.ListSubItems.Add() 'This code is used to insert value into Thired column of list view

oSI.Text = rs("Age")

Set oSI = Nothing

rs.MoveNext

Next

End If

End With

con.Close

End Sub

 

 

Private Sub Form_Load()

Dim con As New ADODB.Connection 'Declare variable to set new ADODB connection

Dim rs As New ADODB.Recordset 'Declare variable to set new ADODB Recordset

Dim sn As Integer

Dim Constr As String

Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"

con.Open Constr 'First we need to open Constr means path of access database

rs.Open "Select * from Test order by sn asc", con, adOpenStatic, adLockReadOnly 'If we need to update value from database, then we need adOpenStatic, adLockReadOnly property

For a = 1 To .ListView1.ListItems.Count 'This code used to get all data from database and show in list one by one using loop

If .ListView1.ListItems.Item(a).Selected = True Then

sn = rs("SN") 'To get current column Serial Number to edit database

NameText.Text = rs("Name")

PhText.Text = rs("Phoneno")

AgeText.Text = rs("Age")

End If

rs.MoveNext

Next

con.Close

Exit Sub