ALTER database during first run only

I have a Windows application written in VB.NET (Visual Studio 2003). It's a simple inventory database program using a MS Access MDB file.

I do periodic updates to the program which just replaces the main .exe file (no dlls or anything, very basic). In my last update I decided to add a new field which needed a new column in the database. When users update the program it doesn't overwrite the database, for obvious reasons.

My problem is: How do I update the database for the new column?

I can probably figure out how to execute an ALTER statement to add the column but I want it done only if it needs to be. Either once during the update MSI is run or during the first time the new exe is run.

Example of how I connect to the DB:
Dim connString As String
Dim AppDir As String
AppDir = Application.StartupPath() & "\DBName.mdb"
Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppDir)
Command = New OleDbCommand("SELECT * FROM Main")
Command.Connection = Connection

Please provide code also. I'm a little rusty on my VB.NET.

Another problem I have is I don't have a seperate MSI for updating, I'm using the same install one. So when I attempt to install the update I get an error saying another version of the application is installed. And that I have to uninstall it first via the control panel. I do that and it works fine but it's annoying. Is there a tool or setting I can add to fix this?

Who is Participating?
You can check if the column exist on the startup of your program...

For example
Dim connString As String
Dim AppDir As String
AppDir = Application.StartupPath() & "\DBName.mdb"
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppDir
dim da as New OleDbDataAdapter("SELECT top 1 * FROM Main", connString)
dim ds as new dataset()
if columnExists(ds.tables("main"),"<columnname">) = true false
   Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppDir)
   Command = New OleDbCommand("ALTER Main ADD COLUMN...")
   Command.Connection = Connection
end if
Private Function ColumnExists(ByVal Table As DataTable, ByVal ColumnName As String) As Boolean
    Dim bRet As Boolean =  False
    Dim col As DataColumn
    For Each col In Table.Columns
        If col.ColumnName = ColumnName Then
            bRet = True
            Exit For
        End If
    Return bRet
End Function
       Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, tableName, columnName})
        ColumnExists = (schemaTable.Rows.Count() > 0)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.