1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
| //---------------------------------------------------------------------------
//! Create the SQL command to set/remove constraint on a field to be required
AnsiString CDBUpdateInterpreterAccess::RequiredFieldSQL(TADOConnection* database,
const AnsiString& tableName,
const SFieldDefinition& field) {
//creates an OLE object using the class name
Variant adox = Variant::CreateObject("ADOX.Catalog");
//Open connection
adox.OlePropertySet("ActiveConnection",database);
//Get tables collection
Variant vTables = adox.OlePropertyGet("Tables");
Variant table = vTables.OlePropertyGet("Item",tableName.c_str());
Variant vColumns = table.OlePropertyGet("Columns");
Variant column = vColumns.OlePropertyGet("Item",field.name.c_str());
// Get the "Required" state to assign to the field
bool newRequired=false;
// METHOD 1: via "Jet OLEDB:Allow Zero Length" AND "ALTER TABLE"
// http://stackoverflow.com/questions/3409561/change-columns-properties-values
{
Variant property = column.OlePropertyGet("Properties")
.OlePropertyGet("Item","Jet OLEDB:Allow Zero Length");
bool allowZero =(bool)property.OlePropertyGet("Value");
if (allowZero==newRequired) { // "allowZero" is inverse of "Required"
// Apply new value
property.OlePropertySet("Value",!newRequired);
// Also change the "NULL" / "NOT NULL" property of the field in ADO
AnsiString sql=" ALTER TABLE "+tableName
+" ALTER COLUMN "+field.name
+" "+GetFieldType(field)
+" "+(newRequired?"NOT NULL":"NULL");
TADOQuery* qry=new TADOQuery();
qry->Connection=database;
qry->SQL=sql;
qry->ExecSQL();
delete qry; qry=NULL;
}
}
// METHOD 2: via the "Attributes" of the column
// http://msdn.microsoft.com/en-us/library/ms681024%28v=VS.85%29.aspx
//
{
int attributes = (int)column.OlePropertyGet("Attributes");
bool oldRequired = !(attributes&2); //adColNullable=2 (inverse of NOT NULL)
// Compare it to the new state to set
if (oldRequired!=newRequired) {
// Different: set the new state
int newValue=(newRequired?attributes^2:attributes|2); //adColNullable=2 (inverse of "Required")
column.OlePropertySet("Attributes",newValue);
}
// Test if change was applied
attributes = (int)column.OlePropertyGet("Attributes");
}
// METHOD 3: via the "Nullable" property of the column
// http://msdn.microsoft.com/en-us/library/ms676554%28v=VS.85%29.aspx
// http://www.pcreview.co.uk/forums/re-change-field-required-property-adox-t1685138.html
{
Variant property = column.OlePropertyGet("Properties")
.OlePropertyGet("Item","Nullable");
bool nullable =(bool)property.OlePropertyGet("Value");
if (nullable==newRequired)) { // "nullable" is inverse of "Required"
try {
property.OlePropertySet("Value",!newRequired); // Also tried to replace true by "True"
} catch (...) {
int error=1;
}
}
// Test if change was applied
nullable =(bool)property.OlePropertyGet("Value");
}
// Close connection
adox = Unassigned;
//Refresh connection
RefreshConnection(database);
// No additionnal command to execute
return AnsiString();
} |
Enlever la contrainte "Required" d'un champ dans MS Access
Répondre avec citation
Envoyé par Patrick Seuret
Partager