ADOX.Indexes/ADOX.Key/Foreign Keys 
Author Message
 ADOX.Indexes/ADOX.Key/Foreign Keys

I am trying to generate a VB program which analyses an existing MDB and writes the VB
source code necessary for creating the MDB from scratch using ADOX. I have hit the
following problem when looping through the Indexes collection of the Table object:

When you create a relationship (i.e. under Access) between 2 tables, an Index is created ,
but the index is *not* visible to Access.  ADOX *does* see this index but the ADOX.Index
object does not have a property for distinguishing between an Index which was created in
order to establish a relationship between tables.

Example:  I visually create the following relationship under Access:

Customers.ID   ----- 1:n -------> Orders.Cust_ID   (this will create the invisible index!)

Under VB, when I loop through the oCat.Tables("Orders").Indexes collection, I will find an
index called "CustomersOrders", kindly supplied by Access behind the scenes.

Using the Indexes collection I need a way of sifting out indices which were the result of
creating a relationship between tables. How? The Index object does not have a RelatedTable
property.

TIA,
-Toby



Wed, 27 Nov 2002 03:00:00 GMT
 ADOX.Indexes/ADOX.Key/Foreign Keys

Toby,

Use the table's key collection to work with relationships.  The indexes
collection sees the fields used in relationships as indexes as well,
but they aren't local indexes like you would manage in Access Design
view.  I have something similar and get around it like this.  There may
be another way, but this way, I distinguish between "local" table
indexes and "foreign" key related indexes...

'iterate through indexes in source table
For Each idx In catSource.Tables(strTableName).Indexes
    'check idx name against table keys
    'default blnOK to true
    blnOK = True
    For Each ky In catSource.Tables(strTableName).Keys
        If idx.Name = ky.Name Then
            'if ky is foreign, then set blnOK to false
            If ky.Type = adKeyForeign Then
                blnOK = False
            End If
        End If
    Next ky

    'only add indexes that are not foreign
    If blnOK Then
        'create new index object
        Set idxDest = New ADOX.Index

        'set new index name
        idxDest.Name = idx.Name

        'iterate through columns for idx
        For Each col In idx.Columns
            'append column to new index
            idxDest.Columns.Append col.Name
        Next col

        'set new index properties
        idxDest.Clustered = idx.Clustered
        idxDest.IndexNulls = idx.IndexNulls
        idxDest.PrimaryKey = idx.PrimaryKey
        idxDest.Unique = idx.Unique

        'append new index to table
        tblDest.Indexes.Append idxDest
    End If

    'destroy index object
    If Not idxDest Is Nothing Then
        Set idxDest = Nothing
    End If
Next idx

'this will only add indexes to a table that are local to the table,
leaving the foreign indexes (which really should be managed through the
keys collection) to be added as relationships (using the keys
collection).  Hope this helps! (sorry so long winded)

John


Quote:

> I am trying to generate a VB program which analyses an existing MDB
and writes the VB
> source code necessary for creating the MDB from scratch using ADOX. I
have hit the
> following problem when looping through the Indexes collection of the
Table object:

> When you create a relationship (i.e. under Access) between 2 tables,

an Index is created ,
Quote:
> but the index is *not* visible to Access.  ADOX *does* see this index
but the ADOX.Index
> object does not have a property for distinguishing between an Index

which was created in
Quote:
> order to establish a relationship between tables.

> Example:  I visually create the following relationship under Access:

> Customers.ID   ----- 1:n -------> Orders.Cust_ID   (this will create

the invisible index!)
Quote:

> Under VB, when I loop through the oCat.Tables("Orders").Indexes

collection, I will find an
Quote:
> index called "CustomersOrders", kindly supplied by Access behind the
scenes.

> Using the Indexes collection I need a way of sifting out indices

which were the result of
Quote:
> creating a relationship between tables. How? The Index object does

not have a RelatedTable

Quote:
> property.

> TIA,
> -Toby

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 16 Dec 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. ADOX: Creating Foreign Keys

2. ADOX, SQLOLEDB and Foreign Keys

3. Retrieving Access foreign key info via ADOX

4. Copying relationships/foreign keys with ADOX

5. Help, foreign keys and index keys

6. Clustered index on primary key w/ foreign keys

7. Help, foreign keys and index keys

8. Help, Foreign keys and Index Keys

9. foreign key w/o foreign/primary key

10. Trigger to check a foreign key without using foreign key

11. Trigger to check a foreign key without using foreign key

12. Getting Primary Key using ADOX


 
Powered by phpBB® Forum Software