UtterAccess Discussion Forums > Removing The 'required'/not Null Restriction Using Vba

Full Version: Removing The 'required'/not Null Restriction Using Vba

UtterAccess Discussion Forums > Microsoft® Access > Access Tables + Relationships

ddaddy

Sep 14 2011, 06:26 AM

Hi, I am trying to use the ALTER TABLE, ALTER COLUMN command in vba to set required=no of an existing table but can't figure out how to do it.

Here is the code I currently have. What needs to go inplace of ....?

Thanks

CODE

Dim cnn As New ADODB.Connection
    LinkPathFile = Mid(FindSource(), 11)

    With cnn
        .Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & LinkPathFile
        .Execute "ALTER TABLE tblRequests ALTER COLUMN RequestFee ....."
        .Close
    End With

vtd

Sep 14 2011, 10:56 AM

Try NULL in place of ....

ddaddy

Sep 14 2011, 11:09 AM

I've tried that, I get Syntax Error in field definition.

vtd

Sep 14 2011, 11:51 AM

Sorry... I was thinking of T-SQL.

I normally use DAO Field Object to alter the Required Property of the JET Table Field istead of ADO + JET DDL.

An alternative is to use ADOX. See UA Topic Code Library Form with lst/cbo RowSource pointing to FE and follow the links to the 2 MSDN articles posted by Brent for more info.

ddaddy

Sep 14 2011, 12:18 PM

Thanks for the link. I've read that and will use DAO, but could do with some sample code as the code I had I had copied from elsewhere, so it's a bit beyond me.

vtd

Sep 14 2011, 12:38 PM

Check Access VBA/DAO Help on the Required Property of a Field Object for detailed explanation and proper sample code but basically, you can do with 1 statement:

CurrentDb.TableDefs("YourTable").Fields("YourField").Required = False

ddaddy

Sep 14 2011, 01:37 PM

Had to make add some more code as i'm using a linked table, but I got it working.
Many Thanks for your help.

vtd

Sep 14 2011, 08:33 PM

You're welcome... Glad to help...

Darwood

Jan 10 2013, 07:00 AM

QUOTE (ddaddy @ Sep 14 2011, 06:37 PM)

Had to make add some more code as i'm using a linked table, but I got it working.
Many Thanks for your help.

@ddaddy Could you let me know the additional code required to get this working with linked tables please as I have the same problem. ie how to change the "Required" property of a column in a linked table.
Thanks
Darwood

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.