Access Alter Column to Nullable

[Shaggy Hiker]

Altering a column to not nullable appears pretty straightforward. Altering a column that is required to be not nullable doesn't seem to be possible. There's a lengthy way around that, of course, since I could drop the column then re-add it (or include more steps to add a temporary column, copy values over, then drop the original, rename the new, and go from there.

That doesn't seem right. So, is there a means to make a non-nullable column nullable in an Access database using a query?

[ChrisE]

you could try...

ssql = " Alter Table yourTable Alter Column [myColumn]TEXT Null;"

'the Field myColumn is TEXT

EDIT: just tried that, and sorry that will not work
back to the drawing board

EDIT2: this will work, I used DAO in .NET

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim AccessDatabaseEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine()
        Dim AccessDatabase As Microsoft.Office.Interop.Access.Dao.Database
        Try
            AccessDatabase = AccessDatabaseEngine.OpenDatabase("E:\Adressen.mdb")
            AccessDatabase.TableDefs("YourTable").Fields("yourColumn").Required = False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            AccessDatabase.Close()
        End Try
    End Sub

[Shaggy Hiker]

I'm not going DAO for this.

In fact, I'm not doing anything, because after a bit of study, I realized that the field was never used in any way and no longer has any relevance, so I dropped it entirely. That solved the problem, but did not answer the question, so I'm leaving it open. I did try your first suggestion before I posted this, as it seemed like it should work. As you noted, it did not. I also found the DAO solution, but that's massive overkill to haul DAO out of it's grave for something like this.

[gigemboy]

Yeah... they even say this on their page : https://learn.microsoft.com/en-us/of...oft-access-sql

"The Microsoft Access database engine does not support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Access databases. Use the DAO Create methods instead."

[Zvoni]

Yeah... they even say this on their page : https://learn.microsoft.com/en-us/of...oft-access-sql

"The Microsoft Access database engine does not support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Access databases. Use the DAO Create methods instead."

What's that got to do with the original issue?
MS Access-ENGINE doesn't support ALTER TABLE for NON-Access-Databases (e.g. if you have linked NON-Access-Tables)

Shaggy, have you thought about it that a "NOT NULL" actually is a CONSTRAINT (like UNIQUE)?
Remember this: https://www.vbforums.com/showthread....ove-Constraint

EDIT: Found this: https://stackoverflow.com/questions/...lumn-in-access
Seems you're out of luck.
Open that Access-DB in Access itself, go to the designer, and change the Required Prop for the Column there.
It is as it is....

[gigemboy]

Probably nothing now that I re-read. But Shaggy is still prob out of luck with this one: https://stackoverflow.com/questions/...lumn-in-access