VB6 Database Programming

Visual Basic 6 supports multiple data access technologies. ADO (ActiveX Data Objects) is the recommended approach for new development.

Adding the ADO Reference

1. Go to Project → References.
2. Check Microsoft ActiveX Data Objects 2.x Library (use 2.8 if available).
3. Click OK.

Connecting and Querying

Private Sub LoadEmployees()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String

    strConn = "Provider=SQLOLEDB;" & _
        "Data Source=SQLSERVER01;" & _
        "Initial Catalog=FlamenetDB;" & _
        "Integrated Security=SSPI;"

    conn.Open strConn

    rs.Open "SELECT EmployeeID, FirstName, LastName, Email " & _
        "FROM Employees WHERE IsActive = 1 ORDER BY LastName", _
        conn, adOpenStatic, adLockReadOnly

    ' Populate a ListView
    lvEmployees.ListItems.Clear
    Do While Not rs.EOF
        Dim li As ListItem
        Set li = lvEmployees.ListItems.Add(, , rs("EmployeeID"))
        li.SubItems(1) = rs("FirstName") & " " & rs("LastName")
        li.SubItems(2) = rs("Email") & ""
        rs.MoveNext
    Loop

    rs.Close
    conn.Close
End Sub

Parameterized Commands

Private Function GetEmployeeByID(ByVal lngID As Long) As ADODB.Recordset
    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = g_conn  ' Module-level connection
    cmd.CommandText = "SELECT * FROM Employees WHERE EmployeeID = ?"
    cmd.CommandType = adCmdText
    cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, , lngID)
    Set GetEmployeeByID = cmd.Execute()
End Function

Data Binding

VB6 supports data binding with the ADO Data Control (ADODC). Set the ConnectionString and RecordSource properties at design time, then bind text boxes and grids to it using the DataSource and DataField properties. However, manual ADO code (as shown above) provides more control and better error handling.

Error Handling

Private Sub SaveData()
    On Error GoTo ErrorHandler

    conn.BeginTrans
    ' ... execute INSERT/UPDATE statements ...
    conn.CommitTrans
    MsgBox "Data saved.", vbInformation
    Exit Sub

ErrorHandler:
    conn.RollbackTrans
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub