Click here to Skip to main content
15,064,072 members
Articles / Database Development
Posted 21 Apr 2013


18 bookmarked

Dynamically add access database columns at runtime using

Rate me:
Please Sign up or sign in to vote.
5.00/5 (13 votes)
21 Apr 2013CPOL5 min read
Dynamically add access database columns at runtime using

Image 1


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.


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 data base 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.

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)

Now there are 3 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")
        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 “<span style="font-family: 'Courier New'; color: green;">Microsoft.Jet.OLEDB.4.0</span>“ 

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.

                       conn.ConnectionString = DB_Provider & DB_Source
               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
               End If

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 data base 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 querry
                End If

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.

        Catch ex As Exception
               '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.


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 more efficient way to accomplish it.  


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rob Culhane
United States United States
No Biography provided

Comments and Discussions

QuestionNeed help with my project Pin
Peter_jnr27-Nov-13 21:27
professionalPeter_jnr27-Nov-13 21:27 
GeneralMy vote of 5 Pin
MohamedKamalPharm12-Jun-13 23:36
MemberMohamedKamalPharm12-Jun-13 23:36 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA10-May-13 18:23
professionalȘtefan-Mihai MOGA10-May-13 18:23 
This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information. Keep it up once again.
GeneralRe: My vote of 5 Pin
Rob Culhane16-May-13 5:28
MemberRob Culhane16-May-13 5:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.