CODEWITHSUNDEEP
ms-accessscriptingvba
logicForPresident
logicForPresident

Reputation: 311

Rewrite field names in multiple tables - skipping over missing fields

I have an Access database with nearly 200 tables each with about 150 field. I need to change most of the field names and am writing some VBA code to programatically update the names to what I assign. The problem is that not all tables have exactly the same fields (some fields are missing from certain tables). Is there a way that I can write the change in the code, but just skip the change for a particular field if it doesn't exist in a particular table?

The basic code that I am using comes from this site: http://windowssecrets.com/forums/showthread.php/125845-Change-Field-name-in-Table-with-VBA

And here is my actual code. I'm brand new to VBA, so please be gentle.

Option Compare Database
Option Explicit

Public Sub changeFieldName()

Dim db As DAO.Database
Dim table As DAO.TableDef

Set db = CurrentDb
For 
Set table = db.TableDefs("table")

table.Fields("Field1").Name = "name1"
table.Fields("Field2").Name = "name2"
if(IsNull(table.Fields("FieldDoesn'tExist").Name = "name")) Then End If

Set table = Nothing
Next    

db.Close
Set db = Nothing


MsgBox "Changed"

End Sub

The "Then End If" statement doesn't work, but I don't know what to use here

Thanks for any help!
Paul

Upvotes: 2

Views: 247

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

The way I'd approach it would be to put the old and new names into their own table named [NameMap]:

oldName   newName 
--------  --------
oldName1  newName1
oldName2  newName2

and then loop through that list to apply the name changes. The On Error Resume Next statement would allow the routine to continue if the table did not have a field whose name corresponded to one of the [oldName] values

Option Compare Database
Option Explicit

Public Sub changeFieldNames()
    Dim cdb As DAO.Database, rst As DAO.Recordset, tbd As DAO.TableDef
    Set cdb = CurrentDb
    Set tbd = cdb.TableDefs("SampleTable")
    Set rst = cdb.OpenRecordset("SELECT oldName, newName FROM NameMap", dbOpenSnapshot)
    Do Until rst.EOF
        On Error Resume Next
        tbd.Fields(rst!oldName).Name = rst!newName
        On Error GoTo 0
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set tbd = Nothing
    Set cdb = Nothing
End Sub

Upvotes: 4

Related Questions