HELP PLEASE - using ADOX to add indexed field to MS Access MDB

HELP PLEASE - using ADOX to add indexed field to MS Access MDB

Post by Scotte » Sun, 26 Sep 2004 06:35:56


Attempting to programmatically (ASP) add an indexed (not key) field to an MS
Access 2002 table.

Error I get is:
"Index or primary key cannot contain a Null value"
The funky thing is that I look at the tables (this code is within a loop
that goes thru a few MDB files) and I see it works with one file, adds the
field, index, default value, etc... and then loops to next table, adds the
field, and comes up with the error before adding the index.

SEE DOWN BELOW WHERE I SAY "ERROR IS HERE"

[clip]
set objColumn = server.CreateObject("ADOX.Column")

objColumn.Name = s_new_field_name
objColumn.Type = s_new_field_type
if (s_new_field_size<>"") then objColumn.DefinedSize = s_new_field_size

set objColumn.ParentCatalog=Cat

if (left(s_new_field_name,2)="ID") then
set objIndex = server.CreateObject("ADOX.Index")
'if (s_new_field_required<>"") then objColumn.Properties("Nullable") = true
'if (s_new_field_required<>"") then
objColumn.attributes=s_new_field_required
end if

objColumn.properties("Default")=0
objColumn.properties("Nullable")=true
objTable.Columns.Append objColumn

if (left(s_new_field_name,2)="ID") then
objIndex.NAME = s_new_field_name & "_index"
objIndex.COLUMNS.APPEND(s_new_field_name) '<<<ERROR IS HERE
objIndex.PrimaryKey = false
objIndex.Unique = false
objTable.INDEXES.APPEND(objIndex)
end if

set objColumn=nothing
set objIndex=nothing


[clip]

--
Scotter
 
 
 

HELP PLEASE - using ADOX to add indexed field to MS Access MDB

Post by v-kev » Sun, 26 Sep 2004 11:38:49

Hi Scotter,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you are having problem why trying to
add an index to Access 2002 table using ADOX. If there is any
misunderstanding, please feel free to let me know.

Based on my research, an Index object has a property named IndexNulls. Its
default value is adIndexNullsDisallow, which means null value is not
allowed on index fields. You can try to set it to adIndexNullsAllow before
adding the index to the table's index collection. And I think error will
not occur again.

For more information, please check the following link:

http://www.yqcomputer.com/
adproindexnulls.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 
 
 

HELP PLEASE - using ADOX to add indexed field to MS Access MDB

Post by Scotte » Sun, 26 Sep 2004 13:55:21

Wow that did the trick!!!
Now would I'd really love to figure out (if it is possible) is if when I add
a field (column) I can determine column order.
In other words if my Access table has the following fields:
ID
ID_user
s_name_last
s_name_first
s_phone

and I want to add "ID_parent" but I want this new field to be after
"ID_user" and before "s_name_last"...
IS this possible?
 
 
 

HELP PLEASE - using ADOX to add indexed field to MS Access MDB

Post by v-kev » Sun, 26 Sep 2004 14:26:43

Hi Scotter,

As far as I know, we cannot achieve that. The columns property of a Table
object is a collection which doens't have an order. When you're working on
a table using ADO, the order depends on how you select from the database.
For example, SELECT ID, ID_user, ID_parent, s_name_first, s_name_last,
s_phone will put the ID_parent right after ID_user and put first name
before last name.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
 
 

HELP PLEASE - using ADOX to add indexed field to MS Access MDB

Post by v-kev » Wed, 29 Sep 2004 18:23:04

Hi Scotter,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."