UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Removing The 'required'/not Null Restriction Using Vba, Office 2003    
 
   
ddaddy
post Sep 14 2011, 06:26 AM
Post #1

UtterAccess Addict
Posts: 293
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
Go to the top of the page
 
+
vtd
post Sep 14 2011, 10:56 AM
Post #2

Retired Moderator
Posts: 19,667
Try NULL in place of ....
Go to the top of the page
 
+
ddaddy
post Sep 14 2011, 11:09 AM
Post #3

UtterAccess Addict
Posts: 293
I've tried that, I get Syntax Error in field definition.
Go to the top of the page
 
+
vtd
post Sep 14 2011, 11:51 AM
Post #4

Retired Moderator
Posts: 19,667
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.
Go to the top of the page
 
+
ddaddy
post Sep 14 2011, 12:18 PM
Post #5

UtterAccess Addict
Posts: 293
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.
Go to the top of the page
 
+
vtd
post Sep 14 2011, 12:38 PM
Post #6

Retired Moderator
Posts: 19,667
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
Go to the top of the page
 
+
ddaddy
post Sep 14 2011, 01:37 PM
Post #7

UtterAccess Addict
Posts: 293
Had to make add some more code as i'm using a linked table, but I got it working.
Many Thanks for your help.
Go to the top of the page
 
+
vtd
post Sep 14 2011, 08:33 PM
Post #8

Retired Moderator
Posts: 19,667
You're welcome... Glad to help...
Go to the top of the page
 
+
Darwood
post Jan 10 2013, 07:00 AM
Post #9

UtterAccess Enthusiast
Posts: 67
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
Go to the top of the page
 
+
« Next Oldest · Access Tables + Relationships · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 07:36 PM