uppercase field names

D

Dennis

Monthly, I need to import a database and uppercase all the field names in all
its tables. Is there a quicker way to do this than to just retype all the
field names every month?
 
O

Ofer

Hi Dennis,
This function example get a table name and change the fields name to upper
case

Function RenameFields(TableName As String) As String
Dim MyDb As DAO.Database
Dim MyRec As DAO.TableDef
Dim FldName As DAO.Field
Dim I As Integer

Set MyDb = CodeDb()
Set MyRec = MyDb.TableDefs(TableName)
For I = 0 To MyRec.Fields.Count - 1
Set FldName = MyRec.Fields(I)
FldName.Name = UCase(FldName.Name)
Next I
End Function
 
D

Douglas J. Steele

What do you mean by "uppercase field names"? You want the name of each field
in each table to be uppercase? Whatever for?

If you have a legitimate reason (which I can't imagine!), the following DAO
code will do it:

Sub NamesToUpper()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()

For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
fldCurr.Name = UCase$(fldCurr.Name)
Next fldCurr
End If
Next tdfCurr

Set dbCurr = Nothing

End Sub
 
6

'69 Camaro

Hi, Dennis.

Public Sub UCaseFldNames()

On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim idx As Long

Set db = CurrentDb()

For Each tbl In db.TableDefs
If (Left$(tbl.Name, 4) <> "MSys") Then
For idx = 0 To (tbl.Fields.Count - 1)
tbl.Fields(idx).Name = UCase$(tbl.Fields(idx).Name)
Next idx
End If
Next tbl

CleanUp:

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in UCaseFldNames( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
I

ivan_haohao

Ofer said:
Hi Dennis,
This function example get a table name and change the fields name to upper
case

Function RenameFields(TableName As String) As String
Dim MyDb As DAO.Database
Dim MyRec As DAO.TableDef
Dim FldName As DAO.Field
Dim I As Integer

Set MyDb = CodeDb()
Set MyRec = MyDb.TableDefs(TableName)
For I = 0 To MyRec.Fields.Count - 1
Set FldName = MyRec.Fields(I)
FldName.Name = UCase(FldName.Name)
Next I
End Function
Click to expand...
 
I

ivan_haohao

Douglas J. Steele said:
What do you mean by "uppercase field names"? You want the name of each field
in each table to be uppercase? Whatever for?

If you have a legitimate reason (which I can't imagine!), the following DAO
code will do it:

Sub NamesToUpper()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()

For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
fldCurr.Name = UCase$(fldCurr.Name)
Next fldCurr
End If
Next tdfCurr

Set dbCurr = Nothing

End Sub
Click to expand...
 
Top