How to change the "Required" property of a column in Access?

Question

In an access table I have a column which has the "Required" property set to "True". I need a query which would change it to "False". I tried the following without success:

ALTER TABLE [MyTbl] ALTER COLUMN [MyCol] VARCHAR(30) NULL;

Answer 1

Jet SQL, the underlying SQL engine in Access does not allow you to modify the null property on columns. The work around for doing this is to:

1. Create a new temporary column (B) which allows nulls.
2. Copy the data from the old column (A) to the new column (B).
3. Drop the old column (A).
4. Create a new column (C) with the same name as the old column (A). Make sure the new column (C) has the correct null constraint.
5. Copy the data back from the temporary new column (B) to the newly added column (C).
6. Drop Column B.

Answer 2

I am pretty sure that the tschaibles answer given above is COMPLETELY INCORRECT. It IS true that you cannot change the required property using the query engine. But I am almost positive you can change it using VBA.

Your question is confusing...could you clarify a few things.

Do you want to permanently changed the required property. If so, you can change that property in the table designer at any time.

Do you want to change the property under certain conditions at runtime? Sort of a Change the property, Do Something, Change it back scenario. In that case you can't use the query engine to do it but I am almost positive you can do it through VBA. So it can be done. Most if not all things that can be done through the UI can be done through the VBA code.

If, you want to do this at runtime but you are merely using an Access file as the data store of a .net application (using the OleDB provider of ado.net) then you do have an issue. I would recommend you use another data provider (like vistaDB). So can you provide some details then I might can dig a little and give you a more thorough answer.

Seth

Answer 3

Try referenceing ADOX in your application (.NET ?). ADOX On the Table object you can set the attribute to Nullable (You'll have to look into the new conventions in .NET compared to VB 6 which handled this similar to VBA).

Answer 4

Jet DDL will allow you to change the "required" property from False to True. The following statement changed "required" to True:

CurrentProject.Connection.Execute _
    "ALTER TABLE People ALTER COLUMN lname TEXT(255) NOT NULL"

However, I couldn't find a way to change "required" from True to False. This statement did not cause an error message, but left the "required" property unchanged (True):

CurrentProject.Connection.Execute _
    "ALTER TABLE People ALTER COLUMN lname TEXT(255) NULL"

This syntax is only supported when using the Access database engine's ANSI-92 Query Mode. The Access object model's CurrentProject.Connection object is an ADODB.Connection object using the OLE DB provider and OLE DB always uses ANSI-92 Query Mode.