SQL Alter Table - Remove NOT NULL

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
I have a table with several existing fields that we have required to have values in the past. Now, we want to remove that requirement. (In the mdb file, the fields "Required" property is True.) I want to make that property false by running VBA from our Excel (2007) front end. In the past, I've used ALTER TABLE (SQL) to make a field required but can't seem to do the reverse for some reason.

These fields are not part of a key. I don't see any related constraint, either. They are not indexed. They are text fields. As a test I've added a new field to the table (making it required using NOT NULL) but then could not remove the requirement using ALTER TABLE.

Set rsdata = New ADODB.Recordset

szSQL = "ALTER TABLE [strTableName] ALTER COLUMN [strFieldName] Text(50) NULL"

rsdata.Open szSQL, gDBconnect, adOpenStatic, adLockOptimistic, adCmdText

I'm finding only posts about making a field required, not removing the requirement. I can manually open mdb file and set the fields Required = false but I need to do this with code for multiple users/files. That is the problem.

Can someone please help with this?
Thanks in advance.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Are you sure the SQL is being executed?

Perhaps you could try using Execute instead of Open?
 

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
Yes, I think it's executing because I can run ALTER to change an existing field's size (say from 55 to 50, as shown in example code) successfully but the "Required" property won't convert to false despite having "Null" in SQL.

And as mentioned, I can also test by running ALTER successfully to add a temporary test field to this table. I can add it as required or I can add it not required...all just fine. But then I can't get the ALTER to change that field's "Required" property from true to false, just like I can't do this for the existing fields.

Can you actually change a field's Required property from true to false on your system?
 
Last edited:

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
I also tried using EXECUTE, as suggested above, but still no change (and no error).

As before, the ALTER statement changes the field length but *not* the required property to false.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Paul

I think this might not be possible check this DDL Code Examples.
 

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
Thanks for the link. So I guess you're referring to the page's text. It just seems odd that I can set the Required property to be true using NOT NULL but then can't set that property to be false. Well, thanks for the reply.


"While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero-length strings. You can create a Yes/No field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a Date/Time field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all."
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Perhaps it's possible using some other method.

Or you could take drastic measures and add a new field with identical properties, except the required bit, use an UPDATE query to populate that and delete
the original field.

That should work but like I said perhaps a bit drastic.:)
 

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
Here's the solution that worked for me (running VBA from module subprocedure in Excel).

(IMPORTANT: This requires adding to Excel file reference: "Microsoft DAO x Object Library")

Dim tdf1 As DAO.TableDef
Dim fld As DAO.Field
Dim acc As Object
Dim db1 As Object
'__________________________________________________________________

Set acc = CreateObject("Access.Application")

acc.visible = False

'(Password parameter is optional)
Set db1 = acc.DBEngine.OpenDatabase(gMDBName, False, False, ";PWD=" & gStdPassword)

acc.OpenCurrentDatabase gMDBName

Set tdf1 = db1.TableDefs("xxxxx")

Set fld = tdf1.Fields("ffffff")
fld.Required = False

db1.Close

Set db1 = Nothing
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
That was one of the methods I was thinking of, but my DAO's a bit rusty.

I actually tried doing something similar with ADOX catalog but it didn't work of course.:)
 

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
Thanks again for your input. I'm never eager to add another reference to my Excel project file because it could prove problematic on some user's machines. So I hesitated to do this. But it seems the only way.

I hope this post will prove useful to others also attempting this task.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,947
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top