How to create a multivalued field using Access SQL

I need to alter a table and add a field that can hold multiple values but I need to this using Access sQL statement / VBA not the GUI

Once the constraint is created I am unable to set the "allow multiple value" field

how can this be achieved programatically ?

thank you
LVL 50
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
to go to SQL view use below


You can use SQL to do that-

ALTER TABLE  yourtableName
   ADD COLUMN yourCOLUMNName Text(30) /* Datatype(Size) ---- You can change as per your need*/
AkhaterAuthor Commented:
Pawan did you even read my question ?
Pawan KumarDatabase ExpertCommented:
Akhater - Can you explain what you need ? The question is not clear to me if the last comment is not valid.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

AkhaterAuthor Commented:
I need to create a MULTIVALUED field by sql / vba what is not clear?
John TsioumprisSoftware & Systems EngineerCommented:
Try this
Public Function createMulti()
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim prpf As DAO.Property
Set db = CurrentDb
Set tdf = db.TableDefs("table1")
With tdf
Set fld = .CreateField("MyMultiField", dbComplexText, 255)
.Fields.Append fld
 fld.Properties.Append .CreateProperty("UnicodeCompression", dbBoolean, True)
    fld.Properties.Append .CreateProperty("AllowMultipleValues", dbBoolean, -1)
    fld.Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
    fld.Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
    fld.Properties.Append .CreateProperty("RowSource", dbText, "SELECT * from YourTable;")
    fld.Properties.Append .CreateProperty("BoundColumn", dbInteger, 1)
    fld.Properties.Append .CreateProperty("ColumnCount", dbInteger, 2)
    fld.Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
    fld.Properties.Append .CreateProperty("ColumnWidths", dbText, "0;2880")
    fld.Properties.Append .CreateProperty("ListRows", dbInteger, 16)
    fld.Properties.Append .CreateProperty("ListWidth", dbText, "2880twip")
    fld.Properties.Append .CreateProperty("LimitToList", dbBoolean, True)
    fld.Properties.Append .CreateProperty("AllowValueListEdits", dbBoolean, False)
    fld.Properties.Append .CreateProperty("ShowOnlyRowSourceValues", dbBoolean, False)

End With
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AkhaterAuthor Commented:
Thank you, I still need to sort a few glitches but it works

Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I would strongly discourage multi-valued fields.  They may seem helpful, but when you start trying to write queries or reports and want to filter on that column it becomes a nightmare.

You are far better off creating the lookup table with the values, and the joining table which contains the PK from your main table, the PK from the lookup, and a Yes/No field, and then creating a subform to make your selections.  This will make it far easier to create reports and queries which rely on that "column" of data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.