Putting a prefix on a fieldname

[Eric]

I have an access db with 6 tables. Four of them i want to merge into
one table. Merging them will give me a poor design but will be much
easier to work with a specific app. Performance isn't an issue. Low
usage, not many rows, and being slow is ok.
Before i copy and paste all the rows over, i want to put a prefix on
each field so i know which table they came from. There are a LOT of fields.

Is there a quick way to put a prefix on all (or a selection) of fields
in a table?

[Rick Wannall]

Public Function NameChangeTest()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("MyTable")
For Each fld In tdf.Fields
fld.Name = "MyPrefix_" & fld.Name
Next fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Function
However, it seems an odd thing to do, to be frank.

[salad]

Eric wrote:

I have an access db with 6 tables. Four of them i want to merge into
one table. Merging them will give me a poor design but will be much
easier to work with a specific app. Performance isn't an issue. Low
usage, not many rows, and being slow is ok.
Before i copy and paste all the rows over, i want to put a prefix on
each field so i know which table they came from. There are a LOT of fields.

Is there a quick way to put a prefix on all (or a selection) of fields
in a table?

Here I put an extension
Sub TableFieldRename()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!Table1
For Each fld In tdf.Fields
'append the number 1 to each field name
fld.name = fld.name & "1"
Debug.Print fld.name
Next
Set tdf = Nothing
dbs.Close
Set dbs = Nothing
MsgBox "Done"
End Sub

[Eric]

Rick Wannall wrote:

Public Function NameChangeTest()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("MyTable")
For Each fld In tdf.Fields
fld.Name = "MyPrefix_" & fld.Name
Next fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Function
However, it seems an odd thing to do, to be frank.

Thank you very much,
I'll try to get that going, but i don't know how it's going to go. I'm
using ADO rather then DAO

I agree. Normally i would never do this. I usually try to keep the db as
highly normalized as possible. For this specific project it would be
Much faster if everything where in the same table. Granted the real
solution would be to fix the code to use mulitple tables, but this
project is long over due. I'll save database normalization for version 2 :)

[Rick Wannall]

Use DAO just for the object model during the renaming. Has no effect on
using ADO for your other tasks.

[Rick Wannall]

If you have data that you can correlate row by row across tables, why not
use a select query that joins the three separate tables and present that as
your single-table data?

If you just must know which tables the underlying data is in (a highly
suspect proposition) you can even use aliases in the query so that your
tables stay cleaner.

Select t1.Field1 As T1_Field1, t2.Field2 As T2_Field2
From table1 As t1 inner join table2 as t2
on t1.KeyField = t2.KeyField;

Wouldn't that be a faster solution to your current problem, and also leave
your underlying data normalized, assuming that it currently is?