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.
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.
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.
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
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.
Public Class Form1
Dim DB_TableName As String = "TestDataTable"
Dim DB_TestDataTableRow As DataRow
Dim DB_DataSet As DataSet
Dim DB_DataTable As DataTable
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)
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.
DB_TestDataTableRow = DB_DataTable.NewRow()
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.
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) If i = -1 Then Dim column As DataColumn = DB_DataTable.Columns.Add(TestItemColumn, Type.GetType(SystemValueType))
Select Case SystemValueType
DB_TestDataTableRow.Item(TestItemColumn) = CInt(TestItemValue)
DB_TestDataTableRow.Item(TestItemColumn) = CDbl(TestItemValue)
DB_TestDataTableRow.Item(TestItemColumn) = CBool(TestItemValue)
Case Else If TestItemValue.Length() >= 255 Then
TestItemValue = TestItemValue.Substring(0, 254)
DB_TestDataTableRow.Item(TestItemColumn) = TestItemValue
Catch ex As Exception
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.
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
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 = ""
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
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
strSQL = "SELECT " & DB_TableName & ".* FROM [" & DB_TableName & "];"
cmd.CommandText = strSQL
da.SelectCommand = cmd
Dim TempDataSet As New DataSet
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
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
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 Dim TempCmdDataType As String = ""
Select Case DB_TestDataTableRow.Item(x).GetType().ToString
TempCmdDataType = "Integer"
TempCmdDataType = "Double"
TempCmdDataType = "Yes/No"
TempCmdDataType = "Text(255)"
TempCmdDataType = "Text(255)"
cmd.CommandText = "ALTER TABLE " & DB_TableName & " ADD " & TempColumnName & " " & TempCmdDataType
cmd.ExecuteNonQuery() End If
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
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
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.