Create a VB6 script to manipulate records in Ms Access 2010 database

Based on previous tutorial , here script to give procedure input, update, and erase records in Ms Access 2010.

Save Procedure
---
Private Sub save()
    If Trim(Text(1).Text) = "" Or Trim(Text(1).Text) = " " Then
        MsgBox ("ID Distributor is empty, ID Distributor must not empty")
        Cancel = True          'if empty, then cancel or do nothing
        Text(1).SetFocus
    Else
        Call opendb
        Call delete              'this is used to erase the data, and then replace with the new one (update function)
        xSQL = "insert into distributor (id_distributor,distributor,"
        xSQL = xSQL & "discount)"
        xSQL = xSQL & " values "
        xSQL = xSQL & "('" & Text(1).Text & "',"
        xSQL = xSQL & "'" & Text(2).Text & "',"
        xSQL = xSQL & "'" & Text(3).Text & "')"
        oConn.Execute xSQL
        If Err = 1 Then
            MsgBox ("Failed,..!!!")   'if error, then show message
        Else
            Call clear                        'if success all text box clean up, and show message
            MsgBox ("Successfull,..!!!")
            Text(1).SetFocus
        End If
    End If
End Sub
---

Delete Procedure
---
Private Sub delete()
    If Trim(Text(1).Text) = "" Or Trim(Text(1).Text) = " " Then
        MsgBox ("ID Distributor is empty, ID Distributor must not empty")
        Cancel = True
        Text(1).SetFocus
    Else
        Call opendb
        xSQL = "delete from distributor "
        xSQL = xSQL & " where "
        xSQL = xSQL & " id_distributor = '" & RTrim(Text(1).Text) & "' "
        oConn.Execute xSQL
        If Err = 1 Then
            MsgBox ("Failed,..!!!")
        Else
        Call clear
            MsgBox ("Successfull,..!!!")
            Text(1).SetFocus
        End If
    End If
End Sub
---

Read More

Create connection test tool using VB 6.0

Now, we will create a tool to test a connection to the database. Here the steps,..

Step 1

Create New Project, and create new form like picture below,..

Step 2
Don't forget add Reference by click on Project Menu-->Reference

 

Step 3

Add this script in the Command Button control...

Read More

Create a database connection script in a VB 6.0 Modul

Now, we will create a database connection script in a VB 6.0 Modul

Step 1
Click Menu Project--->Add Modul

Step 2
Add this scrip to the Module
|-----
Option Explicit
Public oConn As ADODB.Connection
Public rsXData As ADODB.Recordset
Public xSQL As String

Public Sub opendb()
  Set oConn = New ADODB.Connection
  Set rsXData = New ADODB.Recordset
  oConn.CursorLocation = adUseClient
  oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       "Data Source=" & App.Path & "\data.accdb;Jet OLEDB:Database Password=0rch1d;"
End Sub|-----



How  to create a Visual Basic 6.0 Modul? Here it is,...
 
Here references that you could to learn.

Read More

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
---

Read More

Making a connection to SQL Server 2000-2008 VB6

Basically any commands for manipulating databases perform well, add or update definitely needs an operation connection, and therefore do not need to create a script that repeatedly the connection needs to be made in connection script in the module file. And if necessary just call use the command "Call  <procedurename>"
Step 1
Open a new project, select the Enterprise.
Click the Project menu, click Add Module, click OK
Enter the following code in the module, then Save, ..
---
Option Explicit
Public conn As ADODB.Connection
Public recset As ADODB.Recordset

Public Sub opendb()
  Set oConn = New ADODB.Connection
  Set recset = New ADODB.Recordset
  oConn.CursorLocation = adUseClient
  oConn.Open "Provider=SQLOLEDB.1;Password=b217an;User ID=sa;Initial Catalog=Xserver;Data     Source=local"
End Sub
---
Step 2
They will just call the procedure in the form with the Call command, if the sample is placed at the time the form was called / load:
Private Sub Form1_Load()
 Call opendb
 Set recset = New ADODB.recordset

 recset.Open ("select * from data_pegawai"), oConn
 DataGrid1.Data Source = recset

 '----in order to save memory resources and then close again  recset.Close
 Set recset = Nothing
 oConn.Close
End Sub

 

 

Read More