VBA Programming - HELP

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi there,
as you know in Access table, each numeric field has a
property called "Required" to indicate whether a value is
required or not in record creation. Could anyone tell me
how to change the default value (YES) to NO using ADO in
VBA for Access?

I've tried the following codes but it didn't work.

catcurr.Tables("Temp").Columns("Amount").Properties
("Required").Value =NO

Appreciate your expertise advise.
Regards,
Steve
..
 
The following example should change the Nullable property of your field.
Whether it actually works or not depends on the version of MDAC you have on
the computer, the weather on the day and many other unknowns. (ADOX really
is about that unreliable.)


Sub ModifyFieldPropAdox()
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
Dim prp As ADOX.Property

cat.ActiveConnection = CurrentProject.Connection
Set col = cat.Tables("MyTable").Columns("MyField")
col.ParentCatalog = cat
Set prp = col.Properties("Nullable")
'Read the property
Debug.Print prp.Name, prp.Value, (prp.Type = adBoolean)
'Change the property
prp.Value = Not prp.Value

'Clean up
Set prp = Nothing
Set col = Nothing
Set cat = Nothing
End Sub
 
Of course, when I am working on an ADP, with ADO, using SQL Server, I have
an MDB to do the things that are easier.

I've never worked on an ADP for which the primary data store was an MDB, and
wonder why one would do so. I do use an MDB as storage for some design time
code I run on the ADP, and for testing some new items prior to the SQL
Server DBA implementing the necessary table.

Maybe the original poster could use an MDB to make this design change -- or,
even better, open the MDB directly with Access and make that change
manually? Of course, if the back end is SQL Server or some other Server DB,
it'll have to be changed according to the server rules.

Larry Linson
Microsoft Access MVP
 
as you know in Access table, each numeric field has a
property called "Required" to indicate whether a value is
required or not in record creation. Could anyone tell me
how to change the default value (YES) to NO using ADO in
VBA for Access?
Click to expand...

strSQL = "ALTER TABLE myTable" & vbNewLine & _
"ALTER COLUMN MyColumn INTEGER NULL"

Set con = CurrentProject.Connection
con.Execute strSQL

' pick up errors here...


By the way, all columns have a NULL or NOT NULL attribute, except for BIT
columns which is (arguably) a bug... or a meringue...


Tim F
 
Back
Top