locked
How to Alter Table Column Name in Microsoft Access table using VB.NET RRS feed

  • Question

  • I get a Syntax Error with the following code

    "ALTER TABLE receipts RENAME COLUMN payees TO payee"

    Any ideas of what I am doing wrong

    reagards
    Colin Reid
    Friday, October 23, 2009 2:00 PM

Answers

  • Hi Colin,

    you'll have to add a reference to the library in question. For instance, in order to use ADOX, right-click References, click Add Reference , go to the COM tab and locate the entry that starts with Microsoft ADO Ext and click OK. With the reference in place, you'll be able to use the ADOX-namespace.

    Cheers,
    Olaf
    • Marked as answer by Colin Reid Monday, October 26, 2009 2:07 PM
    Sunday, October 25, 2009 5:07 PM
  • Another way is recreating column  by droping the old one, like SSMS does(IMO)

    One example, step by step Command, with Transaction(not to change anything if breaks  in middle),

    Dim Sqlstmt1 As String = "ALTER TABLE receipts Add Column  Newname Text(50)"
            Dim Sqlstmt2 As String = "UPDATE receipts SET NewName = OldName"
            Dim Sqlstmt3 As String = "Alter table receipts drop column OldName"
            Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Dim trans As OleDb.OleDbTransaction
            Try
                AccessConnection.Open()
                trans = AccessConnection.BeginTransaction
                Try
                    Dim AccessCommand As New System.Data.OleDb.OleDbCommand(Sqlstmt1, AccessConnection)
                    AccessCommand.Transaction = trans
                    AccessCommand.ExecuteNonQuery()
                    AccessCommand.CommandText = Sqlstmt2
                    AccessCommand.ExecuteNonQuery()
                    AccessCommand.CommandText = Sqlstmt3
                    AccessCommand.ExecuteNonQuery()
                    trans.Commit()
                Catch ex As Exception
                    trans.Rollback()
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
            End Try


    Arjun Paudel
    • Marked as answer by Colin Reid Monday, October 26, 2009 2:07 PM
    Sunday, October 25, 2009 6:08 PM
  • Not sure what type of problem you had using ADOX but I tested the code (below) in VS 2008 and it works fine. Arjun's method may be preferred since you don't need to use COM ADOX, however; make certain that your new column has the same properties as the old column when using this method. Otherwise you could end up with some rather unexpected results.

    ADOX code (requires reference to Microsoft ADO Ext. 2.x for DDL and Security):

    Dim ADOXCatalog As New ADOX.Catalog
    Dim ADOConnection As New ADODB.Connection
    
    ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\...\Documents\Database\db1.mdb;" & _
    "Jet OLEDB:Engine Type=5;")
    
    ADOXCatalog.ActiveConnection = ADOConnection
    ADOXCatalog.Tables("Table1").Columns("Col1").Name = "NewColumnName"
    
    ADOXCatalog.ActiveConnection.Close()
    ADOXCatalog.ActiveConnection = Nothing
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Colin Reid Tuesday, October 27, 2009 11:20 AM
    Monday, October 26, 2009 5:05 PM

All replies

  • could you please post the relevant code?


    VB.Net to C# Convert :)
    Friday, October 23, 2009 2:12 PM
  • Unfortunately Jet SQL doesn't support the ability to rename a column. You can use COM DAO (Data Access Objects) or ADOX (Microsoft ADO Ext. 2.x for DDL and Security). There is an example in the below newsgroup thread:

    http://www.msusenet.com/f-dotnetframeworkadonet-344/t-best-way-rename-column-access-database-file-1874371885.html

     
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Proposed as answer by Olaf Rabbachin Sunday, October 25, 2009 5:07 PM
    Friday, October 23, 2009 2:26 PM
  • Sorry to trouble you again.  I Use VB2008 and can find no mention to ADOX or ADODB in the help file.

    When I tried to add your suggested code, I get an error code to say thet neither are defined.  How do I call ADOX as I note that the code to open the connection is almost identical to that I use to open my connection to the access database (i.e., Jet OLEDB 4)

    regards
    Colin Reid
    Sunday, October 25, 2009 4:29 PM
  • Hi Colin,

    you'll have to add a reference to the library in question. For instance, in order to use ADOX, right-click References, click Add Reference , go to the COM tab and locate the entry that starts with Microsoft ADO Ext and click OK. With the reference in place, you'll be able to use the ADOX-namespace.

    Cheers,
    Olaf
    • Marked as answer by Colin Reid Monday, October 26, 2009 2:07 PM
    Sunday, October 25, 2009 5:07 PM
  • Another way is recreating column  by droping the old one, like SSMS does(IMO)

    One example, step by step Command, with Transaction(not to change anything if breaks  in middle),

    Dim Sqlstmt1 As String = "ALTER TABLE receipts Add Column  Newname Text(50)"
            Dim Sqlstmt2 As String = "UPDATE receipts SET NewName = OldName"
            Dim Sqlstmt3 As String = "Alter table receipts drop column OldName"
            Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Dim trans As OleDb.OleDbTransaction
            Try
                AccessConnection.Open()
                trans = AccessConnection.BeginTransaction
                Try
                    Dim AccessCommand As New System.Data.OleDb.OleDbCommand(Sqlstmt1, AccessConnection)
                    AccessCommand.Transaction = trans
                    AccessCommand.ExecuteNonQuery()
                    AccessCommand.CommandText = Sqlstmt2
                    AccessCommand.ExecuteNonQuery()
                    AccessCommand.CommandText = Sqlstmt3
                    AccessCommand.ExecuteNonQuery()
                    trans.Commit()
                Catch ex As Exception
                    trans.Rollback()
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
            End Try


    Arjun Paudel
    • Marked as answer by Colin Reid Monday, October 26, 2009 2:07 PM
    Sunday, October 25, 2009 6:08 PM
  • Thanks for your but the ADOConnection gave a problem.  However, the germ of my solution lay in the next post which was to add a new column, update setting the new column the same as the old one and then dropping the old one.
    Monday, October 26, 2009 2:05 PM
  • Thanks for the suggestion code as the solution laying my existion Jet code to add a new column, update setting the new column the same as the old one and then dropping the old one.
    regards
    Colin Reid
    Monday, October 26, 2009 2:07 PM
  • Not sure what type of problem you had using ADOX but I tested the code (below) in VS 2008 and it works fine. Arjun's method may be preferred since you don't need to use COM ADOX, however; make certain that your new column has the same properties as the old column when using this method. Otherwise you could end up with some rather unexpected results.

    ADOX code (requires reference to Microsoft ADO Ext. 2.x for DDL and Security):

    Dim ADOXCatalog As New ADOX.Catalog
    Dim ADOConnection As New ADODB.Connection
    
    ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\...\Documents\Database\db1.mdb;" & _
    "Jet OLEDB:Engine Type=5;")
    
    ADOXCatalog.ActiveConnection = ADOConnection
    ADOXCatalog.Tables("Table1").Columns("Col1").Name = "NewColumnName"
    
    ADOXCatalog.ActiveConnection.Close()
    ADOXCatalog.ActiveConnection = Nothing
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Colin Reid Tuesday, October 27, 2009 11:20 AM
    Monday, October 26, 2009 5:05 PM
  • The ADODB.Connection gave rise to an error as it was not defined.

    The new column and old column have the same as the old column.  As you say, Arjuns method avoid using COM ADOX.
    regards
    Colin Reid
    Tuesday, October 27, 2009 11:20 AM
  • Friday, November 12, 2010 1:01 PM
  • Do you by any chance know if the sql command RENAME is available in VS10? It gives me a syntax error.

    Dim strSql$ = "ALTER TABLE [" & tabName & "] RENAME [" & colName & "] TO [" & NewColName & "]"
    Dim dbCommand As New OleDbCommand(strSql, cn)
    dbCommand.ExecuteNonQuery()


    Appleforce
    Friday, November 12, 2010 1:03 PM
  • Paul P C

    Your link here does not exist anymore I guess.

    http://www.msusenet.com/f-dotnetframeworkadonet-344/t-best-way-rename-column-access-database-file-1874371885.html

     

     


     

    Appleforce


    It's probably the below link. It's also posted in my example in this thread.

    http://www.devnewsgroups.net/adonet/t50261-best-way-rename-column-access-database-file.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 12, 2010 1:30 PM
  • Do you by any chance know if the sql command RENAME is available in VS10? It gives me a syntax error.

    Dim strSql$ = "ALTER TABLE [" & tabName & "] RENAME [" & colName & "] TO [" & NewColName & "]"
    Dim dbCommand As New OleDbCommand(strSql, cn)
    dbCommand.ExecuteNonQuery()


    Appleforce

    The Rename keyword is not supported in Microsoft Access if that is what you are asking.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 12, 2010 1:31 PM
  • This is exactly what I'm asking, thanx.

    I have tried to use your code above

    Dim ADOXCatalog As New ADOX.Catalog
    Dim ADOConnection As New ADODB.Connection
    
    ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\...\Documents\Database\db1.mdb;" & _
    "Jet OLEDB:Engine Type=5;")
    
    ADOXCatalog.ActiveConnection = ADOConnection
    ADOXCatalog.Tables("Table1").Columns("Col1").Name = "NewColumnName"
    
    ADOXCatalog.ActiveConnection.Close()
    ADOXCatalog.ActiveConnection = Nothing
    


    but I'm told that th ADODB.Connection is not defined. I thought that came with the package (ADOX).


    Appleforce
    Friday, November 12, 2010 1:53 PM
  • Hi Appleforce,

    did you add a reference to both ADODB and ADOX? If not, your code shouldn't compile. Also, make sure you have both Option Explicit and Option Strict set to on .

    but I'm told that th ADODB.Connection is not defined. I thought that came with the package (ADOX).

    No, that's not the case.


    Cheers,
    Olaf
    http://blogs.intuidev.com
    Friday, November 12, 2010 1:59 PM
  • Thanks for your response.

    I had added the reference to ADOX earlier. But the ADODB reference, I can't find such a reference, what should I look for?


    Appleforce
    Friday, November 12, 2010 2:15 PM
  • Thanks for your response.

    I had added the reference to ADOX earlier. But the ADODB reference, I can't find such a reference, what should I look for?


    Appleforce

    Look for Microsoft ActiveX Data Objects 2.8 Library.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 12, 2010 2:29 PM
  • Thank you, now we are talking!

    I just changed a column name!


    Appleforce
    Friday, November 12, 2010 2:36 PM
  • This is my easy way to do it.. I had two columns I needed to rename.  Qty, and QtyValue.  Qty is the text version of qty (20 ft, 15 in, etc) and QtyValue is the number version.  In a previous query where I did some fiddling they were renamed to: Qtyvaluex, and sqty.  To get them re-named back to their REAL names I did this:

    DoCmd.RunSQL "alter table [cartsparts - list a] add column QtyValue single, Qty text"
    DoCmd.RunSQL "update [cartsparts - list a] set qtyvalue = qtyvaluex, qty = sqty"
    DoCmd.RunSQL "alter table [cartsparts - list a] drop column qtyvaluex, sqty"

    Fill in your own field table/field/field types and it could work for you too!

    -BrianDP


    Brian Prenovost Best Data & Pinball

    Wednesday, December 9, 2020 4:29 PM
  • I would suggest perhaps DAO - but that is over the top to add to some code.

    You can do DDL this way:

    ALTER TABLE [your table] ADD COLUMN [your newcolumn] Text(250)
    UPDATE [your table] SET [your table].[newcolumn] = [your table].[old column]
    ALTER TABLE [your table] DROP COLUMN [oldcolumn]

    Now this is less then ideal if the data is involved in relationships, has indexing, has unique indexing - cascade delete - quite a long list.

    But, for a simple column - above is a possible road - and would only be 3 DDL statements.

    The code would be like this:

       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Using cmdSQL As New OleDbCommand("",
                        New OleDbConnection(My.Settings.TestDB))
                cmdSQL.Connection.Open()
    
                cmdSQL.CommandText = "ALTER TABLE tblHotels2 ADD COLUMN HotelNameZ Text(250)"
                cmdSQL.ExecuteNonQuery()
    
                cmdSQL.CommandText = "UPDATE tblHotels2 SET HotelNameZ = HotelName"
                cmdSQL.ExecuteScalar()
    
                cmdSQL.CommandText = "ALTER TABLE tblHotels2 DROP COLUMN HotelName"
                cmdSQL.ExecuteNonQuery()
    
            End Using
            Beep()
    
        End Sub

    On the other hand? If you have access, you could just open that database, table design mode, and re-name.  ;-)

    So depending on how critical the column is? The above would certainly suffice - but of course the more important and more involved the column is? Then the higher the risk. I guess, I saying make a copy before you do this.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Saturday, December 12, 2020 10:24 AM
  • Well, those commands are for Access. The poster is using vb.net.

    So, you can see my code and other post. If one has Access? then MUCH better to just open the table in design mode, and re-name the column. The reasons are many - but indexing settings, (does it have one), perahps unique index. And also if the column is part of a relationship - all of these things are not only checked, but if the re-name will not case damage, then from the UI it will be allowed.

    But, the poster is using vb.net - you can see my other post here - all in all? Really much the same as what you suggested here.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Saturday, December 12, 2020 10:29 AM