Concatenate string to Field Names

[Jason1971]

Hi,
I have a table named "Call". I wish to add (concatenate) the text "Call_" to the front of every field name in this table. Can anyone suggest the VBA code?
Thanks.

 

[CameronM]

Use the TableDef and Field Objects;

Public Function EditTable()
Dim dbs As Database
Dim tbl As TableDef
Dim fld As Field
 
'set a connection to your database - which could be as simple as the 
'name of the local db, or you might be using a connection string
Set dbs = DBEngine.OpenDatabase(...connection to your database...)
 
'open the table called Call
Set tbl = dbs.TableDefs("Call")
 
'loop through all the fields and append the text Call_
For Each fld In tbl.Fields
    fld.Name = "Call_" & fld.Name
Next
End Function

 

[Mike375]

Cameron,

That is amazing. I just tried it.

Public Function EditTable()
Dim dbs As Database
Dim tbl As TableDef
Dim fld As field

'set a connection to your database - which could be as simple as the
'name of the local db, or you might be using a connection string
Set dbs = DBEngine.OpenDatabase("c:\ViperResides\ViperCopy.mdb")

'open the table called Call
Set tbl = dbs.TableDefs("SumPrem")

'loop through all the fields and append the text Call_
For Each fld In tbl.Fields
fld.Name = "Call_" & fld.Name
Next
End Function

 

[Mike375]

Look how the forum has put a space between m and db. This was happening to another member yesterdat but with Date. He was getting Da te() and on the 3rd occurrence of the date, it was IIF stuff

I will post it on its own and see what happens

("c:\ViperResides\ViperCopy.mdb")

Edit. Did not do when only the one line.

 

[Jason1971]

(I'm a newbie to VBA). I'm trying the following but it's not working. The error I get is Runtime error '3420' object invalid or no longer set.

Private Sub Command2_Click()

Dim tbl As TableDef
Dim fld As Field

'open the table called Call
Set tbl = CurrentDb.TableDefs("Call pts")

'loop through all the fields and append the text Call_
For Each fld In tbl.Fields <---- Problem here
fld.Name = "Call_" & fld.Name
Next

End Sub

 

[Jason1971]

CameronM, thank you for your original (1st) post...works like a charm. Your a legend, Thanks once again.
Jason.
Melbourne, Australia.

 

[CameronM]

Hi Jason,

Glad I could help. I initially tried the CurrentDB option and got the error you mentioned, which I later realised was because CurrentDB creates another instance of the currently open database. I expect that most changes to the schema would therefore need to use the DBEngine option to avoid this error.

From the Access help file :

The CurrentDb method creates another instance of the current database, while the DBEngine(0)(0) syntax refers to the open copy of the current database.