Change a filed name by vba

[BarryMK]

Ooops! Should say "FIELD" name!


I've been through dozens of posts on this and can't get my head around the solution.

Is there a simple way to change the name of a table field?

 

[SQL_Hell]

Hi there

I found a VBA function which looks to be pretty good

Function CreateFieldDDL2()
    'Purpose:   Add a field to a table in another database using DDL.
    Dim strSql As String
    Dim db As DAO.Database
 
    Set db = CurrentDb()
    strSql = "ALTER TABLE Table IN 'C:\Data\junk.mdb' ADD COLUMN MyNewField TEXT (5);"
    db.Execute strSql, dbFailOnError
    Set db = Nothing
    Debug.Print "MyNewField added"
End Function

I got this from the following web site

http://allenbrowne.com/func-DDL.html#CreateViewDDL


*edit*

Oops posted the wrong one

Here is alter column

Sub ModifyFieldDDL()
'Purpose: Change the type or size of a field using DDL.
Dim strSql As String
 
strSql = "ALTER TABLE MyTable ALTER COLUMN MyText2Change TEXT(100);"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

 

[raskew]

Hi -

You might give this a try:

Public Sub RenameField(ptblName As String, _
                       pfldName As String, _
                       pnewfldname As String)
'**************************************************
'Purpose:       Rename a field programatically
'Coded by:      raskew
'Parameters:    ptblName = Name of table to modify
'               pfldname = Name of field to rename
'               pnewfldname = New field name
'(From Debug Window:)
'Inputs: RenameField("tblColors", "hue", "color")
'Output:  Field 'hue' has been renamed 'color'
'**************************************************

Dim db As Database
Dim td As TableDef
Dim fld As Field

    Set db = CurrentDb
    Set td = db.TableDefs(ptblName)
      
    For Each fld In td.Fields
      If fld.Name = pfldName Then
         fld.Name = pnewfldname
         'quit the loop if successful
         Exit For
      End If
    Next fld
    
    db.TableDefs.Refresh
    
    'avoid memory leaks
    Set td = Nothing
    Set db = Nothing
    
End Sub

HTH - Bob

 

[BarryMK]

Thanks Bob I'll take a look at these.

 

[BarryMK]

Hi Bob I'm getting a debug error 'varaible not defined' on tblSugg in Red

Public Sub RenameField(ptblName As String, _
pfldName As String, _
pnewfldname As String)
'**************************************************
'Purpose:       Rename a field programatically
'Coded by:      raskew
'Parameters:    ptblName = Name of table to modify
'               pfldname = Name of field to rename
'               pnewfldname = New field name
'(From Debug Window:)
'Inputs: RenameField("tblColors", "hue", "color")
'Output:  Field 'hue' has been renamed 'color'
'**************************************************


Dim db As Database
Dim td As TableDef
Dim fld As Field

    Set db = CurrentDb
    Set td = db.TableDefs([COLOR=Red]tblSugg[/COLOR])
      
    For Each fld In td.Fields
      If fld.Name = CombinedComments Then
         fld.Name = Comments
         'quit the loop if successful
         Exit For
      End If
    Next fld
    
    db.TableDefs.Refresh
    
    'avoid memory leaks
    Set td = Nothing
    Set db = Nothing
    
End Sub

 

[JANR]

The code you use is a generic code to ues on different tables, so you have to "pass" tablenames and field names to the sub. So change it back to

Set td = db.TableDefs(ptblName)

to use this sub you call it with :

call RenameField("tblSugg","yourOldFieldname","YourNewFieldname")

JR

 

[BarryMK]

Thanks JANR

I reset all the code to Bob's to get rid of my names but I now get a compile error "Can't assign to read-only property" on

For Each fld In td.Fields
If fld.Name = pfldName Then
fld.Name = pnewfldname

 

[JANR]

Public Function RenameField(ptblName As String, _
                       pfldName As String, _
                       pnewfldname As String)
'**************************************************
'Purpose:       Rename a field programatically
'Coded by:      raskew
'Parameters:    ptblName = Name of table to modify
'               pfldname = Name of field to rename
'               pnewfldname = New field name
'(From Debug Window:)
'Inputs: RenameField("tblColors", "hue", "color")
'Output:  Field 'hue' has been renamed 'color'
'**************************************************
Dim db As Database
Dim td As TableDef
Dim fld As Field
    Set db = CurrentDb
    Set td = db.TableDefs(ptblName)
      
    For Each fld In td.Fields
      If fld.Name = pfldName Then
         fld.Name = pnewfldname
         'quit the loop if successful
         Exit For
      End If
    Next fld
    
    db.TableDefs.Refresh
    
    'avoid memory leaks
    Set td = Nothing
    Set db = Nothing
    
End Function

Change it to a FUNCTION instead. Just copy this and paste it into a module and it should work. Works here..

JR

 

[raskew]

Hi -

Having just retested this (as a sub, not a function) from the debug (immediate) window with:

call RenameField("tblColors", "color", "hue")

... and verifying that it indeed replaced 'color' with 'hue', tend to think that it's somehow a problem with your setup. Here's some things you might check:

1) Open the table and ensure the change wasn't made. If it had been and you were unaware, you might encounter some strange messages.

2) Ensure that the field is not involved with a relationship with other table(s). If it is, you may want to rethink what you're attempting. If it is, but you still want to continue, first remove the relationship. If you want code to do that, let me know.

3) Failing both of the above, if you're still unable to make the change, consider posting a sample of your database here so we can take a look.

Best wishes - Bob

 

[BarryMK]

I appreciate your help guys. As a module it's the same. So I created a new table tblColors with fields color and hue and using Bob's code tested it in the debug window with
Call RenameField("tblColors", "hue", "color")
I hope this is correct, and now get Sub or Function not defined.


I'm off to a management meeting for the next two hours so will not catch up on this before tomorrow.


Cheers

 

[raskew]

If you followed JANR's advice (Post #8) and changed my code to a function (and didn't change it back)

call RenameField("tblColors", "color", "hue") won't work, since Call is applicable to Subs, not functions.

However, when I changed my code to a function (something I don't recommend, since a function is intended to return a result, while a sub performs an action) and called it with:

? RenameField("tblColors", "hue", "color")

...it worked as advertised.

Bob

 

[BarryMK]

Thanks for sticking with this. I've attached a small db I made to illustrate my problems. Im sure it's pilot error here but I think I've followed your instructions.

 

[raskew]

Ah so -

"A simple example is worth a thousand words..." or something to that effect. Thank you!

Rule #1 - All objects must have unique names. To do otherwise guarantees disaster.

You have given the module and the sub the same name. Remembering Rule #1, rename the module 'Module1', then, from the debug window (Ctrl-G):

call RenameField("tblColors", "hue", "color") <enter>

Open the table and you'll find that field 'hue' is now field 'color'. - No error messages!

HTH - Bob

 

[BarryMK]

Thanks Bob I only changed the Module name as I was still getting errors and forgot to change it back.

That works fine now from the debug window but how would I call this from a command button or onLoad event?

 

[raskew]

Barry -

You need to develop a test to see if the field name is 'hue' or 'color', because once you've run the sub once, 'hue' is now 'color' (or visa versa) and running the sub again with the same criteria would be meaningless (and would probably produce a whole new batch of errors).

Gotta ask, what's the rationale behind changing field names (and, where are you going with this)?

The code provided was created to prove "It could be done", but not necessarily "It's a great idea".

Bob

 

[BarryMK]

Sorry Bob, it's complex but the reasons are valid and being able to do it would help me automate a difficult job. This change would only take place in a temporary table and would not affect the core tables so data integrity is not going to be an issue.

 

[raskew]

Barry -

Sorry Bob, it's complex but the reasons are valid

You're being kind of cyrptic here. Have to say that I've never encountered a situation where it was beneficial to change field names.

Come on, share it with us -- we won't tell.

Bob

 

[BarryMK]

Re: Change a field name by vba

Sorry Bob I’m not being precious here it is complicated and I’m pressed as always for time.

I’m trying to overcome a problem caused by unavoidable double entry of data. Records from an external application are imported into my Access dbase. There are two fields concerned here Comments and QFT4MoreMemo.

After the import, Comments and QFT4MoreMemo may share the same text or not as the case may be. Where QFT4MoreMemo has data and Comments is blank I need to get the data from QFT4MoreMemo into the empty Comments field.

I wrestled with code for merging the two fields where one is blank etc for hours with no success so turned to a workaround which is where we are. This transfer of data only happens quarterly so needs to be as automated as possible as people forget how to do it. I want to avoid an admin having to export to Excel then scrabble data around manually then re-import. This name change would enable me to run the whole process using Action queries from one form.

Bet you wish you hadn’t asked??

 

[raskew]

Hi again -

Not sure why an update should be so difficult. Here's code against a table (tblTypes2) with two fields (Type and Category) that updates field [type] with the contents of field [category] when field [type] is blank:

UPDATE tblTypes2 SET tblTypes2.Type = [category]
WHERE
   (((tblTypes2.Type) Is Null));

Could you not adapt this to your situation?

Bob

 

[BarryMK]

Bob I searched high and low for code to do this, on this site and others. I got loads of stuff that was complicated and failed miserably on test and you had this all the time! I can't believe how elegantly simple it turns out to be.


Many thanks to you and all who contributed to this thread. I may just owe you a beer or several.