renaming mdb table fields programatically

[Muddy]

How can I change the name of a field of a table in an mdb (using ADO preferably) ?

Thanks for any help!

[robertx]

There is probably an SQL statement that would do it. You could create a new table with the new name, copy the data from the old table to the new table then delete the old table.

[Ecniv]

Have a look at ADOX and search the forum (top right) for this...


Vince

[peet]

Hi there Muddy, I have not found any ways to just rename a field in a db. What I'v had to do is making new field, then copy all the data from the old field into the new one. finally I deleted the old field.

sample

Private Sub Command1_Click()
    Dim sConnString As String
    Dim cnn As New ADODB.Connection
    Dim SQL As String
    
    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"
    cnn.Open sConnString
    
    'create new field
    SQL = "ALTER TABLE Table1 ADD COLUMN NewField TEXT(45)"
    cnn.Execute SQL
    
    'copy data from old field to new field
    SQL = "UPDATE Table1 SET NewField = OldField"
    cnn.Execute SQL
    
    'now, delete the old field
    SQL = "ALTER TABLE Table1 DROP COLUMN OldField"
    cnn.Execute SQL
        
    'Cleanup
    cnn.Close
    Set cnn = Nothing
End Sub

if you find a better way, please let me know

[Muddy]

Thanks Peet,

That works. There is also a RENAME option for the ALTER TABLE SQL statement. I cant get it to work though. Gives "Syntax Error in ALTER statement"

[DaveBo]

In the "better late than never" category, using DAO:

Function Rename_Field_In_Table(ByRef Tablename As String, _
    ByVal OldFldName As String, ByRef NewFldName As String) As Boolean
    ' This will rename a field/column in an existing local table.
    ' call as: Rename_Field_In_Table "MyTable","OldField","NewField"
    ' This returns True if successful
 
    ' Requires Reference to MS DAO 3.6 Object Library (see Tools - References ...)
 
    Dim DB As DAO.Database
    Dim FieldNo As Integer
    Dim strTmp As String
    Dim WS As DAO.Workspace
 
    Set WS = DBEngine.Workspaces(0)
    Set DB = CurrentDb  'WS.OpenDatabase(DBname)    ' Open specified DB
 
    ' Seek OldFldName non-case-sensitive
    OldFldName = UCase$(OldFldName)
 
    With DB
    For FieldNo = 0 To .TableDefs(Tablename).Fields.Count - 1
        If UCase$(.TableDefs(Tablename).Fields(FieldNo).Name) = OldFldName Then
            .TableDefs(Tablename).Fields(FieldNo).Name = NewFldName
            Rename_Field_In_Table = True
            Exit For    ' We're done
        End If
    Next
    End With
 
    Set DB = Nothing
    Set WS = Nothing
 
End Function    ' Rename_Field_In_Table
'#######################################################################

[eranfox]

Reply to Muddy,
Muddy Hello,
I think the RENAME is good only for renaming a TABLE!
peet has it!!!

From The MSDN:

ALTER TABLE – SQL Command Examples

Example 1 adds a field called fax to the customer table and allows the field to have null values.

Example 2 makes the cust_id field the primary key of the customer table.

Example 3 adds a field validation rule to the quantity field of the orders table so that values in the quantity field must be non-negative.

Example 4 adds a one-to-many persistent relation between the customer and orders tables based on the primary key cust_id in the customer table and a new foreign key index cust_id in the orders table.

Example 5 removes the field validation rule from the quantity field in the orders table.

Example 6 removes the persistent relation between the customer and orders tables, but keeps the cust_id index tag in the orders table.

Example 7 adds a field called fax2 to the customer table and prevents the field from containing null values. The new structure of the table is displayed. Two ALTER COLUMN clauses are used to allow the field to have null values and set the default value for the field to the null value. Note that multiple ALTER COLUMN clauses are required to change more than one property of a field in a single ALTER TABLE command. The new field is then removed from the table to restore the table to its original state.

* Example 1
SET PATH TO (HOME(2) + 'Data\') && Sets path to table
ALTER TABLE customer ADD COLUMN fax c(20) NULL

* Example 2
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY

* Example 3
ALTER TABLE orders;
ALTER COLUMN quantity SET CHECK quantity >= 0;
ERROR "Quantities must be non-negative"

* Example 4
ALTER TABLE orders;
ADD FOREIGN KEY cust_id TAG cust_id REFERENCES customer

* Example 5
ALTER TABLE orders ALTER COLUMN quantity DROP CHECK

* Example 6
ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE

* Example 7
CLEAR
ALTER TABLE customer ADD COLUMN fax2 c(20) NOT NULL
DISPLAY STRUCTURE

ALTER TABLE customer;
ALTER COLUMN fax2 NULL;
ALTER COLUMN fax2 SET DEFAULT .NULL.

ALTER TABLE customer DROP COLUMN fax2

Best Regards
ERAN

[DaveBo]

Again in the "better late than never" category, using ADOX this time:

Public Function Rename_Field_In_Table_ADOX(ByRef Tablename As String, _
    ByVal OldFldName As String, ByRef NewFldName As String) As Boolean
    ' This will rename a field/column in an existing local table.
    ' call as: Rename_Field_In_Table_ADOX "MyTable","OldField","NewField"
    ' This returns True if successful
 
    ' Requires Reference to "Microsoft ADO Ext. 2.7 for DDL ..."
 
    Dim objADOXDatabase
    Dim objTable, objColumn
    On Error GoTo ErrBranch
 
    Set objADOXDatabase = CreateObject("ADOX.Catalog")
    objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection    ' Local DB
    ' For an external DB have to setup proper connection, e.g.
    ' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
        "Source=" & App.Path & "\MyDB.mdb"
 
    Set objTable = objADOXDatabase.Tables(Tablename)
 
    'Debug.Print "Table: " & objTable.Name
    'Debug.Print "  OldFldName=" & objTable.Columns(OldFldName).Name
    objTable.Columns(OldFldName).Name = NewFldName
    'Debug.Print "  NewFldName=" & objTable.Columns(NewFldName).Name
    Rename_Field_In_Table_ADOX = True
 
ErrBranch:
    Set objADOXDatabase = Nothing
    Set objTable = Nothing
 
End Function    ' Rename_Field_In_Table_ADOX
'#######################################################################

See what happens when you don't set your thread to "Resolved" !!

[clickman]

Again in the "better late than never" category, using ADOX this time:

Public Function Rename_Field_In_Table_ADOX(ByRef Tablename As String, _
    ByVal OldFldName As String, ByRef NewFldName As String) As Boolean
    ' This will rename a field/column in an existing local table.
    ' call as: Rename_Field_In_Table_ADOX "MyTable","OldField","NewField"
    ' This returns True if successful
 
    ' Requires Reference to "Microsoft ADO Ext. 2.7 for DDL ..."
 
    Dim objADOXDatabase
    Dim objTable, objColumn
    On Error GoTo ErrBranch
 
    Set objADOXDatabase = CreateObject("ADOX.Catalog")
    objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection    ' Local DB
    ' For an external DB have to setup proper connection, e.g.
    ' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
        "Source=" & App.Path & "\MyDB.mdb"
 
    Set objTable = objADOXDatabase.Tables(Tablename)
 
    'Debug.Print "Table: " & objTable.Name
    'Debug.Print "  OldFldName=" & objTable.Columns(OldFldName).Name
    objTable.Columns(OldFldName).Name = NewFldName
    'Debug.Print "  NewFldName=" & objTable.Columns(NewFldName).Name
    Rename_Field_In_Table_ADOX = True
 
ErrBranch:
    Set objADOXDatabase = Nothing
    Set objTable = Nothing
 
End Function    ' Rename_Field_In_Table_ADOX
'#######################################################################

See what happens when you don't set your thread to "Resolved" !!

Hummm .. this looks promising and while I can do this "Renaming of a Field" with the ALTER TABLE and then copy data from old field to new field and then DROP COLUMN solution as outlined at the beginning of this thread , I would like to see about doing it the way proposed above as the Public Function Rename_Field_In_Table_ADOX way .. Again, however, I need to do it with my protected database.

From the previous solutions in this forum dealing with the correct strings for the ALTER TABLE, on my protected database I used the following and it worked fine to establish the connection ... and then make the insertion to my mdw protected database

Public Sub ConnectToDatabase()



Dim g_str_DatabasePath As String
Dim g_str_DataBase_MDW_SecurityFilePath As String

'
'            'Secured Database and Security File Paths
                g_str_DatabasePath = App.Path & "\Data Files\Databases\exampleprotected.mdb"
                g_str_DataBase_MDW_SecurityFilePath = App.Path & "\Data Files\Databases\exampleprotected.mdw"
                
                Debug.Print g_str_DatabasePath
                Debug.Print g_str_DataBase_MDW_SecurityFilePath

        cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
        cnn.Properties("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"

Debug.Print g_str_DatabasePath
Debug.Print g_str_DataBase_MDW_SecurityFilePath


    cnn.Open "Data Source= " & g_str_DatabasePath & ";User Id= johndoe;Password= 1234;"
      
   
  'From vbForums Example Code that I then modified for my mdw protected database
  
'   cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
'   cnn.Properties("Jet OLEDB:System database") = _
'      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
'
'   cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"

'   cnn.Close
'
'    strSqlA = "ALTER Table Adressen ADD Column myNewField varChar(35) Null"

'cnn.Execute strSqlA
  
  

End Sub



Private Sub cmd_Execute_Click()

        strSqlA = "ALTER Table TABLE1 ADD Column myNewField varChar(35) Null"
        
        cnn.Execute strSqlA
    
    cnn.Close
    
End Sub

I am trying now to create the correct connection within the suggested Public Function Rename_Field_In_Table_ but am confused as to the syntax to connect tot he protected database

Here is what I am trying, and having blocks on:

    


'FROM THE SUGGESTED FUNCTION
'*********
    '*****objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection    ' Local DB
    
    ' For an external DB have to setup proper connection, e.g.
    ' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
        "Source=" & App.Path & "\MyDB.mdb"
 '*************


 'THIS IS WHAT I AM TRYING AND NOT HAVING SUCCESS WITH

 objADOXDatabase.ActiveConnection.Provider = "Microsoft.Jet.OLEDB.4.0;"
 objADOXDatabase.ActiveConnection.Properties = ("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
 objADOXDatabase.ActiveConnection.Open "DataSource =  " & g_str_DatabasePath & " ;User Id= johndoe;Password= 1234;"

I am trying to model the previous way of making a connection into this new function but I am off base I think trying to use that objADOXDatabase.ActiveConnection.Provider = line ...

Advice????

[Arnoutdv]

Better start a new thread and copy or point to the relevant information in this very old thread.

[ChrisE]

'FROM THE SUGGESTED FUNCTION
'*********
'*****objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection ' Local DB

' For an external DB have to setup proper connection, e.g.
' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=" & App.Path & "\MyDB.mdb"
'*************


'THIS IS WHAT I AM TRYING AND NOT HAVING SUCCESS WITH

objADOXDatabase.ActiveConnection.Provider = "Microsoft.Jet.OLEDB.4.0;"
objADOXDatabase.ActiveConnection.Properties = ("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
objADOXDatabase.ActiveConnection.Open "DataSource = " & g_str_DatabasePath & " ;User Id= johndoe;Password= 1234;"[/CODE]



I am trying to model the previous way of making a connection into this new function but I am off base I think trying to use that objADOXDatabase.ActiveConnection.Provider = line ...

Advice????

try...

Set cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
    .Mode = adModeShareDenyNone
    ' Jet-Provider
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    'path to DB
    .Properties("Data Source") = App.Path & "\MyDB.mdb"
    'password for Database ?
   ' .Properties("Jet OLEDB:Database Password") = "test"
   'for .mdw
    'path System- und Securety-MDW
    .Properties("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
    ' UserId
    .Properties("User ID") = "johndoe"
    ' Password
    .Properties("Password") = "testpwd"
    .Open
End With

[clickman]

Chris E

What you posted does work for me to do an Alter Table ... Add Columns action- I got that covered based on the forum help a couple weeks ago.

What I am trying to do, however, is to change the name of an already existing column. That Function posted by DaveBo to do that was what I was trying to do. Again, however, i have a mdw protected database.

[ChrisE]

I'm not sure what the problem is ?

I showed you how to access the .mdw
does the Functionwork from 'DaveBo' ?

I'm also not sure why you work with a .mdw, I think as of Access 2007 there is no more .mdw
do you have to use a .mdw?

[clickman]

I'm not sure what the problem is ?

I showed you how to access the .mdw
does the Functionwork from 'DaveBo' ?

I'm also not sure why you work with a .mdw, I think as of Access 2007 there is no more .mdw
do you have to use a .mdw?

The reason for needing solutions to work with a database having user level security, is that I have to do maintenance on client installs that have such a databases already on their systems.

As for showing how to access the MDW and establish the connection and then to add a column, yes your solution for establishing a connection and for adding columns is super, and I have used it to do just that.. The situation I am looking at now is trying to use the DanBo code for his Function that he calls "Rename_Field_In_Table_ADOX" against an MDW protected DB, to change the name of the field directly - which will do the action directly without doing an add column and then copy and then doing a drop column

His function uses what appears to me is a connection object objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection, and I am confused about how to implement his code against a User Level Security [MDW] protected database.




Where then do I insert your solution

Set cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
    .Mode = adModeShareDenyNone
    ' Jet-Provider
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    'path to DB
    .Properties("Data Source") = App.Path & "\MyDB.mdb"
    'password for Database ?
   ' .Properties("Jet OLEDB:Database Password") = "test"
   'for .mdw
    'path System- und Securety-MDW
    .Properties("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
    ' UserId
    .Properties("User ID") = "johndoe"
    ' Password
    .Properties("Password") = "testpwd"
    .Open
End With

into Danbo's direct solution [ In the DanBo code, have highlighted in blue the area in question where I have been trying to re-structure his code by using code of the same style as in your earlier Alter Table .. Add Column solution but No Joy.]

Public Function Rename_Field_In_Table_ADOX(ByRef Tablename As String, _
    ByVal OldFldName As String, ByRef NewFldName As String) As Boolean
    ' This will rename a field/column in an existing local table.
    ' call as: Rename_Field_In_Table_ADOX "MyTable","OldField","NewField"
    ' This returns True if successful
 
    ' Requires Reference to "Microsoft ADO Ext. 2.7 for DDL ..."
 
    Dim objADOXDatabase
    Dim objTable, objColumn
    On Error GoTo ErrBranch
 
    Set objADOXDatabase = CreateObject("ADOX.Catalog")
    objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection    ' Local DB
    ' For an external DB have to setup proper connection, e.g.
    ' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
        "Source=" & App.Path & "\MyDB.mdb"
 
    Set objTable = objADOXDatabase.Tables(Tablename)
 
    'Debug.Print "Table: " & objTable.Name
    'Debug.Print "  OldFldName=" & objTable.Columns(OldFldName).Name
    objTable.Columns(OldFldName).Name = NewFldName
    'Debug.Print "  NewFldName=" & objTable.Columns(NewFldName).Name
    Rename_Field_In_Table_ADOX = True
 
ErrBranch:
    Set objADOXDatabase = Nothing
    Set objTable = Nothing
 
End Function    ' Rename_Field_In_Table_ADOX
'#######################################################################

It is that

 objADOXDatabase.ActiveConnection = ??????? 

that I am trying to figure out .. if indeed it IS figureoutable

What is that ???? supposed to be?

[ChrisE]

Hi,

see if this helps, you need a Ref. to Adox

Private Sub Command5_Click()
 'From vbForums Example Code that I then modified for my mdw protected database
  
   cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
   cnn.Properties("Jet OLEDB:System database") = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

   cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"

Call renameColumn(cnn, "Customers", "CompanyName", "NewCompanyName", , , True)

   cnn.Close
'
End Sub


Public Function renameColumn(ActConn As ADODB.Connection, _
                             TableName As String, _
                             ColumnName As String, _
                             NewName As String, _
                             Optional ErrNumber As Long = 0, _
                             Optional ErrDescription As String = "", _
                             Optional ShowErrorMsg As Boolean = True) _
                             As Boolean

   Dim Cat As ADOX.Catalog
   
      Set Cat = New ADOX.Catalog
      Set Cat.ActiveConnection = ActConn

      On Error GoTo Fehler
      If (ColumnName <> NewName) And (Len(NewName) > 0) Then
         Cat.Tables(TableName).Columns(ColumnName).Name = NewName
      End If
      renameColumn = True

Fehler:
      If Err.Number <> 0 Then
         ErrNumber = Err.Number
         ErrDescription = Err.Description
         If ShowErrorMsg Then
            FehlerAnzeige Err.Number, Err.Description, _
                          "RenameColumn: " & ColumnName & _
                          " (" & TableName & ")"
         End If
      End If
      On Error GoTo 0
      Set Cat = Nothing
End Function

Private Sub FehlerAnzeige(ErrNumber As Long, _
                          ErrDescription As String, _
                          Optional Titel As String = "")

   Dim Msg As String
   
      Msg = "Fehler " & ErrNumber & vbCrLf & vbCrLf & _
            ErrDescription
      If Len(Titel) > 0 Then
         MsgBox Msg, vbCritical, Titel
      Else
         MsgBox Msg, vbCritical
      End If

      'hier ggf Eintrag in FehlerProtokoll
End Sub

[clickman]

Let me take a look ...!!!!

[clickman]

YES! It works great .... This will really help with my work on the existing client databases. We had to change our underlying VB code that now interacts with these databases, and this required a change in the name of one of the columns in a particular table, AND the addition of several new columns to that table. I was faced with doing each database manually and this streamlines things.

THANK YOU!

[clickman]

Is this the way to mark as resolved

[ChrisE]

YES! It works great .... This will really help with my work on the existing client databases. We had to change our underlying VB code that now interacts with these databases, and this required a change in the name of one of the columns in a particular table, AND the addition of several new columns to that table. I was faced with doing each database manually and this streamlines things.

THANK YOU!

glad you got it working

you do know that renaming a Column can cause problems
if the Column is a FOREIGN KEY to another Table, or was that Column INDEXED

just making sure you know that

[clickman]

glad you got it working

you do know that renaming a Column can cause problems
if the Column is a FOREIGN KEY to another Table, or was that Column INDEXED

just making sure you know that

You making sure is most helpful .. I was in the next stage of thinking about that what you mention. Also thinking about how to set other properties of the field.. for example input mask etc if possible