How to add column and ignoring existing column in ms access dbase?

[ronelpisan]

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

[jmcilhinney]

As I just posted on SO, you can call GetSchema or GetOleDbSchemaTable on your connection to get schema information, including about columns. I would probably start with a list of all columns and the SQL snippets for them and then remove the ones you find already present, e.g.

Dim dataTypesByColumnName As New Dictionary(Of String, String) From {{"name", "Text(250)"},
                                                                     ...,
                                                                     {"updated", "YesNo"}}
Dim existingColumnNames As New List(Of String) 'Populate from database
Dim columnSnippets = dataTypesByColumnName.Keys.Except(existingColumnNames).
                                                Select(Function(key) $"{key} {dataTypesByColumnName(key)}").
                                                ToArray()
 
If columnSnippets.Any() Then
    Dim sql = "ALTER TABLE Table1 ADD COLUMN " & String.Join(", ", columnSnippets)
 
    'Use sql here.
End If

[jmcilhinney]

I just realised that you're using VS 2013. I would recommend upgrading to VS 2019 but if you're going to stick with 2013 then, while I think everything else should be fine, you won't be able to use string interpolation. That means falling back to using String.Format:

Dim dataTypesByColumnName As New Dictionary(Of String, String) From {{"name", "Text(250)"},
                                                                     ...,
                                                                     {"updated", "YesNo"}}
Dim existingColumnNames As New List(Of String) 'Populate from database
Dim columnSnippets = dataTypesByColumnName.Keys.Except(existingColumnNames).
                                                Select(Function(key) String.Format("{0} {1}", key, dataTypesByColumnName(key))).
                                                ToArray()
 
If columnSnippets.Any() Then
    Dim sql = "ALTER TABLE Table1 ADD COLUMN " & String.Join(", ", columnSnippets)
 
    'Use sql here.
End If

[ronelpisan]

I just realised that you're using VS 2013. I would recommend upgrading to VS 2019 but if you're going to stick with 2013 then, while I think everything else should be fine, you won't be able to use string interpolation. That means falling back to using String.Format:

Dim dataTypesByColumnName As New Dictionary(Of String, String) From {{"name", "Text(250)"},
                                                                     ...,
                                                                     {"updated", "YesNo"}}
Dim existingColumnNames As New List(Of String) 'Populate from database
Dim columnSnippets = dataTypesByColumnName.Keys.Except(existingColumnNames).
                                                Select(Function(key) String.Format("{0} {1}", key, dataTypesByColumnName(key))).
                                                ToArray()
 
If columnSnippets.Any() Then
    Dim sql = "ALTER TABLE Table1 ADD COLUMN " & String.Join(", ", columnSnippets)
 
    'Use sql here.
End If

Thanks for your help my friend but it did not work. Same error prompted. Field 'name' already exists in table 'Table1'. Any modification please.

[ronelpisan]

Thanks for your help my friend but it did not work. Same error prompted. Field 'name' already exists in table 'Table1'. Any modification please.

Thanks buddy. I just put "On error Resume Next" and replacing column "name" with "fullname". Finally it works.

[jmcilhinney]

Thanks for your help my friend but it did not work. Same error prompted. Field 'name' already exists in table 'Table1'. Any modification please.

If you got that error message then you didn't do what I told you to do. As you didn't bother to show us what you did, there's no way for us to tell how you butchered it. If you had done what I said then it would have worked.

[jmcilhinney]

Thanks buddy. I just put "On error Resume Next" and replacing column "name" with "fullname". Finally it works.

As I posted at SO, that is no solution at all. If you ever use On Error Resume Next in VB.NET then your code is garbage. The only reason you might think you need it is because you've written bad code and you're just hiding the problem, not solving it.

[ChrisE]

I would fill a Listbox with Columns names from the Table first, but that's just me

[jmcilhinney]

If you got that error message then you didn't do what I told you to do. As you didn't bother to show us what you did, there's no way for us to tell how you butchered it. If you had done what I said then it would have worked.

I'd wager that what you actually did was ignore this:

Dim existingColumnNames As New List(Of String) 'Populate from database

Did you bother to populate that list from the database after a call to GetSchema or GetOleDbSchemaTable? Probably not. FYI, I just ran this code on a table that did not contain either column and it worked:

Dim filePath = "data file path here"
Dim tableName = "table name here"
 
Using connection As New OleDbConnection($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};")
    connection.Open()
 
    Dim schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                                                     {Nothing, Nothing, tableName})
 
    Dim dataTypesByColumnName As New Dictionary(Of String, String) From {{"Column1", "Text(250)"},
                                                                         {"Column2", "Text(250)"}}
    Dim existingColumnNames = schemaTable.AsEnumerable().
                                          Select(Function(row) row.Field(Of String)("COLUMN_NAME")).
                                          ToArray()
    Dim columnSnippets = dataTypesByColumnName.Keys.Except(existingColumnNames).
        Select(Function(key) $"{key} {dataTypesByColumnName(key)}").
        ToArray()
 
    If columnSnippets.Any() Then
        Dim sql = $"ALTER TABLE {tableName} ADD COLUMN {String.Join(", ", columnSnippets)}"
        Dim command As New OleDbCommand(sql, connection)
 
        command.ExecuteNonQuery()
    End If
End Using

After running that code, the specified table in the specified file contained columns named Column1 and Column2. I then made this adjustment:

Dim filePath = "D:\johnm\Documents\JobSheet.mdb"
Dim tableName = "JobInfo"

Using connection As New OleDbConnection($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};")
    connection.Open()

    Dim schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                                                     {Nothing, Nothing, tableName})

    Dim dataTypesByColumnName As New Dictionary(Of String, String) From {{"Column1", "Text(250)"},
                                                                         {"Column2", "Text(250)"},
                                                                         {"Column3", "Text(250)"},
                                                                         {"Column4", "Text(250)"}}
    Dim existingColumnNames = schemaTable.AsEnumerable().
                                          Select(Function(row) row.Field(Of String)("COLUMN_NAME")).
                                          ToArray()
    Dim columnSnippets = dataTypesByColumnName.Keys.Except(existingColumnNames).
        Select(Function(key) $"{key} {dataTypesByColumnName(key)}").
        ToArray()

    If columnSnippets.Any() Then
        Dim sql = $"ALTER TABLE {tableName} ADD COLUMN {String.Join(", ", columnSnippets)}"
        Dim command As New OleDbCommand(sql, connection)

        command.ExecuteNonQuery()
    End If
End Using

and ran the code again and, lo and behold, the code executed without issue and the table then contained columns named Column3 and Column4. It's almost like it works when you do as instructed.

[jmcilhinney]

I would fill a Listbox with Columns names from the Table first, but that's just me

There's no need. If you want to add a manual step in the middle or you just want to see the columns then that's fine but there's no need to see the list if you just want to add any columns from a list that don't already exist.

[ronelpisan]

As I posted at SO, that is no solution at all. If you ever use On Error Resume Next in VB.NET then your code is garbage. The only reason you might think you need it is because you've written bad code and you're just hiding the problem, not solving it.

Sorry for what I have done but this is the easiest thing to do. Do you have any idea or suggestion to modify the code and not using "On Error Resume Next"? Thanks.

[jmcilhinney]

Sorry for what I have done but this is the easiest thing to do. Do you have any idea or suggestion to modify the code and not using "On Error Resume Next"? Thanks.

On Error Resume Next is easy because it says ignore anything that goes wrong and carry on regardless. How can you know whether it's OK to carry on if you have no idea if or what went wrong?

I have posted a working solution further up the thread, although it may not have been there when you started typing your post. I'll repost the code I provided at SO as it is more specific to your case and includes comments:

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

[ChrisE]

There's no need. If you want to add a manual step in the middle or you just want to see the columns then that's fine but there's no need to see the list if you just want to add any columns from a list that don't already exist.

that's true but....
sometimes I have to alter a Column name so my memory isn't that good (age is getting to me)

but for solution Post#12

[ronelpisan]

On Error Resume Next is easy because it says ignore anything that goes wrong and carry on regardless. How can you know whether it's OK to carry on if you have no idea if or what went wrong?

I have posted a working solution further up the thread, although it may not have been there when you started typing your post. I'll repost the code I provided at SO as it is more specific to your case and includes comments:

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

Thanks my friend jmcilhinney. It works without even using "On Error Resume Next". You're truly genius man. Thank you so much.

[jmcilhinney]

You're truly genius man.

Far from it. I just know the basics really well. I wouldn't expect a beginner to write code like that in one go, as I did, because they're not familiar with all the concepts and specific details. What I do expect is for a beginner to break it down, tackle it piece by piece and, with the help of nudges in the right direction from time to time, be able to build up code like this, even if it takes a bit longer. For instance, I mentioned GetSchema and GetOleDbSchemaTable at the outset so you should have been reading about those, testing them to see what they produce and then be able to get the list of existing column names from that output. That's one stage done and you can then decide how to use that list of names. Etc. In my experience, the #1 issue that beginners have is not breaking a larger problem down into multiple smaller ones. Just like every journey is made up of many small steps, every problem is made up of several smaller, simpler ones. Most people are capable of solving any of those simpler ones, perhaps with a bit of help like being told the correct method to look at using. If you never even know that you're trying to solve those simple problems though, you never solve them and, thus, you don't solve the bigger problem. Personally, I think of each problem as a bunch of boxes representing the parts and each of them may contain smaller boxes. I make each box in isolation and then I just have to make pipes to connect them to each other in the correct arrangement.