Error adding field in Access 2016 using VBA

[Welshgasman]

Sounds like the dB is not normalised if you need to create tables each month?, anyway surround the ADHD with a single quote ' as it is text

[Welshgasman]

I'd probably have a set of empty tables and copy them each month?

[pnpez]

still getting syntax error on update statement:
CurrentDb.Execute "UPDATE ADHD" & "Set DrugType2 = 'ADHD';"

[Gicu]

Try this:

CurrentDb.Execute "UPDATE [ADHD] Set [DrugType2] = 'ADHD';"

Cheers,

[Welshgasman]

Why the concatenation when there is no need?😔
You need spaces between the words?
Other way, put it all into a string, debug print that to see what comes out, then execute that?

[pnpez]

the identifier [ADHD] could not be found

[Gicu]

Can you please post the entire code (using the code tags # in the forum)?

[pnpez]

Vlad, This is all there is (not sure how to use code tags num) :

Sub alter2()

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


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

End Sub

[Gicu]

Can you try this, I think you need to refresh the tabledef:

Sub alter2()
CurrentDb.Execute "ALTER TABLE ADHD ADD COLUMN DrugType2 TEXT(255);"
CurrentDb.Tabledefs.Refresh
CurrentDb.Execute "Update ADHD Set DrugType2 = 'ADHD';"
End Sub

[pnpez]

Solved!!!!!!!!!!!
Thanks again guys.

[Minty]

I'll just re-itereate what others have said here, if you are adding a column every month you aren't using this as a database.
This is spreadsheet thinking.

Have a read up on Normalisation.
Preferably before you come back asking why your queries ae so difficult to get working.

[NoellaG]

If your friend needs to add columns to a table on a regular base, it seems to me there is something wrong with his database design.