Access Alter Column to Nullable
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?
Re: Access Alter Column to Nullable
you could try...
Code:
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
Code:
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
Re: Access Alter Column to Nullable
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.
Re: Access Alter Column to Nullable
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."
Re: Access Alter Column to Nullable
Quote:
Originally Posted by
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."
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....
Re: Access Alter Column to Nullable
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