.CreateField Method Naming Issues

[HarmLeastOne]

This is what I have so far with some extraneous code left out and some anonymous database and variable names:

Dim dB as DAO.Database
Dim tdf as DAO.TableDef
Dim newField As DAO.Field

Dim newFieldNameVar As String

newFieldNameVar = "Variable Name"

Set dB = CurrentDb



Set tdf = dB.TableDefs("PreExistingTable")

Set newField = tdf.CreateField(newFieldNameVar)

tdf.Fields.Append newField

Set newField = Nothing
Set tdf = Nothing
Set dB = Nothing

Where the aforementioned code exists in my function is not in a loop but the newFieldNameVar is a product of a loop, and so changes regularly based on question thread irrelevant conditions.

At the point the .Fields.Append line executes I get this run-time error: Invalid field data type. '3259' I am fairly new to programming in any form or fashion, but imagine that I might be using the wrong syntax for the variable used as the Name element of the .CreateField Method. Any ideas? Am I able to use a string variable as the Name in the .CreateField Method?

Thanks for the help in advance!

[June7]

Why are you trying to implement code that modifies db structure? I suspect db is not optimally normalized.

Doesn't work with literal text either so variable is not issue.

I set the field Type argument and code works for me.

Please post code between CODE tags to retain indentation and readability and set it apart from rest of narrative.

[isladogs]

You need to specify the datatype (dbText) and field size e.g. 50

Set newField = tdf.CreateField(newFieldNameVar, dbText,50)

I've not tried it with a variable rather than a field name in quotes, but I can't see why it wouldn't work

See this link on Allen Browne's website for many more DAO functions related to setting or modifying tables/fields/indexes/relationships using code http://allenbrowne.com/func-DAO.html

@June7
The code also works on linked tables which is very useful for updating backends in split databases
I use this approach to modify backend databases when distributing frontend updates to clients without needing to visit their site
e.g. add new tables, add/modify fields, add new indexes etc

[June7]

Colin, yes I am familiar with that usage. I actually have a procedure to do that (built by someone else although I modified it); however, I still prefer to modify tables directly, which rarely happens. If this is a regular event I suspect data structure is not optimally normalized.

[isladogs]

In my case, updates are done about 4 times per year, usually to add new features or improve existing features.
Nothing to do with normalisation issues
Changes to backend tables are done to facilitate those types of change.
For Access BE, I use DAO functions. For SQL Server tables, I use a script.
As some of my large schools apps are used by multiple client schools in different locations, this approach is vital.