Sample ADO DB Via Code

'//PROGRAM TO ACCESS DATABASE WITH ADO VIA CODE
'//AUTHOR: IBRAHIM ITAMBO
'//VERSION: 1.0


'These two objects must be accessible by all Subs
Public cJKUAT As ADODB.Connection
Public rsStudent As ADODB.Recordset

Private Sub cmdAdd_Click()

Call ClearFields
cmdAdd.Visible = False
cmdDelete.Visible = False
cmdOK.Visible = True
cmdCancel.Visible = True
End Sub

Private Sub cmdCancel_Click()
With rsStudent
.CancelUpdate
.MoveLast
End With
Call UpdateFields
cmdAdd.Visible = True
cmdDelete.Visible = True
cmdOK.Visible = False
cmdCancel.Visible = False
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdDelete_Click()
With rsStudent
.Delete
.MoveLast
End With
Call UpdateFields
End Sub

Private Sub cmdMoveFirst_Click()

rsStudent.MoveFirst
Call UpdateFields
End Sub

Private Sub cmdMoveLast_Click()

rsStudent.MoveLast
Call UpdateFields
End Sub

Private Sub cmdMoveNext_Click()

rsStudent.MoveNext
If rsStudent.EOF Then
rsStudent.MoveLast
MsgBox "U R on the Last Record"
End If
Call UpdateFields
End Sub

Private Sub cmdMovePrevious_Click()

rsStudent.MovePrevious
If rsStudent.BOF Then
rsStudent.MoveFirst
MsgBox "U R on the First Record"
End If
Call UpdateFields
End Sub

Private Sub cmdOK_Click()
cmdAdd.Visible = True
cmdDelete.Visible = True
cmdOK.Visible = False
cmdCancel.Visible = False
rsStudent.AddNew
On Error GoTo errHandler
rsStudent.Fields("Name") = txtName.Text
rsStudent.Fields("RegNo") = txtRegNo.Text
rsStudent.Fields("Gender") = txtGender.Text
rsStudent.Fields("Age") = CInt(txtAge.Text)
rsStudent.Update
rsStudent.MoveLast
Call UpdateFields
Exit Sub

errHandler:
MsgBox "Update Failed", vbOKOnly, "ERROR"
rsStudent.MoveLast
Call UpdateFields
End Sub

Private Sub Form_Load()

Set cJKUAT = New ADODB.Connection
Set rsStudent = New ADODB.Recordset
Dim sConnect As String
'set lock type to enable updates to the recordset
rsStudent.LockType = adLockOptimistic
'initialize connection string
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=JKUAT.mdb"
'make connection
cJKUAT.Open sConnect
rsStudent.Source = "select * from students"
Set rsStudent.ActiveConnection = cJKUAT
rsStudent.Open
Call UpdateFields
cmdOK.Visible = False
cmdCancel.Visible = False
End Sub

Public Sub UpdateFields()
txtName.Text = rsStudent.Fields("Name")
txtRegNo.Text = rsStudent.Fields("RegNo")
txtGender.Text = rsStudent.Fields("Gender")
txtAge.Text = rsStudent.Fields("Age")
End Sub

Public Sub ClearFields()
txtName.Text = ""
txtRegNo.Text = ""
txtGender.Text = ""
txtAge.Text = ""
End Sub