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