Need Help Referencing Field Names in Code!! 2

[DCBBB]

I am trying to rename the field names of a table in code. This table (I'll call ChangeTable)is the result of a make table query off of a crosstab, so the field names will always vary, but I do list these fieldnames in a second table (FieldTable). To rename my fields, I am looping through code that creates a temp field with the new name, dumps the data into the new field, and then deletes the old field. BUT, I can't seem to get the code to work as I am having problems referencing the original field name. Here's what I have:

Sub RenameField()

Dim sSQL As String
Dim iPosition As Integer
Dim OldField As String
X As Integer

FieldCount = FieldTable.RecordCount
For X = 1 To FieldCount
OldField = FieldTable![FieldName]
NewField = "Field" & Format(X) 'I want the new field name to be Field1, Field2, etc

iPosition = CurrentDB.TableDefs_("ChangeTable&quot.Fields(OldField).OrdinalPosition 'SNAG HERE!

sSQL = "ALTER TABLE Table1 ADD COLUMN Temp Text"
CurrentDB.Execute sSQL
sSQL = "UPDATE DISTINCTROW ChangeTable SET Temp =_ OldField"
CurrentDB.Execute sSQL
sSQL = "ALTER TABLE ChangeTable DROP COLUMN_ OldField"
CurrentDB.Execute sSQL
CurrentDB.TableDefs("ChangeTable&quot.Fields_("Temp&quot.Name = NewField
CurrentDB.TableDefs("ChangeTable&quot.Fields_(NewField).OrdinalPosition = iPosition
Next X

End Sub

As mentioned, it seems like my field references are not being recognized, though I am sure there are more bugs I haven't hit yet.
Help!
Thanks,
D

 

[nickjar2]

Try this:

Sub RenameField()

Dim sSQL As String
Dim iPosition As Integer
Dim OldField As String
Dim X As Integer
Dim tbl As TableDef
Dim db As DAO.Database
Dim fieldcount

Set db = CurrentDb

Set tbl = db.TableDefs("table1&quot

fieldcount = tbl.Fields.Count
For X = 1 To fieldcount
OldField = tbl(X - 1).Name
NewField = "Field" & X 'I want the new field name to be Field1 , Field2, etc

'iPosition = db.TableDefs_("ChangeTable&quot.Fields(OldField).OrdinalPosition
'SNAG HERE!
iPosition = db.TableDefs("table1&quot.Fields(OldField).OrdinalPosition

sSQL = "ALTER TABLE Table1 ADD COLUMN Temp Text"
CurrentDb.Execute sSQL
sSQL = "UPDATE table1 SET Temp = '" & OldField & "'"
CurrentDb.Execute sSQL
sSQL = "ALTER TABLE table1 DROP COLUMN " & OldField
CurrentDb.Execute sSQL
CurrentDb.TableDefs("table1&quot.Fields("Temp&quot.Name = NewField
CurrentDb.TableDefs("table1&quot.Fields(NewField).OrdinalPosition = iPosition
Next X

NickEnd Sub

 

[nickjar2]

Try this:

Sub RenameField()

Dim sSQL As String
Dim iPosition As Integer
Dim OldField As String
Dim X As Integer
Dim tbl As TableDef
Dim db As DAO.Database
Dim fieldcount

Set db = CurrentDb

Set tbl = db.TableDefs("table1&quot

fieldcount = tbl.Fields.Count
For X = 1 To fieldcount
OldField = tbl(X - 1).Name
NewField = "Field" & X 'I want the new field name to be Field1 , Field2, etc

'iPosition = db.TableDefs_("ChangeTable&quot.Fields(OldField).OrdinalPosition
'SNAG HERE!
iPosition = db.TableDefs("table1&quot.Fields(OldField).OrdinalPosition

sSQL = "ALTER TABLE Table1 ADD COLUMN Temp Text"
CurrentDb.Execute sSQL
sSQL = "UPDATE table1 SET Temp = '" & OldField & "'"
CurrentDb.Execute sSQL
sSQL = "ALTER TABLE table1 DROP COLUMN " & OldField
CurrentDb.Execute sSQL
CurrentDb.TableDefs("table1&quot.Fields("Temp&quot.Name = NewField
CurrentDb.TableDefs("table1&quot.Fields(NewField).OrdinalPosition = iPosition
Next X

End sub

Nick

 

[nickjar2]

Also D,

I have changed the table name to Table1 (from changeTable). You have a nice routine there. I think this could come in really handy.

Nick

 

[fogal]

This code was used to create new fields in an existing table in an external db.

By modifying it you should be able to change table names

Dim dbschange As Database
Dim tdfnew As TableDef


Set dbschange = OpenDatabase("c:\changenew2.mdb&quot
Set tdfnew = dbschange.TableDefs!contacts
With tdfnew
.Fields.Append .CreateField("dsp1", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> &quot;&quot; Then Debug.Print &quot; &quot; & _
prpLoop.Name & &quot; = &quot; & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
'dbschange.TableDefs.Append tdfnew

Debug.Print &quot;Properties of new TableDef object &quot; & _
&quot;after appending to collection:&quot;

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> &quot;&quot; Then Debug.Print &quot; &quot; & _
prpLoop.Name & &quot; = &quot; & prpLoop
On Error GoTo 0
Next prpLoop

End With

dbschange.Close


Cheers!

 

[DCBBB]

Thanks for responding!
Nick, I like your improvements but for some reason I still get hung up on the same line:
iPosition = db.TableDefs(&quot;table1&quot.Fields(OldField).OrdinalPosition
I get an &quot;Item not found in collection&quot; error.
Could it have anything to do with the way I am declaring my field variables? I don't know. I'll fiddle with it and try playing with Fogal's code. Any further suggestions are appreciated. Thanks!
D

 

[DCBBB]

Okay, Nick, looks like I got her ALMOST working. The above error was because I didn't restore all the proper table names (Duh!). Now the field names are changing, but the old data is not being dumped into the new field. Instead, the old field name is being dumped there, repeated over and over! Any ideas?

 

[nickjar2]

I am not in work at the mo (UK) but i will look first thing tomorrow. I don't have Acc at home unfortunately.

Nick

 

[nickjar2]

DCBBB,

Amend one of the lines to this:

sSQL = &quot;UPDATE table1 SET Temp = [&quot; & OldField & &quot;]&quot;

I have tried this and it works.

Nick

 

[DCBBB]

Yipee! That did the trick, Nick! Now I can finish my report and look like a hero! You get my vote for tipmaster!
D

 

[nickjar2]

D,

Glad ot works.

Nick