Add Edit Delete Records in Visual Basic 6 and MS Access

This tutorial teaches you how to add edit and delete records using Visual Basic 6 and MS Access. 

====================

CODES FOR MODULE 1 

====================

Option Explicit

Public con As ADODB.connection

Public ado As ADODB.Recordset

Public useradd As Boolean, edituser As Boolean

____________________________________________________________________________

Public Sub connection()

Set con = New ADODB.connection

con.CursorLocation = adUseClient

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & App.Path & "\Database1.mdb"

con.Open

End Sub

====================

CODES FOR FRMMAIN 

====================

Option Explicit

________________________________________________________________

Private Sub cmdadd_Click()

useradd = True

edituser = False

Load frmadduser

frmadduser.Show

frmadduser.Caption = "Add User"

frmmain.Enabled = False

End Sub

________________________________________________________________

Private Sub cmddelete_Click()

Dim res As VbMsgBoxResult

With ado

    If .BOF And .EOF = True Then

        MsgBox "Empty Database!", vbExclamation, "Error"

    Exit Sub

    Else

    res = MsgBox("Are you sure you want to delete " & vbCrLf & vbCrLf & "Username: " & ado!user_name, vbYesNo + vbQuestion, "confirm")

        If res = vbYes Then

        .Delete

        .Requery

        Set DataGrid1.DataSource = ado

        Else

        Exit Sub

        End If

        End If

        End With

End Sub

_______________________________________________________________

Private Sub cmdedit_Click()

With ado

    If .BOF And .EOF = True Then

        MsgBox "Empty Database!", vbExclamation, "Error"

    Exit Sub

    Else

        edituser = True

        useradd = False

        frmmain.Enabled = False

        Load frmadduser

        frmadduser.Show

        frmadduser.Caption = "Edit User"

        End If

        End With

End Sub

_______________________________________________________________

Private Sub Form_Load()

Module1.connection

On Error Resume Next

Set ado = New ADODB.Recordset

ado.Open "Select * From tbluser", con, adOpenStatic, adLockPessimistic

Set DataGrid1.DataSource = ado

End Sub

======================

CODES FOR FRMADDUSER

======================

Option Explicit

Dim modeval As Boolean

_____________________________________________________________________________

Private Sub cmdcancel_Click()

frmmain.Enabled = True

Unload Me

End Sub

_____________________________________________________________________________

Private Sub cmdsave_Click()

Dim resp As VbMsgBoxResult

    If txtuser.Text = "" Or txtpass.Text = "" Or txtpass2.Text = "" Then

    MsgBox "Do not leave empty text field(s).", vbExclamation, "Error"

    Exit Sub

Else

    If txtpass.Text <> txtpass2.Text Then

    MsgBox "Password does not matched.", vbExclamation, "Error"

    Exit Sub

Else

    Call validate_user

    If modeval = False Then

save:

    Dim res As VbMsgBoxResult

    res = MsgBox("Do you want to save this record?", vbYesNo + vbQuestion, "save?")

    If res = vbYes Then

        If useradd = True And edituser = False Then

            ado.AddNew

edit:

    ado!user_name = txtuser.Text

    ado!password = txtpass.Text

    ado!password2 = txtpass2.Text

    If useradd = True And edituser = False Then

        ado.Update

        ado.Requery

    ElseIf edituser = True And useradd = False Then

        ado.UpdateBatch adAffectCurrent

    End If

        frmmain.Enabled = True

        Load frmmain

        frmmain.Show

        Unload Me

    ElseIf edituser = True And useradd = False Then

        GoTo edit:

    End If

    Else

        Exit Sub

    End If

    ElseIf modeval = True Then

        If txtval.Text = txtuser.Text Then

    GoTo save:

    Else

    MsgBox "Duplicate entries are not allowed." & vbCrLf & vbCrLf & "user_name" & "''" & txtuser.Text & "'' already exists.", vbExclamation, "Error"

    txtuser.SetFocus

    End If

    End If

    End If

    End If

End Sub

_______________________________________________________________________________

Private Sub Form_Load()

    If edituser = True And useradd = False Then

        txtuser.Text = ado!user_name

        txtpass.Text = ado!password

        txtval.Text = ado!user_name

    Else

        Exit Sub

    End If

End Sub

_______________________________________________________________________________

Private Sub validate_user()

Dim rs As New ADODB.Recordset

rs.Open "Select * From tbluser Where user_name = '" & txtuser.Text & "'", con, adOpenStatic, adLockReadOnly

    If rs.RecordCount < 1 Then

    modeval = False

    Exit Sub

Else

    modeval = True

End If

    Set rs = Nothing

End Sub

CLICK HERE TO DOWNLOAD THE SOURCE CODE

Display Records using VB6 and MS Access

This tutorial will teach you how to display records from MS Access to Visual Basic 6.

We will use Data Grid to store information from our database.

CODES FOR MODULE1
========================================================================
Option Explicit

Public con As ADODB.connection
Public ado As ADODB.Recordset
________________________________________________________________
Public Sub connection()
Set con = New ADODB.connection
con.CursorLocation = adUseClient
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\Database1.mdb"
con.Open
End Sub
=================================================================
CODES FOR FORM1

Option Explicit
________________________________________________________________
Private Sub Form_Load()
Module1.connection
On Error Resume Next
Set ado = New ADODB.Recordset
ado.Open "Select * From Table1", con, adOpenStatic, adLockPessimistic
Set DataGrid1.DataSource = ado
End Sub
=================================================================

CLICK HERE TO DOWNLOAD THE SOURCE CODE

Add Records using VB.NET 2008 and Microsoft Access

This tutorial will teach you how to add records in the database using VB.NET and MS Access

You should save the database in the "Bin" folder.

Here are the codes:

CODES FOR MODULE1
===================================
Imports System.Data.OleDb
________________________________________________________________
Module Module1
    Dim Provider As String
    Public OleCn As New OleDbConnection()
    Public OleDa As New OleDbDataAdapter()
________________________________________________________________
    Public Function OledbConnectionString() As String
        Provider = "provider=microsoft.jet.oledb.4.0; data source=..\Database1.mdb"
        Return Provider
    End Function
________________________________________________________________
    Public Sub opencon()
        If OleCn.State <> ConnectionState.Open Then
            OleCn.ConnectionString = OledbConnectionString()
            OleCn.Open()
        End If
    End Sub
________________________________________________________________
    Public Sub Initialized()
        OleDa.SelectCommand = New OleDbCommand
        OleDa.SelectCommand.CommandText = "SELECT * FROM tblemp"
        OleDa.SelectCommand.Connection = OleCn
    End Sub
________________________________________________________________
    Public Sub closecon()
        OleCn.Close()
    End Sub
End Module

CODES FOR FORM1
============================

Imports System.Data.OleDb
_________________________________________________________________
Public Class Form1
    Dim OleDr As OleDbDataReader
    Dim Item As New ListViewItem
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call opencon()
        Call LoadLv()
        Call closecon()
    End Sub
_________________________________________________________________
    Public Sub LoadLv()
        ListView1.Items.Clear()
        Call Initialized()
        OleDr = OleDa.SelectCommand.ExecuteReader()
        Do While OleDr.Read()
            Item = ListView1.Items.Add(OleDr("EmployeeID").ToString())
            Item.SubItems.Add(OleDr("FirstName").ToString())
            Item.SubItems.Add(OleDr("LastName").ToString())
            Item.SubItems.Add(OleDr("Gender").ToString())
        Loop
        OleDr.Close()
    End Sub
_________________________________________________________________
    Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
        Form2.ShowDialog()
    End Sub


End Class

CODES FOR FORM2
================================
Imports System.Data.OleDb
_________________________________________________________________
Public Class Form2

    Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
        If txtid.Text = "" Or txtfn.Text = "" Or txtfn.Text = "" Or cmbgender.Text = "" Then
            MsgBox("Please do not leave a blank textfield!", MsgBoxStyle.Critical)
            Exit Sub
        End If
        Try
            Call opencon()
            OleDa.InsertCommand = New OleDbCommand()
            OleDa.InsertCommand.CommandText = "INSERT INTO tblemp (EmployeeID , FirstName , LastName , Gender) " & _
            "VALUES (@EmployeeID, @FirstName, @LastName, @Gender)"
            OleDa.InsertCommand.Connection = OleCn
            OleDa.InsertCommand.Parameters.Add("@EmployeeID", OleDbType.VarWChar, 50, "EmployeeID").Value = txtid.Text
            OleDa.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarWChar, 50, "FirstName").Value = txtfn.Text
            OleDa.InsertCommand.Parameters.Add("@LastName", OleDbType.VarWChar, 50, "LastName").Value = txtln.Text
            OleDa.InsertCommand.Parameters.Add("@Gender", OleDbType.VarWChar, 50, "Gender").Value = cmbgender.Text

            OleDa.InsertCommand.ExecuteNonQuery()
            Call Form1.LoadLv()
            Call closecon()
            MsgBox("Record has been saved.", MsgBoxStyle.Information)
            Me.Close()

        Catch ex As Exception

            MsgBox("Cannot be save. Existing EmployeeID", MsgBoxStyle.Critical)
            Call closecon()
            txtid.Focus()
            txtid.SelectAll()

        End Try
    End Sub
________________________________________________________________

    Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Call cleartext()
        txtid.Focus()
        Form1.ListView1.SelectedItems.Clear()
    End Sub
________________________________________________________________
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub cleartext()
        Me.txtid.Clear()
        Me.txtfn.Clear()
        Me.txtln.Clear()
    End Sub
________________________________________________________________
    Private Sub cmdcancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel.Click
        Me.Close()
    End Sub

End Class

To avoid some errors, you should do the following:

-Check the name of your database. 

-Check the name of your table in your database. 

-The vb.net project and database file should be on the same folder.

-Check the variables if you typed them correctly.

CLICK HERE TO DOWNLOAD THE SOURCE CODE

If you have any questions, feel free to post a comment below.

Create Login with Database using VB6 and MS Access

This tutorial will teach you how to create Log-in with database using VB6 and MS Access.

Keep in mind that your project and database file should be on the same folder. 

Here are the codes: 

CODES FOR MODULE1

====================================================================

Option Explicit

Public cnn As ADODB.Connection

_____________________________________________________________

Public Sub getconnected()

Set cnn = New ADODB.Connection

cnn.CursorLocation = adUseClient

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

cnn.Open

End Sub

==============================================================

NOTE: The red text is the name of your database file.

==============================================================

CODES FOR FORM1 

==============================================================

Option Explicit

Private Sub cmdlogin_Click()

If txtuser.Text = "" Then

MsgBox "Username is Empty.", vbInformation

txtuser.SetFocus

Exit Sub

ElseIf txtpass.Text = "" Then

MsgBox "Password is Empty"

txtpass.SetFocus

Exit Sub

Else

Call login

End If

End Sub

Private Sub login()

Module1.getconnected

Dim rs As New ADODB.Recordset

rs.Open "Select * From Table1 Where username = '" & txtuser.Text & "'", cnn, adOpenStatic, adLockReadOnly

If rs.RecordCount < 1 Then

MsgBox "Username is Invalid. Please try again.", vbInformation

txtuser.SetFocus

Exit Sub

Else

If txtpass.Text = rs!pass Then

Unload Me

Load Form2

Form2.Show

Exit Sub

Else

MsgBox "Password is Invalid. Please try again.", vbInformation

txtpass.SetFocus

Exit Sub

End If

End If

Set rs = Nothing

End Sub

Private Sub Form_Load()

End Sub

==============================================================

NOTE: The red text is the name of your table in the database.

==============================================================

To avoid some errors, you should do the following:

-Check the name of your database. 

-Check the name of your table in your database. 

-The vb6 project and database file should be on the same folder.

-Check the variables if you typed them correctly.

CLICK HERE TO DOWNLOAD THE SOURCE CODE

If you have any questions, feel free to post a comment below.