How to add missing columns and ignoring existing columns in ms access dbase with vb.net?

Question

The scenario is this. I have an existing ms access database. Inside the database has Table1. Assuming that I do not know what columns or existing columns are there already. My program using vb.net form, in just one click of a button I can add all columns like name, salary, address, daterec, and updated. No problem in there adding all those columns since they are not existing in Table1. My problem is in some databases Table1, some columns/fields are already exist like salary and address, I want to add columns name, daterec and updated but it gave me an error "Field 'name' already exists in Table 'Table1'. What should I do? I just want to add those columns who are not yet existing in Table1. I want to ignore those existing columns and proceed adding those missing columns. Any suggestions are very much appreciated.

Below is the code that can add columns and for modification. Thank you.

Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|addcolumn.accdb"


    Dim SqlString As String = "ALTER TABLE Table1 ADD COLUMN " + "name Text(250)," + "salary Number," + "address Memo," + "daterec DateTime," + "updated YesNo" 'Datatypes: Text is ShortText, Number is Number(Double), Memo is LongText, DateTime is Date or/and Time, YesNo is either checkbox/True or False/On or Off depends in saving format .
    Using conn As New OleDbConnection(ConnString)
        Using cmd As New OleDbCommand(SqlString, conn)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using

Answer 1

I wasn't going to provide an answer because I didn't think that the question showed enough effort but, as the accepted answer was not what I would consider advisable, I felt I needed to. This code does what I have advised here and for the same question elsewhere and should work as is, although I've only tested with different file, table and column names.

Dim tableName = "Table1"

Using connection As New OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\addcolumn.accdb")
    connection.Open()

    'Get a DataTable containing information about all columns in the specified table.
    Dim schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                                                     {Nothing, Nothing, tableName})

    'Create a list of all new column names and data types to add.
    Dim dataTypesByColumnName As New Dictionary(Of String, String) From {{"name", "Text(250)"},
                                                                         {"salary", "Number"},
                                                                         {"address", "Memo"},
                                                                         {"daterec", "DateTime"},
                                                                         {"updated", "YesNo"}}

    'Get a list of the column names already in the table.
    Dim existingColumnNames = schemaTable.AsEnumerable().
                                          Select(Function(row) row.Field(Of String)("COLUMN_NAME")).
                                          ToArray()

    'Create a list of column definition snippets to insert into SQL code, e.g. "columnName DataType".
    Dim columnSnippets = dataTypesByColumnName.Keys.Except(existingColumnNames).
                                                    Select(Function(key) String.Format("{0} {1}", key, dataTypesByColumnName(key))).
                                                    ToArray()

    If columnSnippets.Any() Then
        'Construct the full SQL statement.
        Dim sql = String.Format("ALTER TABLE {0} ADD COLUMN {1}", tableName, String.Join(", ", columnSnippets))

        Dim command As New OleDbCommand(sql, connection)

        'Execute the single SQL statement against the database.
        command.ExecuteNonQuery()
    End If
End Using

It gets a list of existing columns from the database, excludes those from the list to be added and then adds the rest with a single SQL statement.

Answer 2

The challenge here is that as you note? Some fields might exist, some might not. So, you have to test + add on a field by field basis

Something like this should work:

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click

    Dim strCon As String
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\test44.accdb"

    Using dbCon As New OleDbConnection(strCon)
        dbCon.Open()
        AddFieldExist("table1", "name", "Text(250)", dbCon)
        AddFieldExist("table1", "Salary", "number", dbCon)
        AddFieldExist("table1", "Address", "memo", dbCon)
        AddFieldExist("table1", "Address", "memo", dbCon)
        AddFieldExist("table1", "DateRec", "datetime", dbCon)
        AddFieldExist("table1", "Upated", "YesNo", dbCon)

    End Using

End Sub

Sub AddFieldExist(strTable As String,
                    strField As String,
                    strType As String,
                    dbCon As OleDb.OleDbConnection)

    ' return true/false if field exist.

    Dim tblDef As New DataTable
    Dim oReader As New OleDbDataAdapter("SELECT TOP 1 * FROM " & strTable, dbCon)
    oReader.Fill(tblDef)

    If tblDef.Columns.IndexOf(strField) = -1 Then
        ' add field
        Dim strSQL As String = "ALTER TABLE " & strTable & " ADD COLUMN " & strField & " " & strType
        Dim cmd As New OleDbCommand(strSQL, dbCon)
        cmd.ExecuteNonQuery()
    End If

End Sub

the code does assume that the table in question does have data. If it does not then you might have to introduce getSchema here, but for now, the above is quite much what I would suggest.