rename fields programmatically

[mcnewsxp]

is there a quick way to rename fields in tables?
for example replace spaces with an underscore.

then is there a quick way to run through queries and forms that reference
these fields and fix them.

either queries, code or 3rd party tool.

thanks much!
mcnewsxp

 

[Douglas J. Steele]

Using DAO (so if you're using Access 2000 or newer, you'll have to make sure
you've set a reference to DAO):

Dim dbCurr As Database
Dim tdfCurr As TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
For Each fldCurr In tdfCurr.Fields
fldCurr.Name = Replace(fldCurr.Name, " ", "_")
Next fldCurr
Next tdfCurr

Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing

Actually, if you're NOT using Access 2000 or newer, you're going to have to
write your own Replace function, as it wasn't introduced in Access until
Access 2000. The code in http://www.mvps.org/access/strings/str0004.htm at
"The Access Web" is one way to do it.

 

[mcnewsxp]

beautiful!
thanks!

know of any tools that can do same for queries and forms? or at least find
things that aren't linked up correctly?

 

[Douglas J. Steele]

You can write similar code for queries and forms, or you can purchase a 3rd
party product such as Find & Replace: http://www.rickworld.com/products.html
or Speed Ferret: http://www.moshannon.com/speedferret.html

Not quite sure I understand what you mean by "aren't linked up correctly".