VB.NET Class Library: DBIO Access

    Dim dba As New C_DBIO_Access

    Dim dba As New C_DBIO_Access("C:\Database\Demo.mdb")

    Dim dba As New C_DBIO_Access("C:\Database\Demo.mdb", _
                                 "password")

    Dim dba As New C_DBIO_Access("C:\Database\Demo.mdb")
    Dim dtb As New DataTable
    With dba
        '** Generate a SELECT * statement 
        '** for the database table
        .DatabaseTableName(True) = "Customers"
        '** Extract rows from the database table
        If .PopulateDataTable() Then
            '** Delete the customer from the database
            '** WHERE the CustID is 123
            dtb = .FilledDataTable
            If Not .DeleteRow(dtb, "CustID = '123'") Then
                '** Code to execute when delete fails
            End If
        Else
            '** Code to execute when table 
            '** population fails
        End If
    End With

The optional String Array contains the names of datatable columns that are not to be included in the INSERT statement. The names are case sensitive. The names in the array may be in any order.

The optional Integer Array contains the indexes (zero based) of datatable columns that are not to be included in the INSERT statement. The indexes in the array may be in any order.

One or both of the arrays may be passed. The string array is checked first. If a column name is not in the string array, the integer array is checked.

    '** Instantiate a datatable for the database table
    Dim dba As New C_DBIO_Access("C:\Database\Demo.mdb")
    Dim dtb As New DataTable
    With dba
       '** Limit the number of datatable rows returned
       .SQLTop = 1
       '** Generate a SELECT * statement 
       '** for the database table
       .DatabaseTableName(True) = "Customers"
       '** Extract a row from the database table
        If .PopulateDataTable Then
            dtb = .FilledDataTable
        Else
            '** Code executed when population fails
        End If
    End With
    '** Add and populate a new row in the datatable
    Dim dr As DataRow = dtb.NewRow
    dr("CustID") = "456"
    dr("CustName") = "Acme Products"
    dt.Rows.Add(dr)
    dr = Nothing
    '** Build an array of table columns not being 
    '** populated in the new row
    Dim skip(1) As String
    skip(0) = "CustAddress"
    skip(1) = "CustPhone"
    '** Insert the new row into the database without 
    '** an address or phone number
    With dba
        If Not .InsertRow(dtb, skip) Then
            '** Code to execute when insert fails
        End If
    End With

Populates the FilledDataTable property using the SQLString property (either set manually or automatically generated). If any part of the DataTable fill process fails, the FilledDataTable property is not changed. True is returned when the DataTable fill is successful; False if the process fails.

The optional Boolean parameter for DisplayError controls whether an error message is displayed when the fill process fails. The default is True. When an error message is displayed it includes the exception Message property along with the complete SQL SELECT statement. When the DisplayError parameter is False, the exception Message property is exposed via the ErrorMessage property.

The name of the filled DataTable is set to the DatabaseTableName property if that property has been set. Otherwise, the name of the Access table, from which the data was extracted, is used after being parsed from the SQLString property text.

The optional String parameter is the WHERE expression that identifies one or more Access table rows to be updated. Not supplying a WHERE expression causes all rows in the table to be updated.

The optional String Array contains the names of datatable columns that are not to be included in the UPDATE statement. The names are case sensitive. The names in the array may be in any order.

The optional Integer Array contains the indexes (zero based) of datatable columns that are not to be included in the UPDATE statement. The indexes in the array may be in any order.

Updates the database and returns True if the update is successful. False is returned should the update fail. In the latter case, an error message is displayed that includes the exception Message property along with the complete SQL UPDATE statement.

    '** Instantiate a datatable for the database table
    Dim dba As New C_DBIO_Access("C:\Database\Demo.mdb")
    Dim dtb As New DataTable
    With dba
        '** Retrieve customer ID 456
        .SQLWhere = "CustID = '456'"
        '** Generate a SELECT * statement 
        '** for the database table
        .DatabaseTableName(True) = "Customers"
        '** Extract row from the database table
        If .PopulateDataTable Then
            dtb = .FilledDataTable
        Else
            '** Code executed when population fails
        End If
    End With
    '** Update the row in the datatable
    With dtb.Rows(0)
        .Item("CustAddress") = "18 Hill Drive, Anywhere, USA"
        .Item("CustPhone") = "111-222-5555"
    End With
    '** Build an array of table columns not being 
    '** updated
    Dim skip(1) As String
    skip(0) = "CustID"
    skip(1) = "CustName"
    '** Update the row in the database
    If Not dba.UpdateRow(dtb, _
                         0, _
                         "CustID = '456', _
                         skip) Then
        '** Code to execute when update fails
    End If

    '** Instantiate a datatable for the database table
    Dim dba As New C_DBIO_Access("C:\Database\Demo.mdb")
    Dim dtb As New DataTable
    With dba
        '** Generate a SELECT * statement 
        '** for the database table
        .DatabaseTableName(True) = "Customers"
        '** Extract rows from the database table
        If .PopulateDataTable Then
            dtb = .FilledDataTable
        Else
            '** Code executed when population fails
        End If
    End With
    '** Add, update and/or delete one or more rows 
    '** in the datatable
    With dtb.Rows(IndexOfRowBeingChanged)
        .Item("CustAddress") = "18 Hill Drive, Anywhere, USA"
        .Item("CustPhone") = "111-222-5555"
    End With
    '** Update the added, changed and deleted row(s) 
    '** in the database (It is critical that the SQLString
    '** property used above to generate the datatable 
    '** is not changed between populating the datatable 
    '** and invoking the UseCommandBuilder method)
    With dba
        .FilledDataTable = dtb
        If .UseCommandBuilder < 0 Then
            '** Code to execute when update fails
        End If
    End With

    '** Sort the .FilledDataTable on the CustID column
    dba.SQLOrderBy = "CustID"

    '** Populate the .FilledDataTable with only the
    '** CustID and CustName columns
    dba.SQLSelectList = "CustID, CustName"

    '** Set the entire SQL statement to be used
    '** (The SQL statement can be as complex as
    '** needed)
    dba.SQLString = "SELECT CustID, CustName FROM Customers"
    '** Set the table being queried letting the
    '** parameter of the DatabaseTableName 
    '** property default to False so that the 
    '** passed SQL statement is used instead of
    '** a statement being automatically generated
    dba.DatabaseTableName() = "Customers"

    '** Populate the .FilledDataTable with only the
    '** customer having a CustID = 123
    dba.SQLWhere = "CustID = '123'"