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
Command.Connection.Open()
Command.ExecuteNonQuery()
Connection.Close()

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?

Thanks!
undy30Asked:
Who is Participating?
 
DhaestCommented:
You can check if the column exist on the startup of your program...

For example
<startup>
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()
da.fill(ds,"main")
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
   Command.Connection.Open()
   Command.ExecuteNonQuery()
    Connection.Close()
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
    Next
 
    Return bRet
End Function
0
 
ArkCommented:
       Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, tableName, columnName})
        ColumnExists = (schemaTable.Rows.Count() > 0)
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.