Dynamically Add Access Database Columns at Runtime using VB.NET

• Download source code - 72.6 KB

Introduction

This project will demonstrate how to build and update an Access database dynamically using VB.NET. Once the structure shown here is in place, adding new database columns and data is easily accomplished with a single line of code. The example was built and tested with VB.NET 2008 and Access 2010.

Background

As it stood, my company had specific needs to test our products and save data from a set of test software that has been in use for about 3 years now. Originally, a small set of test parameters were saved in .csv format. This worked for a small dataset as long as all the units being tested saved the same amount and types of data. As time went on, the need to save more and different kinds of data for various tests and be able to report useful information from that data was becoming increasingly difficult to maintain using .csv file format. An Access database seemed the obvious solution but exactly how to implement this in VB.NET the way we desired was somewhat of a mystery.

As each product tested might have only part of the full data set, being able to add new columns through code was the most convenient approach. As new tests were needed, the database could be automatically updated. Setting up the database fields manually for each type of product test seemed a bit cumbersome to maintain.

Using the Code

First, a few notes about setting up this project that can easily trip you up if you’re not aware of them. You will most likely get errors depending on your

• Set your VB.NET project settings->Compile->Advanced Compile Options…Target CPU… to “x86”
• You will need Microsoft “Access Engine 2012” installed on your development system and also any system to intend to deploy your program to.
  http://www.microsoft.com/en-us/download/details.aspx?id=13255

OleDB is used for the data connection to our database. The TestDataTableRow DataRow is used to hold test data throughout the code. A DataTable and DataSet is needed to complete the structure used to match our Access database later on.

Imports System.Data.OleDb 
Public Class Form1
        Dim DB_TableName As String = "TestDataTable"
        Dim DB_TestDataTableRow As DataRow
        Dim DB_DataSet As DataSet   
    Dim DB_DataTable As DataTable   

Now to create the data structure and link them together…

Private Sub Form1_Load(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles MyBase.Load
        DB_DataSet = New DataSet()
        DB_DataTable = New DataTable(DB_TableName)
        DB_DataSet.Tables.Add(DB_DataTable)

Now there are three sub routines that do all the work here. First, the simplest at the start of each test DB_InitNewTestItem() is called that initializes a new DataRow and clears previous data if any.

Sub DB_InitNewTestItem()
        DB_TestDataTableRow = DB_DataTable.NewRow()
End Sub

DB_LogTestValue does the work of building the DataRow created previously to match the structure of the format the eventual Access database. The TestItemColumn name is created if it did not previously exist and the TestItemValue assigned. Some cheating is done here as VB.NET no longer accepts “var” type arguments. All values are passed as strings and later converted based on the SystemValueType. More types are available in Access if you need them just add another Case statement. Be aware of the types Access will accept.

Also note the string length needs to be defined when we write the database. Errors will result during the Update command if a longer string is passed larger than the defined maximum. Here, the default value is set to “255” that is the maximum “Text” length value we can assign.

Sub DB_LogTestValue(ByVal TestItemColumn As String, ByVal TestItemValue As String, _
Optional ByVal SystemValueType As String = "System.String")
 
Try
        Dim i As Integer = 1
        i = DB_DataSet.Tables(DB_TableName).Columns.IndexOf_
                             (TestItemColumn) 'test if colum exists
        If i = -1 Then 'Column is missing so add it
                Dim column As DataColumn = DB_DataTable.Columns.Add_
                              (TestItemColumn, Type.GetType(SystemValueType))
        End If
 
        Select Case SystemValueType 
               Case "System.Int32"
                       DB_TestDataTableRow.Item(TestItemColumn) = CInt(TestItemValue)
               Case "System.Double"
                       DB_TestDataTableRow.Item(TestItemColumn) = CDbl(TestItemValue)
               Case "System.Boolean"
                       DB_TestDataTableRow.Item(TestItemColumn) = CBool(TestItemValue)
               Case Else '"System.String"
                       If TestItemValue.Length() >= 255 Then
                               TestItemValue = TestItemValue.Substring(0, 254)
                       End If
                       DB_TestDataTableRow.Item(TestItemColumn) = TestItemValue
        End Select
Catch ex As Exception
        'error handler
End Try
End Sub

Once DB_LogTestValue() has filled the DataRow with useful data, we’ll want to save it using DB_RecordModuleTestToFile(). This is shown here broken down into sections for explanation. There are most certainly better examples out there for connecting and writing to a database in general so only the critical details needed to match and transfer our new DataRow to the database will be explained here.

First, the provider is defined as Microsoft.ACE.OLEDB.12.0. Without the “Access Engine 2012” installed, this would give the error “provider is not registered on the local machine”. The Data Source is, of course, the path to your access database in newer Access format “accdb. For the older “.mdb” format, then use “Microsoft.Jet.OLEDB.4.0“.

Sub DB_RecordModuleTestToFile()
 
        Dim DB_Provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
        Dim DB_Source As String = "Data Source = " & "C:\DummyDB\DummyDB.accdb"
 
        Dim strSQL As String
        Dim conn As New OleDbConnection Dim cmd As New OleDbCommand
        Dim cmd As New OleDbCommand
        Dim da As New OleDbDataAdapter
        Dim x, i As Integer
        Dim TempColumnName As String = ""
…

Add our filled DataRow to the DataTable and establish the connection to the database. Enclosing the database connection code in a Try/Catch block is highly recommended.

…
        Try
               DB_DataTable.Rows.Add(DB_TestDataTableRow)   
                       conn.ConnectionString = DB_Provider & DB_Source
               conn.Open()
               cmd.Connection = conn
               cmd.CommandType = CommandType.Text
…

Now we need to build a SQL SELECT command string to pull data from the database into a temporary DataTable. The intention of this code example is to write the Access database and not necessarily read it back but the structure of our local DataSet and that of the target DataBase must match or the database Update function will fail to write properly.

…
        strSQL = "SELECT " & DB_TableName & ".* FROM [" & DB_TableName & "];"       
        cmd.CommandText = strSQL
        da.SelectCommand = cmd
        Dim TempDataSet As New DataSet
        da.Fill(TempDataSet, DB_TableName)
…

Now search through the TempDataSet and add any columns to our local DB_DataSet DataSet that do not already exist. If you are always reading and writing the same number of columns to the database, then these steps are not necessary.

…
        For x = 0 To TempDataSet.Tables(DB_TableName).Columns.Count - 1      
               TempColumnName = TempDataSet.Tables_
               (DB_TableName).Columns(x).ColumnName 'pull column names 
                                                    'from the test DataTable
               i = DB_DataSet.Tables(DB_TableName).Columns.IndexOf_
               (TempColumnName) 'returns -1 if column does not exist
               If i = -1 Then 'This Column is missing so add it
                       DB_DataSet.Tables(DB_TableName).Columns.Add(TempColumnName)
               End If
        Next
…

The OleDbCommandBuilder will take care of creating the INSERT, DELETE and UPDATE SQL commands for us behind the scenes.

…
        Dim dataCommandBuilder As New OleDb.OleDbCommandBuilder(da)
        da.InsertCommand = dataCommandBuilder.GetInsertCommand
        da.DeleteCommand = dataCommandBuilder.GetDeleteCommand
        da.UpdateCommand = dataCommandBuilder.GetUpdateCommand
…

For each new Data column, an SQL “ALTER TABLE“ command is needed in order for us to write our new column to the database. Here the datatype must be of an SQL compatible type with Access. A for loop is used to search through the database columns read back and build the “ALTER TABLE“ command string. A cmd.ExecuteNonQuery() is used to send the command to the database adding the column.

…
        For x = 0 To DB_DataSet.Tables(DB_TableName).Columns.Count() - 1
               TempColumnName = DB_DataSet.Tables(DB_TableName).Columns(x).ColumnName
               i = TempDataSet.Tables(DB_TableName).Columns.IndexOf(TempColumnName)
               If i = -1 Then 'This Column is missing so add it
               Dim TempCmdDataType As String = ""
               Select Case DB_TestDataTableRow.Item(x).GetType().ToString
                       Case "System.Int32"
                               TempCmdDataType = "Integer"
                       Case "System.Double"
                               TempCmdDataType = "Double"
                       Case "System.Boolean"
                               TempCmdDataType = "Yes/No"
                       Case "System.String"
                               TempCmdDataType = "Text(255)"
                       Case Else
                               TempCmdDataType = "Text(255)"
               End Select
               cmd.CommandText = "ALTER TABLE " & DB_TableName & " _
               ADD " & TempColumnName & " " & TempCmdDataType
               cmd.ExecuteNonQuery()   'executes the SQL code in cmd without query
                End If
        Next
…

With the Access database updated with the new column information, it should now accept the data from our local DataSet and the connection can be closed. This “Update” step is where most errors will occur if any of the previous steps failed to properly synchronize the local DataSet with the Access database.

…
                da.Update(DB_DataSet.Tables(DB_TableName))
                conn.Close()
        Catch ex As Exception
               conn.Close()
               'error handler
        End Try
End Sub

Points of Interest

This code is being shared here on CodeProject for others who might be struggling to do something similar. I hesitate to call this a tutorial as that infers I have some expertise on the subject of Databases outside of this experience. After weeks of searching for examples of what I was trying to accomplish here, a workable program was written that achieved these goals. However, do not take this as the best or only way to accomplish this as I may be breaking some database rules I am unaware of. I hope some of you with more experience in databases and SQL will comment and I welcome your suggestions in how to improve this.

History

This example was built and tested only with VB.NET 2008 and Access Engine 2010. Other combinations may work but use at your own risk. I will try to update the example if comments are posted with a more efficient way to accomplish it.