Alter Table allow null 1

[elibb]

hi, i have an application in vb6.' and im using Access for my database.
im trying to make a program that modifies my tables on Access, for that i am using the ALTER command of SQL, but i dont manage to turn on the flag of "Allow null Strings"
(i have it in spanish, i dont know the exact name of it, but if you add a field, and select Text as datatype, its the third option on the properties starting from the bottom)
i tried:

"ALTER TABLE people ADD Name text(100) not null"

but it doesnt put that option in "yes"
does anybody know how to do it? i really need it..

thank you very much

Eli

 

[elibb]

sorry i mean the "Allow zero length" property to yes..
i hope someone can help me..
thank you very much

Eli

 

[KenReay]

Hi

By saying:

"ALTER TABLE people ADD Name text(100) not null"


you are saying Null is NOT allowed, presumably if you say

"ALTER TABLE people ADD Name text(100)"

then Null WILL be allowed



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jsteph]

You can use dao, adding the ms dao lib to the vb project :

Dim db As DAO.Database, td As DAO.TableDef, strDb As String
strDb = "Some.mdb"
Set db = DBEngine.OpenDatabase(strDb)
Set td = db.TableDefs("People")
td.Fields("[name]").Properties("AllowZeroLength") = true

--jsteph

 

[elibb]

thank you very much, i solved that problem using ADOX, but now i have another question, is there a way to insert the field in the middle of other fields, like lets say i have a table like this:

id int
Name Text
Phone Text

and i want to insert a field called Address, but i want it to be between the Name and the phone, so it looks like this:

id int
Name Text
Address Text
Phone Text

is it possible??

thank you very much

Eli

 

[KenReay]

Hi

The short answer is yes, you can adjust the 'position' of a column within the table by setting the .OrdinalPosition property.

I am not sure if the property controls the Physical position of teh column within the data, or simply the logical or presentation position.

I have in the past been 'reprimanded' in this group for mentioning this property, but it has worked for me, and I can well see the reason to group columns within a table design into some form of logical order, so for what it is worth here is teh help test on it:

Sets or returns the relative position of a Field object within a Fields collection. For an object not yet appended to the Fields collection, this property is read/write.

Settings and Return Values

The setting or return value is an Integer that specifies the numeric order of fields. The default is 0.

Remarks

The availability of the OrdinalPosition property depends on the object that contains the Fields collection, as shown in the following table.

If the Fields collection belongs to a
Then OrdinalPosition is
Index object Not supported
QueryDef object Read-only
Recordset object Read-only
Relation object Not supported
TableDef object Read/write
Generally, the ordinal position of an object that you append to a collection depends on the order in which you append the object. The first appended object is in the first position (0), the second appended object is in the second position (1), and so on. The last appended object is in ordinal position count – 1, where count is the number of objects in the collection as specified by the Count property setting.
You can use the OrdinalPosition property to specify an ordinal position for new Field objects that differs from the order in which you append those objects to a collection. This enables you to specify a field order for your tables, queries, and recordsets when you use them in an application. For example, the order in which fields are returned in a SELECT * query is determined by the current OrdinalPosition property values.

You can permanently reset the order in which fields are returned in recordsets by setting the OrdinalPosition property to any positive integer.
Two or more Field objects in the same collection can have the same OrdinalPosition property value, in which case they will be ordered alphabetically. For example, if you have a field named Age set to 4 and you set a second field named Weight to 4, Weight is returned after Age.
You can specify a number that is greater than the number of fields minus 1. The field will be returned in an order relative to the largest number. For example, if you set a field's OrdinalPosition property to 20 (and there are only 5 fields) and you've set the OrdinalPosition property for two other fields to 10 and 30, respectively, the field set to 20 is returned between the fields set to 10 and 30.

Note Even if the Fields collection of a TableDef has not been refreshed, the field order in a Recordset opened from the TableDef will reflect the OrdinalPosition data of the TableDef object. A table-type Recordset will have the same OrdinalPosition data as the underlying table, but any other type of Recordset will have new OrdinalPosition data (starting with 0) that follow the order determined by the OrdinalPosition data of the TableDef.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK