Add multiple fields using vba

[JohnPapa]

I use the code below to add a Text field to a table. I do NOT use a sub or function to store the code

[COLOR=red][FONT=Courier New]Dim [/FONT][/COLOR][COLOR=navy][FONT=Courier New]Db [/FONT][/COLOR][COLOR=red][FONT=Courier New]As [/FONT][/COLOR][COLOR=navy][FONT=Courier New]DAO.Database 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]Dim [/FONT][/COLOR][COLOR=navy][FONT=Courier New]fld [/FONT][/COLOR][COLOR=red][FONT=Courier New]As [/FONT][/COLOR][COLOR=navy][FONT=Courier New]DAO.Field 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]Dim [/FONT][/COLOR][COLOR=navy][FONT=Courier New]tdf [/FONT][/COLOR][COLOR=red][FONT=Courier New]As [/FONT][/COLOR][COLOR=navy][FONT=Courier New]DAO.TableDef 

[/FONT][/COLOR][COLOR=red][FONT=Courier New]Set [/FONT][/COLOR][COLOR=navy][FONT=Courier New]Db = Application.CurrentDb 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]Set [/FONT][/COLOR][COLOR=navy][FONT=Courier New]tdf = Db.TableDefs(strTableName) 

[/FONT][/COLOR][COLOR=green][FONT=Courier New]' First create a field with data type = Text 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]Set [/FONT][/COLOR][COLOR=navy][FONT=Courier New]fld = tdf.CreateField(strFieldName, dbText) [/FONT][/COLOR][COLOR=green][FONT=Courier New]

' Append the field 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]With [/FONT][/COLOR][COLOR=navy][FONT=Courier New]tdf.Fields 
.Append fld 
.Refresh 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]End With [/FONT][/COLOR][COLOR=red][FONT=Courier New]
[/FONT][/COLOR][COLOR=blue][FONT=Courier New]
ExitHere: 
[/FONT][/COLOR][COLOR=red][FONT=Courier New]Set [/FONT][/COLOR][COLOR=navy][FONT=Courier New]fld = [/FONT][/COLOR][COLOR=red][FONT=Courier New]Nothing 
Set [/FONT][/COLOR][COLOR=navy][FONT=Courier New]tdf = [/FONT][/COLOR][COLOR=red][FONT=Courier New]Nothing 
Set [/FONT][/COLOR][COLOR=navy][FONT=Courier New]Db = [/FONT][/COLOR][COLOR=red][FONT=Courier New]Nothing[/FONT][/COLOR]

It works the first time I use the code, but does not work the second time I include the code to add a second text field to the same table. The instances of the code are included in the the On Open event of a form and are run as part of an update process, depending on which version the user has. I do not get any messages. The second Text field is not added to the table. In the second instance of the code I use different names for the Db2, tdf2, fld2.

The second Text field is added if I use an Application.Quit in between the two field addition codes.

Any ideas?
John

 

[Galaxiom]

I would be more concerned that you are using code to add fields to a table. This often suggest you need to change the data structure.

 

[Khalid_Afridi]

It works the first time I use the code, but does not work the second time...
Any ideas?
John

Hi,
What is the...

Set tdf = Db.TableDefs(strTableName)

' First create a field with data type = Text
Set fld = tdf.CreateField(strFieldName, dbText)

strTableName and strFieldName in your code?

The field will not be added with same name in the table...

Hope this help.

 

[JohnPapa]

Khalid,
strTableName = name of the table in this case "tblRootCanalTreatment" and is the same in both additions since we are adding to the same table
strFieldName = names of the fields to be added "txtSMS" for the first addition and "txtReference Point" for the second addition.

GalaxiomAtHome,
The reason for needing to add fields is to support new functionality. The software was designed and has been running for the past 14 years and is deployed at the customer's site. Is there any other way to add the necessary fields?

 

[Galaxiom]

You could try the SQL ALTER TABLE command.

Also check out BobLarson's back end updater. It might do what you want in a different way.

 

[JohnPapa]

I had tried the ALTER TABLE. Same problem.

Most likely the table is not returned to its original (editable) state after the first addition and the Application.Quit takes care of this.

I will investigate a bit further and look into Bob's examples.

Thanks,
John

 

[Galaxiom]

I just pasted your code into a module in Access 2007 and ran it on an mdb (2002/2003) database . It works perfectly with repeated use only changing the field name on each run. Nothng else required.

Seems you have some problem with that table. Maybe something to do with it being originally designed in an earlier version? Fourteen years ago has to make it a very early version of Access.

Maybe an alternative would be construct a new table based on the old tabledef, add the new fields, append the data and replace the table.

 

[JohnPapa]

Sounds like a possible solution to the problem. The software (dental www.VisualDentist.vom) was originally designed with A97 and has been working with A03 for the past 5-6 years.
Thanks. I will try and inform of the result,
John