Change/Rename a field Name in a Table using vba 2

[sxschech]

I was able to use vba to change the name of a field, but was dependent on that field being in a specific col. I found some code which I thought would allow me to change the name of the field regardless of its position, however I receive an error message [tt]The database engine could not lock table 'netprospex' because it is already in use by another person or process.[/tt]

What I would like is the code to find out if there is a field in the table which is imported from an outside source and then if the field is called Title or Executive Title would be renamed to ETitle.

Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
        
    Set db = CurrentDb
    Set rs = db.OpenRecordset(Me.cboInputTable, dbOpenTable).Clone

    For Each fld In rs.Fields
        'Debug.Print fld.Name
        'Debug.Print fld.OrdinalPosition
        If InStr(fld.Name, "Title") <> 0 Then
            db.TableDefs(Me.cboInputTable).Fields(fld.OrdinalPosition).Name = "ETitle"
        End If
    Next fld
    Set fld = Nothing
    rs.Close
    Set rs = Nothing

I originally had this by itself, [tt]db.TableDefs(Me.cboInputTable).Fields(1).Name = "ETitle"[/tt] which worked, but of course was informed that the field could be in the second col, or any other col such as the 8th. I was hoping that with the loop, it could find the field and then change it, so I don't need to know specifically which col.

Alternatively, could I write an if statement in the vba to create an sql statement that would give the appropriate name, [tt]SELECT * FROM Table WHERE (if fieldname is Title or FieldName is Executive Title) = "President"[/tt]
Becoming
[tt]SELECT * FROM Table WHERE Title = "President" -->If the fieldname is Title
SELECT * FROM Table WHERE [Executive Title] = "President" -->If the fieldname is Executive Title[/tt]

 

[dhookom]

Your table is open in code so you can't change its design.

If you know the name of the field, why do you need to retrieve its Ordinal?

db.TableDefs(Me.cboInputTable).Fields("Title").Name = "ETitle"

Duane
Hook'D on Access
MS Access MVP

 

[sxschech]

I thought that because I don't know where the field name/col is going to be located in the table that I would need to get the number as I won't know the specific name of the field, it could be Title or Executive Title, perhaps there may be other fields I'm not currently concerned with that will need to be renamed to standardize for running downstream queries. After taking a break and thinking a bit more, I think I figured out what to do, I assigned the value to a string and then put the TableDef at the end of the code after the recordset was closed.

    For Each fld In rs.Fields
        If InStr(fld.Name, "Title") <> 0 Then
            stOldName = fld.Name
        End If
    Next fld
    Set fld = Nothing
    rs.Close
    Set rs = Nothing
    db.TableDefs(Me.cboInputTable).Fields(stOldName).Name = "ETitle"
    Set db = Nothing

 

[MajP]

http://msdn.microsoft.com/en-us/library/office/dn142653(v=office.15).aspx

use the fields collection of the the tabledefs object, no need to open a recordset as previously stated. So you are doing a lot of extra work.

 

[jrbarnett]

Alternatively look at this solution:

http://windowssecrets.com/forums/showthread.php/49586-ALTER-TABLE-to-RENAME-COLUMN-(Access-XP-SP2)

John

 

[sxschech]

Thanks for your responses MajP and jrbarnett. I think I need the recordset, as an example, given a table with 12 fields and not knowing if the field in question is called "Title" or "Executive Title" or "ExecutiveTitle", etc., or what position it is located in, how would I identify the field that needs to be renamed without doing a loop to locate it. Once I know the existing name, then I can use that to rename it. Is there another way I'm not seeing to evaluate the list of fields to identify the one in question not using a recordset or loop as the links provided seem to need to already know the name of the field. If I knew the field(s) in question were always in the same position, then I wouldn't care about the names, but since they can be provided in col 2 in some cases and others in col 8 or 10 depending on the vendor source, wanted to figure out a way without having to do manual stuff on import etc.

 

[dhookom]

Here is a generic function that might work for you:

Public Function GetFieldOrdinal(pstrTableName As String, pstrFieldName As String) As Integer
[COLOR=#4E9A06]    ' search for a field with a name containing pstrFieldName
    ' function will return the maximum ordinal postion if there are more than one matching field[/color]
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Set db = CurrentDb
    Set td = db.TableDefs(pstrTableName)
    For Each fd In td.Fields
        If fd.Name Like "*" & pstrFieldName & "*" Then
            GetFieldOrdinal = fd.OrdinalPosition
        End If
    Next
    Set fd = Nothing
    Set td = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I think I need the recordset, as an example, given a table with 12 fields and not knowing if the field in question is called "Title" or "Executive Title" or "ExecutiveTitle", etc., or what position it is located it

The point was that you can loop the fields collection without opening the recordset, which keeps you from editing them. You can do the same thing with the tabledef, and still edit.

 

[sxschech]

Thanks Duane and MajP. I tried to click on star for MajP after doing one for Duane, but wouldn't do anything. Looking at Duane's code, I finally saw what you were talking about with the fields instead of recordset. Duane, your code was very helpful.