Visual Basic 6 Add, Edit, Delete, and Clear Records

If you are handling database add, edit, and delete are the common functions that you need to know and understand.

I am going to show you the simple way on how to create these functions in Visual Basic 6 using MS Access 2003 database. I am also going to add clear function which is use to delete all the records in the database in one click.

In this topic it is very important that you already know about How to connect Visual Basic 6 to Microsoft Access 2003, and the fundamentals of Visual Basic 6 and SQL code.

Add Function:

With rst
    .ActiveConnection = con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "Table1"
End With

With rst
    .AddNew
    .Fields!input = Trim$(StrConv(Text1, vbProperCase))
    .Update
    MsgBox Text1.Text & " Successfully Saved", vbInformation
End With

rst.Close
Set rst = Nothing

Edit Function:

rst.Open "select * from Table1 where id=" & Trim$(Label4.Caption) & "", con, adOpenDynamic, adLockOptimistic

With rst

    .Fields!input = Trim$(StrConv(Text1, vbProperCase))
    .Update
    MsgBox Text1.Text & " Successfully Edited", vbInformation
End With

rst.Close
Set rst = Nothing

Delete Function:

With rst

    .ActiveConnection = con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "Select * from Table1"

End With

With cmd

    .ActiveConnection = con
    .CommandType = adCmdText
    .CommandText = "delete from Table1 where id=" & Trim$(Label4.Caption) & ""
    .Execute
    MsgBox Text1.Text & " Successfully Deleted", vbInformation
End With

Clear Function:

With rst

    .ActiveConnection = con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "Select * from Table1"

End With

With cmd

    .ActiveConnection = con
    .CommandType = adCmdText
    .CommandText = "delete from Table1"
    .Execute
    MsgBox "Database Clear!", vbInformation
End With

The codes are simple and easy to understand. The only complicated parts are the edit and delete functions because we need to select the “id” of its input in order to execute edit and delete function properly. By using “id” which is our table primary key, we can edit and delete a record from the database specifically. Which means, if you have ten inputs in your database with the same value, you can specifically edit and delete one record; whereas if you use “input” instead of “id”, all the records with the same value will be edited or deleted?

You can download the whole program here: How to create add, edit, delete, and clear records from Microsoft Access 2003 Database