How to change a field to allow null using jet sql?

[tatin02]

Hi Everyone,

I have a table called table1 and two text fields col1, col2. col1's nullable property is true, and col2's is false. I have succeeded in using jet sql:

ALTER TABLE table1 ALTER COLUMN col1 text(50) NOT NULL

to change col1's nullable property from true to false. However, when I tried to use

ALTER TABLE table1 ALTER COLUMN col2 text(50) NULL

to change col2's nullable property from false to true, it doesn't work as I expect. col2's nullable property remains false after executing the above sql statement. Can anyone suggest a solution to this?

Thanks,
Tatin

 

[Remou]

You say using SQL, is that an absolute requirement?

 

[Slippenos]

ALTER TABLE table1 ALTER COLUMN col2 text(50) [red]IS[/red] NULL

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[Remou]

Hi Slippenos
What version of Access is that? I am using 2000, and IS NULL does not work for me.

 

[Remou]

From what little I have read, it seems that to get rid of NOT NULL, you need MODIFY, which Access does not accept.

 

[PHV]

Syntax error in ac2003 too ...

 

[Slippenos]

I meant 'ADD', not 'ALTER'. SORRY.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[PHV]

If SQL is the only way to go:
ALTER TABLE table1 ADD COLUMN tmpcol1 text(50)
UPDATE table1 SET tmpcol1 = col1
ALTER TABLE table1 DROP COLUMN col1
ALTER TABLE table1 ADD COLUMN col1 text(50)
UPDATE table1 SET col1 = tmpcol1
ALTER TABLE table1 DROP COLUMN tmpcol1

Hopefully someone has a better solution to offer ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tatin02]

Thanks for the help from everyone. I have to do it by jet sql since changing the ADOX property doesn't work: it gives a "Multi-step OLE DB operation error".

PHV's solution is OK. But I would like to reduce the work as much as possible while keeping everything else unchanged, e.g., the order of fields. Using PHV's method, I have to do the same thing for all fields following field col2 (say, if table1 has fields: col1, col2, col3, ...) just to keep the order of the fields.

I still don't understand why
ALTER TABLE table1 ALTER COLUMN col2 text(50) NULL
or simply
ALTER TABLE table1 ALTER COLUMN col2 text(50)
doesn't work. Is this a bug or the gramma of the above statements is incorrect?

I tried
ALTER TABLE table1 ALTER COLUMN col1 text(50) WITH COMPRESSION
to set UNICODE Compression true for col1, and then used
ALTER TABLE table1 ALTER COLUMN col1 text(50)
to set UNICODE Compression false. It works perfectly.

Can anyone answer my question above and give me a better solution?

Thanks a lot,
Tatin

 

[PHV]

e.g., the order of fields
BTW, the order of the fields in a table is irrelevant in relational database ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

Do you need to do this in code or can you go into table design and set the "Required" property from Yes to No? This will set the nullable property to True.

 

[tatin02]

cmmrfrds:
Yes, I have to do this in code.

 

[cmmrfrds]

Here is a link that talks about using Engine Type = 5 when opening the connection to the database for ADOX. It might be worth a try.

"Jet OLEDB:Engine Type=5"

http://www.xtremevbtalk.com/showthread.php?t=40596

 

[tatin02]

cmmrfrds:
I tried your suggestion to use "Jet OLEDB:Engine Type=5" for ADOX, but still got the same "Multi-step OLE DB operation generated errors".