I Can't Remove NOT NULL (Reqired) Constraint Using SQL (1 Viewer)

[ADIGA88]

Hi Guys,

I am trying to remove the required property from a field using SQL, but can't find anything on MS Docs, another site suggests using the ALTER Column but I can't seem to make it work.
I tried:

ALTER TABLE tblVendorsInvoicesOracle ALTER COLUMN strPONo TEXT(20) Null

Required Still True

Anyone has an idea how to make work.
Thanks

 

[June7]

This is a linked Oracle table?
I don't think can do this with linked table, only local table. Maybe a pass-through query could act on the backend table.

However, with Access tables, some properties cannot be altered with SQL - Required appears to be one that cannot https://stackoverflow.com/questions...e-the-required-property-of-a-column-in-access. Perhaps that is the case with Oracle.

Could you provide link to that other site?

 

[arnelgp]

If you cant do it in sql, do it in design.
Add another field same type as your PO, same length and set the Required to No.
Copy the po to this new field using update query.
Delete the old PO field and rename the new one.

 

[ADIGA88]

This is a linked Oracle table?

No, it's a normal Access table.

it seems as @jungli and @arnelgp suggested, creating a new column instead and moving the data to it instead to modify it.

Thanks for the advice.

 

[Pat Hartman]

Any reason you are trying to do this with DDL rather than through the interface? The alternative methods are pretty awkward if you need to automate this.

 

[ADIGA88]

Any reason you are trying to do this with DDL rather than through the interface? The alternative methods are pretty awkward if you need to automate this.

Because the DB is on a server using Access RunTime, any update to the DB I am trying to use SQL and DAO when I can (I do not copy the DB to another PC to not interrupt the users).

 

[Pat Hartman]

It is 100% wrong to update table design in the BE while the users might be trying to update data. This is a very bad plan. Do your BE maintenance off hours no matter how inconvenient it is for you.

 

[ADIGA88]

PS. Actually, also I found it neater to deploy changes this way because I am making a lot of changes in the test environment DB and it's easier to run the script than remembering what I changed when deploying.

 

[ADIGA88]

It is 100% wrong to update table design in the BE while the users might be trying to update data. This is a very bad plan. Do your BE maintenance off hours no matter how inconvenient it is for you.

actually, I am kicking the user out when running the script as a safety precaution (it's not a big deal to stop users for 30 sec but manually it takes more than an hour to update).
but for the sake of the argument, I am deploying the changes in one big transaction. should this lock all that tables while the script is running?

 

[Pat Hartman]

I told you it was a bad idea. Take your chances if you want. You ALWAYS back up the database before and after changes. I find it hard to believe that you can't actually plan structural updates and need to do them on the fly.

 

[GPGeorge]

Do you not have two versions of this relational database application? One for production and one for Development? If not, create yourself a development environment now, before running into any further difficulties with live production that run the risk of taking down the entire production relational database application in real-time. Make your changes in the development version. Test and validate your changes in the development version. Wait until off-work hours and update the production environment after making a back up and before users return to work. Then you can safely implement the changes on the production database. When users return to work, they can pick up on the new production version without being interrupted and with much lower possibility that something you did--deliberately or inadvertently--broke the system.

Also, make sure your employer knows and approves of your strategy for implementing improvements in a logical manner.