none
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