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

Question

0.00/5 (No votes)

See more:

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.

What I have tried:

VB

[code] 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[/code]

Posted 10-May-20 17:59pm

kyrons

Updated 13-May-20 4:17am

Add a Solution

3 solutions

Solution 2

What I would do would be to breakup your routine into separate ALTER TABLE commands, and wrap the query executions in a Try...Catch block so that the code would continue on if one or more of the fields already exists.

Open conn
try 
   command = "ALTER Table Add Column NewColumn1 DataType"
   execute
catch
  // NewColumn1 already exists
try 
   command = "ALTER Table Add Column NewColumn2 DataType"
   execute
catch
  // NewColumn2 already exists

A variation of the above method would again be able to use a try-catch; but this time try to execute a select statement looking for that column by name, and in the catch block you would run the ALTER statement

Open conn
try 
   command = "SELECT NewColumn1 FROM Table"
   execute
catch
  command = "ALTER Table Add Column NewColumn1 DataType"
   execute
// repeat for other columns

Posted 10-May-20 19:30pm

MadMyche

Comments

Maciej Los 11-May-20 16:18pm

You gave me an idea. Please, see updated solution.

MadMyche 11-May-20 17:34pm

Looks good

Maciej Los 12-May-20 1:31am

;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-05-10T19:09:00

[EDIT]
MadMyche's solution bring me an idea...

I'm lazy to explain... Here is an implementation:

VB

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

Dim FieldsToAdd As List(Of String)  = New List(Of String) From {"name Text(250)", "salary number", "address Memo", "datarec DateTime", "updated YesNo"}
Dim dt As DataTable = New DataTable()
Dim existingCols As List(Of String) 

Try
		Dim sCommand As String = "SELECT * FROM Table1;"
        Using conn As New OleDbConnection(connString)
            conn.Open()
            Using cmd As New OleDbCommand(sCommand, conn)
                Using reader AS OleDbDataReader = cmd.ExecuteReader()
					dt.Load(reader)
				End Using
				existingCols = dt.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName).ToList()
			End Using
		    For Each f As String In FieldsToAdd
				Dim s As String = f.Split(" ")(0)
				If existingCols.Contains(s) Then
					Console.WriteLine(String.Format("Field '{0}' already exists!", s))
				Else
					sCommand = String.Format("AlTER TABLE Table1 ADD COLUMN {0};", f)	
		            Using cmd As New OleDbCommand(sCommand, conn)
						cmd.ExecuteNonQuery()
						Console.WriteLine(String.Format("column '{0}' has been added!", colsToAdd.Count))
					End Using
				End If
		    Next 
			conn.Close()
        End Using
Catch Ex As Exception
    Console.WriteLine(Ex.Message)
End Try

BTW: Please read this: List of reserved words in Access - Office | Microsoft Docs[^]

codejet · Accepted Answer · 2020-05-13T04:18:00

VB

'Procedure to execute sql to add columns 
Private Sub AddTblColumn (ColName As String, ColDataType As String, Conn As OleDbConnection )
 Dim SQLAdd As String = "ALTER TABLE Table1 ADD COLUMN " & ColName & " " & ColDataType
 Dim Cmd As OleDbCommand = New OleDbCommand (SQLAdd, Conn) 
Cmd.ExecuteNonQuery() 
End Sub  

Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data 
 Source=|DataDirectory|addcolumn.accdb"
Dim Conn As OleDbConnection = New OleDbConnection (ConnString) 
Conn.Open

' Get empty datatable to retrieve all columns 
Dim daEmpty As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM addcolumn WHERE 1= 0",ConnString)
Dim dtEmpty As DataTable = New DataTable () 
daEmpty.Fill(dtEmpty) 

' Get list of all columns that exist 
Dim LstCurrentCols As List(Of String) = New List (Of String) 
 For Each Col As DataColumn In dtEmpty.Columns
     Dim ColName As String = Col.ColumnName
     LstCurrentCols.Add(ColName) 
 Next

If LstColCurrent.Contains("Name") = False Then
AddTblColumn("name", "Text (250)", Conn) 
End If 

If LstColCurrent.Contains("salary") = False Then
    AddTblColumn("salary", "Number", Conn) 
End If 

If LstColCurrent.Contains("address") = False Then
    AddTblColumn("address", "memo", Conn) 
End If 

If LstColCurrent.Contains("daterec") = False Then
    AddTblColumn("daterec", "DateTime", Conn) 
End If

If LstColCurrent.Contains("updated") = False Then
    AddTblColumn("updated", "YesNo", Conn) 
End If

Hope that helps

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

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0