Error adding field in Access 2016 using VBA

[pnpez]

Hello, I am trying to run the code below to add a field to an existing table below:

Public Sub addColumn()
Dim strField As String
Dim curDatabase As Object


Dim ADHD As Object
Dim fldNew As Object

Set curDatabase = CurrentDb
Set ADHD = curDatabase.TableDefs("ADHD")

strField = "DrugType2"

Set fldNew = ADHD.CreateField(strField)
ADHD.Fields.Append fldNew
End Sub

I get the run-time error INVALID FIELD DATA TYPE on line "ADHD.FIELDS.APPEND FLDNEW"
I got this code on line so could be outdated.
Any help? Thanks.

[Welshgasman]

Well I'd expect you'd have to say what datatype it is, which appears to be what the error message is indicating?

[pnpez]

I am new to this so wondering where I would put that code. It would be a TEXT type. Thanks

[Welshgasman]

I have never done anything like this, except manually? Why can't you do it that way?. It is not something you are going to do every day, surely?, so no real need for a code solution, especially when you do not know how to do it?

I'd expect it to be a property of the object, so perhaps FldNew.Datatype ?

Put a break point in on the creation of FldNew then look in the locals window for it's properties.

Edit: Looks like it would be the Type property, but you need to select the correct one. EG ID (autonumber) in one of my tables is 4, so look in an existing table like ADHD for a text field and see what number that is.?

FldNew.Type = 4 (if you were making an autonumber field)

Edit again: Text appears to be 10

[Welshgasman]

Yep, fldnew.type = 10 will set it to text, so put before the append line.
You will probably need to specify a length the same way and any other restrictions?

[Gicu]

Can you please try this:

 CurrentDb.Execute "ALTER TABLE ADHD ADD COLUMN DrugType2 TEXT(255);" 'add field

Cheers,

[pnpez]

Thanks to both of you. In both situations I get Run-time error 3211: Database engine could not lock table 'ADHD' because it's already in use by another person or process.

[Welshgasman]

Yes, it needs to be closed. You cannot even do it manually if the table is in use?

[pnpez]

Vlad, file was open, sorry. I did run your code but it produced no results.

[Welshgasman]

Vlad, file was open, sorry. I did run your code but it produced no results.

Works for me.?
Did you check the table?

[pnpez]

I'm getting "cannot define field more than once" now on your code Welsh

[Welshgasman]

Well that would be correct? you cannot have two fieldnames the same in the same table :-(
That seems ti infer that Vlad's code worked, which I would expect, so just change the name of the field to DrugType3

[Gicu]

Can you show us the full code you are running?

[Welshgasman]

I have to ask

Why are you attempting such advanced topics, (well to me at least) when you appear to be a novice with Access.?

I'm all for learning, but not by jumping in the deep end without learning to swim first?

You have not said why you can't just do this manually.?

I can see a use for this if you wanted to build the tables from scratch each time, but for day to day use?

[pnpez]

Thanks for asking...I'm building this to help a colleague who is doing this for several tables, so it's a pain completing each month. No better way to learn on my own.
So both methods worked, YOU GUYS ROCK!
Last thing is to populate field, this code says invalid syntax:

CurrentDb.Execute "Update ADHD" & "Set DrugType2 = ADHD;"