Can't create index in new table 
Author Message
 Can't create index in new table

I'm trying to create a new table in another DB. The table is created with
all the fields, but the index does not appear when I open that access db.
Here is the code:
Private Sub MakeRetailTable()
    Dim db As Database
    Dim tbl As TableDef
    Dim idx As Index
    Dim found As Boolean

    found = False
    Set db = DBEngine.Workspaces(0).OpenDatabase(Forms![Main].SharePath &
"FCData.mdb")
    For Each tbl In db.TableDefs    'Look for Retail Sales Table
        Debug.Print tbl.Name
        If tbl.Name = "RetailSales" Then found = True
    Next
    If Not found Then   'Build Retail Sales Table
        'Create Table
        Set tbl = db.CreateTableDef("RetailSales")
        'Create Fields
        With tbl
            .Fields.Append .CreateField("ItemNo", dbLong)
            .Fields.Append .CreateField("Date", dbDate)
            .Fields.Append .CreateField("ItemName", dbText, 30)
            .Fields.Append .CreateField("Acct", dbLong)
            .Fields.Append .CreateField("Order", dbLong)
            .Fields.Append .CreateField("CasePack", dbText, 12)
            .Fields.Append .CreateField("InvUnit", dbText, 12)
            .Fields.Append .CreateField("InvNo", dbDouble)
            .Fields.Append .CreateField("CaseCost", dbDouble)
            .Fields.Append .CreateField("Sales", dbInteger)
        End With
        'Create Index
        Set idx = tbl.CreateIndex("PrimaryKey")
        With idx
            .Fields.Append .CreateField("ItemNo", dbLong)
            .Fields.Append .CreateField("Date", dbDate)
        End With
        tbl.Indexes.Append idx
        db.TableDefs.Append tbl
    End If
    db.Close: Set db = Nothing
End Sub



Thu, 25 Jul 2002 03:00:00 GMT  
 Can't create index in new table
My guess is that the index appears in the Indexes dialog, but doesn't
show up as the primary key of the table. To fix that, add the
following line, before you append the index:
idx.Primary = True
Just naming the index "PrimaryKey" isn't enough, and in fact you could
give it any legal name.

 -- Andy

Quote:

>I'm trying to create a new table in another DB. The table is created with
>all the fields, but the index does not appear when I open that access db.
>Here is the code:
>Private Sub MakeRetailTable()
>    Dim db As Database
>    Dim tbl As TableDef
>    Dim idx As Index
>    Dim found As Boolean

>    found = False
>    Set db = DBEngine.Workspaces(0).OpenDatabase(Forms![Main].SharePath &
>"FCData.mdb")
>    For Each tbl In db.TableDefs    'Look for Retail Sales Table
>        Debug.Print tbl.Name
>        If tbl.Name = "RetailSales" Then found = True
>    Next
>    If Not found Then   'Build Retail Sales Table
>        'Create Table
>        Set tbl = db.CreateTableDef("RetailSales")
>        'Create Fields
>        With tbl
>            .Fields.Append .CreateField("ItemNo", dbLong)
>            .Fields.Append .CreateField("Date", dbDate)
>            .Fields.Append .CreateField("ItemName", dbText, 30)
>            .Fields.Append .CreateField("Acct", dbLong)
>            .Fields.Append .CreateField("Order", dbLong)
>            .Fields.Append .CreateField("CasePack", dbText, 12)
>            .Fields.Append .CreateField("InvUnit", dbText, 12)
>            .Fields.Append .CreateField("InvNo", dbDouble)
>            .Fields.Append .CreateField("CaseCost", dbDouble)
>            .Fields.Append .CreateField("Sales", dbInteger)
>        End With
>        'Create Index
>        Set idx = tbl.CreateIndex("PrimaryKey")
>        With idx
>            .Fields.Append .CreateField("ItemNo", dbLong)
>            .Fields.Append .CreateField("Date", dbDate)
>        End With
>        tbl.Indexes.Append idx
>        db.TableDefs.Append tbl
>    End If
>    db.Close: Set db = Nothing
>End Sub



Fri, 26 Jul 2002 03:00:00 GMT  
 Can't create index in new table
THANKS! That worked, you were right.


Fri, 26 Jul 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Create new index on Foxpro table?

2. Create new index on Foxpro table?

3. Create new index on Foxpro table?

4. How to get table's index in the tables collection

5. 'Canned' data in VB program

6. Create new fields in a table based off of fields in another table

7. Create new field in existing table exactly like field in second table

8. Copying table definition to create new table

9. DELETING TABLE, CREATING NEW TABLE

10. Index for new table

11. Setting an Index on a new table

12. How do I create a canned form?

 

 
Powered by phpBB® Forum Software