ADD, EDIT, UPDATE, DELETE, SEARCH AND NAVIGATION IN VB 6.0 TO MS ACCESS USING ADO

PROGRAM DESCRIPTION


This program is capable of the following functionality:
-Add record
-Delete record
-Search and Filter record
-Edit and Update record
-Navigate into record

with Status bar using ADO connection to connect to Microsoft Access Database.


SCREENSHOT

CODE:

MODULE

Option Explicit

Public conn As ADODB.Connection
Public rs As ADODB.Recordset

Sub connect()
Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\myDatabase.mdb;Persist Security Info=False"
Set rs = New ADODB.Recordset
    rs.ActiveConnection = conn
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
    rs.Source = "SELECT * FROM MyTable"
    rs.Open
End Sub

Sub main()
connect
frmMain.Show
End Sub

FORM 1

Private Sub cmdDelete_Click()
On Error Resume Next
If MsgBox("Data is not recoverable!", vbExclamation + vbOKCancel, "Confirm Delete") = vbOK Then
rs.Delete
End If
End Sub

Private Sub cmdFirst_Click()
rs.MoveFirst
Call stat
End Sub

Private Sub cmdLast_Click()
rs.MoveLast
Call stat
End Sub

Private Sub cmdNext_Click()
If rs.EOF = True Then
    rs.MoveFirst
    Call stat
Else
    rs.MoveNext
    Call stat
End If
End Sub

Private Sub cmdPrevious_Click()
If rs.BOF = True Then
    rs.MoveLast
    Call stat
Else
    rs.MovePrevious
    Call stat
End If
End Sub

Private Sub Command1_Click()
rs.Filter = adFilterNone
rs.Requery
End Sub

Private Sub Command2_Click()
If MsgBox("Close Applect?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
    End
End If
End Sub

Private Sub Form_Load()

Set DataGrid1.DataSource = rs

End Sub

Sub stat()
StatusBar1.Panels(1).Text = "Record " & rs.AbsolutePosition & " of " & rs.RecordCount
End Sub

Private Sub mnuAdd_Click()
frmAdd.Show
End Sub

Private Sub cmdSave_Click()
If txtid.Text = "" Or txtFn.Text = "" Or txtMi.Text = "" Or txtLn.Text = "" Then
    MsgBox "Some fields are still empty!", vbExclamation, "Input Error"
Else
rs.AddNew
rs("studId") = txtid.Text
rs("FirstName") = txtFn.Text
rs("MI") = txtMi.Text
rs("LastName") = txtLn.Text
rs.Update
MsgBox "Record Added Successfusly!", vbInformation, "Add Record"
Call clear
End If
End Sub

Sub clear()
txtid.Text = ""
txtFn.Text = ""
txtMi.Text = ""
txtLn.Text = ""
txtFn.SetFocus
End Sub

Private Sub txtSearch_Change()
If txtSearch.Text = "" Then
Call Form_Load
Me.Show
Else
rs.Filter = "FirstName LIKE '" & Me.txtSearch.Text & "*'"
Set DataGrid1.DataSource = rs
End If
End Sub

DOWNLOAD HERE: Full Project Source Code Above and Database (ADO connection)

You might also like:

ADODC in VB 6.0 to MS Access with DataGrid Control

Advance Login Form for System Security

Lesson 3 - Working With Controls

Simple Login / Security in VB 6.0