I can't add a key (index) on two fields using VB 
Author Message
 I can't add a key (index) on two fields using VB

I'm having trouble
adding two keys to two fields in the same table.  I'm trying to create a
database application to log bike rides.  I have a table called "Intervals"
that records the distance and time of several intervals on the same day.  I
would like to make "Date" and "Interval" fields co-key fields that
individually allow duplicates, but together provide a unique key.

When I set Primary to "True" and Unique to "False" and bring up the table in
Access, the key symbol is not present on the Date field.  When I set Primary
to "True" and Unique to "True" on both Date and Interval, I get a runtime
error saying "Primary key already added."

I can add two keys using Access97, but I can't do it using Visual Basic 5 code.
This is not a big deal, because I can get around it, but I'd like to know
why it's not working.  Please let me know if you've got any ideas.

Thanks for the help.

Here's the code segment I'm using:

    Dim MyDB As Database, MyWs As Workspace
    Dim IntervalTd As TableDef
    Dim IntervalFlds(5) As Field
    Dim IntervalIdx(2) As Index
    Dim IxFld(2) As Field
    Dim i as Integer

    Set MyWs = DBEngine.Workspaces(0)
    Set MyDB = MyWs.CreateDatabase(DBName, _
    dbLangGeneral, dbVersion30)

'Create "Intervals" table
    Set IntervalTd = MyDB.CreateTableDef("Intervals")
    Set IntervalFlds(0) = IntervalTd.CreateField("Date", dbDate)
    Set IntervalFlds(1) = IntervalTd.CreateField("Interval", dbInteger)
    Set IntervalFlds(2) = IntervalTd.CreateField("Distance", dbSingle)
    Set IntervalFlds(3) = IntervalTd.CreateField("Avg Speed", dbSingle)
    Set IntervalFlds(4) = IntervalTd.CreateField("Time", dbText)
    IntervalFlds(4).Size = 8

    For i = 0 To 4
        IntervalTd.Fields.Append IntervalFlds(i)
    Next i

***************************************************************************
THIS IS WHERE I'M HAVING TROUBLE
***************************************************************************

'Create Index on "Date" and "Interval" fields
    Set IntervalIdx(0) = IntervalTd.CreateIndex("Date")
    IntervalIdx(0).Primary = True
    IntervalIdx(0).Unique = True
    Set IxFld(0) = IntervalIdx(0).CreateField("Date")
    IntervalIdx(0).Fields.Append IxFld(0)
    IntervalTd.Indexes.Append IntervalIdx(0)
    Set IntervalIdx(1) = IntervalTd.CreateIndex("Interval")
    IntervalIdx(1).Primary = True
    IntervalIdx(1).Unique = False
    Set IxFld(1) = IntervalIdx(1).CreateField("Interval")
    IntervalIdx(1).Fields.Append IxFld(1)
    IntervalTd.Indexes.Append IntervalIdx(1)

     MyDB.TableDefs.Append IntervalTd



Mon, 12 Aug 2002 03:00:00 GMT  
 I can't add a key (index) on two fields using VB

Quote:

> I'm having trouble
> adding two keys to two fields in the same table.  I'm trying to create a
> database application to log bike rides.  I have a table called "Intervals"
> that records the distance and time of several intervals on the same day.  I
> would like to make "Date" and "Interval" fields co-key fields that
> individually allow duplicates, but together provide a unique key.
> When I set Primary to "True" and Unique to "False" and bring up the table in
> Access, the key symbol is not present on the Date field.  When I set Primary
> to "True" and Unique to "True" on both Date and Interval, I get a runtime
> error saying "Primary key already added."
> I can add two keys using Access97, but I can't do it using Visual Basic 5 code.
> This is not a big deal, because I can get around it, but I'd like to know
> why it's not working.  Please let me know if you've got any ideas.
> Thanks for the help.
> Here's the code segment I'm using:
>     Dim MyDB As Database, MyWs As Workspace
>     Dim IntervalTd As TableDef
>     Dim IntervalFlds(5) As Field
>     Dim IntervalIdx(2) As Index
>     Dim IxFld(2) As Field
>     Dim i as Integer
>     Set MyWs = DBEngine.Workspaces(0)
>     Set MyDB = MyWs.CreateDatabase(DBName, _
>     dbLangGeneral, dbVersion30)
> 'Create "Intervals" table
>     Set IntervalTd = MyDB.CreateTableDef("Intervals")
>     Set IntervalFlds(0) = IntervalTd.CreateField("Date", dbDate)
>     Set IntervalFlds(1) = IntervalTd.CreateField("Interval", dbInteger)
>     Set IntervalFlds(2) = IntervalTd.CreateField("Distance", dbSingle)
>     Set IntervalFlds(3) = IntervalTd.CreateField("Avg Speed", dbSingle)
>     Set IntervalFlds(4) = IntervalTd.CreateField("Time", dbText)
>     IntervalFlds(4).Size = 8
>     For i = 0 To 4
>         IntervalTd.Fields.Append IntervalFlds(i)
>     Next i
> ***************************************************************************
> THIS IS WHERE I'M HAVING TROUBLE
> ***************************************************************************
> 'Create Index on "Date" and "Interval" fields
>     Set IntervalIdx(0) = IntervalTd.CreateIndex("Date")
>     IntervalIdx(0).Primary = True
>     IntervalIdx(0).Unique = True
>     Set IxFld(0) = IntervalIdx(0).CreateField("Date")
>     IntervalIdx(0).Fields.Append IxFld(0)
>     IntervalTd.Indexes.Append IntervalIdx(0)
>     Set IntervalIdx(1) = IntervalTd.CreateIndex("Interval")
>     IntervalIdx(1).Primary = True
>     IntervalIdx(1).Unique = False
>     Set IxFld(1) = IntervalIdx(1).CreateField("Interval")
>     IntervalIdx(1).Fields.Append IxFld(1)
>     IntervalTd.Indexes.Append IntervalIdx(1)
>      MyDB.TableDefs.Append IntervalTd

Add both fields to one index or do something like this after creating
the table:

mydb.execute "create unique index PrimaryKey on Intervals (Date, Interval) with primary", dbfailonerror

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!



Mon, 12 Aug 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. need help opening a two field index with VB6 using access 2000

2. 'Canned' data in VB program

3. Adding Two fields to a list Box using ADO

4. Having trouble accessing single field key with two concatenated fields

5. Adding Field to Access db - need to make indexed property of field No Duplicates

6. Index of a combination of two fields

7. How to index on two fields, one descending

8. Two indexes on one field!

9. How do I Create two field index

10. Too many indexes - Wierd hidden indexes preventing index add

11. remove index from field or delete a indexed field

12. Adding index and links are 'broken'

 

 
Powered by phpBB® Forum Software