ALTER database during first run only

Posted on 2007-07-28
Last Modified: 2012-05-05
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?

Question by:undy30
    LVL 53

    Accepted Solution

    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
    LVL 27

    Expert Comment

           Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, tableName, columnName})
            ColumnExists = (schemaTable.Rows.Count() > 0)

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now