[RESOLVED] Upsizing from Access 2k3 to SQL, need to change queries and macros programmatically

As you can see from the title I am attempting to upsize an Access 2k3 database to SQL. One of the major tables has a field name that is also a SQL reserved keyword (CAGE). There are a whole lot of Queries and Macros that use this field so I am looking for an easy way to write a macro or function to search through each one and change and instances of CAGE to CAGEcode.

I searched on here and found an example of how to change table data so I am pretty sure what I am trying to accomplish can be done.

I don't want to reference the queries by name so I am assuming I can reference them by an index number and just cycle through.

Any info would be greatly appreciated. Thanks! :wave:

dingle

Hello,

I've tried this in a 2k3 access db.
It's with DAO but works like a charm

Code:

---

Sub test()
Dim tdf As TableDef
Dim fld As dao.Field

For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
        If InStr(1, UCase(fld.Name), "CAGE", vbTextCompare) <> 0 Then
            tdf.Fields(fld.Name).Name = "New_" & fld.Name
        End If
       
   
    Next

Next
End Sub

---

Quote:

---

Originally Posted by Killazzz

Hello,

I've tried this in a 2k3 access db.
It's with DAO but works like a charm

Code:

---

Sub test()
Dim tdf As TableDef
Dim fld As dao.Field

For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
        If InStr(1, UCase(fld.Name), "CAGE", vbTextCompare) <> 0 Then
            tdf.Fields(fld.Name).Name = "New_" & fld.Name
        End If
       
   
    Next

Next
End Sub

---

---

That is exaclty what I am trying to do. Thanks for your help! :thumb: