Database Application Using SQL Statements in VB 6 Full Add, View, Update and Delete Rows

In this tutorial I will explain how to manipulate data using the SQL statement in Visual Basic. The purpose of this tutorial is that the reader can understand how to use the SQL Statement to manipulate data in Visual Basic.


Step 1

Create a Microsoft Access 2010 database file, save it in c: \ BLC\data.accdb
Create a table with name distributor with the following structure:
---
id_distributor Text (3)
distributor Text (50)
discount (Number)
---

Step 2
Open Visual Basic, create a project Standard.EXE with name pos.vbp

Step 3

In a new Form add this controls
- 3 Text Boxes, create a Text Box array, fill the index in each Index Text Box Propety
- 3 Labels with Caption: ID Distributor, Distributor, Discount
- 3 Buttons with Caption: Save, Delete, Close

Explanation
ID Distributor is used to refer a distributor code (as a key field)
Distributor and Discount used to show distributor name and discount value refer from the Distributor code

Save Button is used to Save (create a new record into database) and Update (replace the record exist in the database). I didn't used Update Button, because it was ineffective.
Delete Button is used to erase record exist in database.
Close Button is used to closing form.
 
Step 4


Based on connection script in the Modul we have made, add this code. Enter code editor write this code
---
Private Sub Text_Validate(Index As Integer, Cancel As Boolean)
    Select Case Index
        Case 1
            If Trim(Text(1).Text) = "" Or Trim(Text(1).Text) = " " Then
                Text(2).SetFocus   '----> if empty string then do nothing
            Else
                Call opendb
                xSQL = " select top 1 id_distributor, distributor, " & vbCrLf & _
                       " discount " & vbCrLf & _
                       " from distributor where " & vbCrLf & _
                       " id_distributor = '" & RTrim(Text(1).Text) & "' " & vbCrLf & _
                       " order by id_distributor asc "
                rsXData.Open xSQL, oConn
                If rsXData.EOF = False Then  '-----> if the data are exist, show it
                    Text(1).Text = rsXData!id_distributor
                    Text(2).Text = rsXData!distributor
                    Text(3).Text = rsXData!discount
                Else
                    Text(2).SetFocus
'                    MsgBox ("The record was not found,..") '----> if the data nothing, show message
                End If
            End If
        Case 2
            Text(3).SetFocus
        Case 3
            Cmd(1).SetFocus
    End Select
End Sub
----


This script is used to give procedure to all Text Boxes Validate event. This event consist of lost focus, Tab key, click on other control but except Enter/Return key. This script below used to handle that.


---
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
       SendKeys "{TAB}"
    End If
End Sub
---


Step 5


This script below is used to give procedure to all Command Buttons Validate events.
---
Private Sub Cmd_Click(Index As Integer)
    Select Case Index
        Case 1
            Call save
        Case 2
            Call delete
        Case 3
            Unload Me
    End Select
End Sub
---