I Need to change the name of a field in all DB Objects

[kmclane]

Is there some way to do a global "search and replace" in Access 2003. I have several tables and lots of queries that utilize the field name "MID". I don't relish the idea of going through 700 tabkes and 275 queries and a bunch of vba code to hand change every instance. I would like to change all references to "Merchant Number". I did some searching, but haven't run across anything concrete so far. Point me in the right direction please. Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

OK, I don't see a way to make changes in queries other than manually, replace doesn't seem to work in design view. I have code in place to give me a list of tables and loop through the list. Now I need to be able to pull a second list of field names in each table, check to see if the name of one matches "MID", change it to "Merchant Number" and then go to the next. I have an idea I may be able to if nothing else copy my queries out to a text editor and do a replace and copy them back. Still faster than doing it by hand. I think there is a fieldname collection that I can use to get the list of fields, but darn if I can find any info in the help files. Can someone point me at how to pull that into a RS? Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Ascentient]

I am not sure how this would be done, but I would venture a guess that you could create an SQL statement that will display the structure of your table in a table.

From there you could do a find or create a loop that checked the field name against what you are looking for and change it as needed.

I spent the last 10 minutes trying to find you some examples but have turned up nothing. I know in other languages this is possible in some fashion.

I hope this "pushes" you in the right direction.

 

[kmclane]

Here is as far as I have gotten:

Function ChangeFieldName()
Dim rs As Recordset
Dim rs1 As Recordset
Dim db As DAO.Database
Dim Tabname As String
Set db = CurrentDb
Set rs = db.OpenRecordset("listTables")
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
Tabname = rs(0).Value
Set rs1 = Tabname.Fields
Do While Not rs1.EOF
If rs1(0).Value = "MID" Then
rs1(0) = "Merchant Number"
End If
rs1.MoveNext
Loop
rs.MoveNext
Loop
End Function

I can't seem to figure out what to use for this line:
Set rs1 = Tabname.Fields
This give me "Invalid Qualifier". I know there is a way to get a list, just cannot find it. Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Got it working!

Function ChangeFieldName()
Dim rs As Recordset
Dim rs1 As DAO.TableDef
Dim db As DAO.Database
Dim Tabname As String
Dim FLD As Field
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("listTables")
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
Tabname = rs(0).Value
Set rs1 = db.TableDefs(Tabname)
For Each FLD In rs1.Fields
If FLD.Name = "MID" Then
FLD.Name = "Merchant Number"
End If
Next FLD
rs.MoveNext
Loop
End Function

Hope this helps someone in the future.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Ascentient]

Good Job! I was just looking through the tabledefs syntax, but was a little lost