Whats the diff between key and index in adox?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MP - 12 Aug 2008 14:33 GMT

per adox help on msdn

'under keys to create foreign key
' Define the foreign key.
kyForeign.Name = "CustOrder"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "Customers"
kyForeign.Columns.Append "CustomerId"
kyForeign.Columns("CustomerId").RelatedColumn = "CustomerId"
kyForeign.UpdateRule = adRICascade

' Append the foreign key to the keys collection.
cat.Tables("Orders").Keys.Append kyForeign

'under indexes to create primary key
' Append new Primary Key index on NumField column
' to new table
idxNew.Name = "NumIndex"
idxNew.Columns.Append "NumField"
idxNew.PrimaryKey = True
idxNew.Unique = True
tblNew.Indexes.Append idxNew

do I need both keys and indexes to create a primary and foreign key?
I've been creating a primary key in a single table database with only using
key
now I'm trying to add a foreign key and not succeeding as yet...
may be because i need an index also?
thanks
amrk

Reply to this Message

Ralph - 12 Aug 2008 16:24 GMT

> per adox help on msdn
>
[quoted text clipped - 26 lines]
> thanks
> amrk

An 'index' is a non-relational database optimization. The actual mechanism
or technology employed will vary, but think of it as a separate hash table
or association table on the field of interest.
eg, You might have a People Table with indexes on last name and Dept. So you
would have something like this:
People_Table
[last_name, department]
(1) Smith, Accounting, blah, blah, blah, ...
(2) Jones, Drafting, blah, blah, blah, ...
(3) Allen, Payroll, blah, blah, blah, ...
(4) O'Neil, Payroll, blah, blah, blah, ...

You would then have two additional hidden "tables"
Last_Name_Index
Allen, 3
Jones, 2
O'Neil, 4
Smith, 1
Department_Index
Accounting, 1
Drafting, 2
Payroll, 3, 4

Indexing on a Field dramatically speeds up 'selects', but it also creates
additional work on inserts and deletes. Everytime you add a record with an
indexed field the database also has to update the index. (Again - purely
database dependent what it is or where it is stored.) It is an optimization.

Keys are relational attributes and dictate integrity and constraint rules on
tables. Primary Keys are always indexed for obvious reasons. Foreign keys
may or may not be.

hth
-ralph

Reply to this Message

MP - 12 Aug 2008 17:29 GMT

>> per adox help on msdn
>
> An 'index' is a non-relational database optimization. The actual mechanism

> Keys are relational attributes and dictate integrity and constraint rules
> on
[quoted text clipped - 3 lines]
> hth
> -ralph

well, I probably don't need indexs then, I only thought I might due to the
errors I was getting when trying to implement a foreign key in a main table,
referring back to the primary key of a "foreign table" (lookup table)

The reason I asked is I can't seem to figure out the magic mix to get a
foreign key in a main table using adox
without an index I got the error
<-2147217903> <Microsoft JET Database Engine> (Column ID is invalid.)
...when trying to append the foreign key

foreign key created thus :
(where oField is a class holding Field properties and adoxTable is my"main
table" created after the "foreign table")
Set oKey = New adox.key
With oKey
.Name = "fk_" & oField.RelatedTableName
.Type = adKeyForeign
.RelatedTable = oField.RelatedTableName
.Columns.Append oField.Name
.Columns(oField.Name).RelatedColumn = oField.RelatedColumnName
End With
ADOXtable.Keys.Append oKey

since I didin't know what a columnId was I searched on that and ended up at
Indexes...so I put one of those in :-)

with an index I get the error:
<-2147467259> <Microsoft JET Database Engine> (No unique index found for
the referenced field of the primary table.)

I don't understand the error because I did create the index as unique when
setting up the primary key of the "ForeignTable" and the primary key of the
"main table'

index created thusly:
(where oField is a class holding Field properties and adoxTable is either my
"foreign table" or "main table" depending on which was passed in to this
function to get created )
Set iDxNew = New adox.Index
iDxNew.Name = "idxPrimary"
iDxNew.Columns.Append oField.Name
iDxNew.PrimaryKey = True
iDxNew.Unique = True
ADOXtable.Indexes.Append iDxNew

clearly I have no idea what I'm doing :-) just mucking about trying
everything i can think of....
any hints? <g>
Thanks
mark

Reply to this Message

VB Forum / Database Access / August 2008

Whats the diff between key and index in adox?