Request help creating a multi-field table index 
Author Message
 Request help creating a multi-field table index

(VB 4.0 Pro, Windows 3.11, default Jet 2.5)

I don't understand how to write code, either DAO or SQL, for creating a
table index composed of more than 1 field; i.e.,

Index = (ASC)Field1,(DEC)Field2,(ASC)Field3

I want to make this index the primary index (each record will have a
unique field value combination).

I appreciate any help.  (I am learning database programming slowly.)

Dan Gewirtz



Mon, 28 Sep 1998 03:00:00 GMT  
 Request help creating a multi-field table index
The following code should work for you - I hope.

Sub CreateDb ()

'--------------------------------------
'db Source - Create Database Subroutine
'--------------------------------------

    Dim db as database

    Set db = CreateDatabase("c:\temp\jp.mdb","",0)

    ' Create TABLE clients
    Dim Table1 As New TableDef
    Table1.Name = "clients"
    Table1.Attributes = 0

    ' Create Field clientid
    Dim Field1 As New Field
    Field1.Name = "clientid"
    Field1.Type = 4
    Field1.Size = 4
    Field1.Attributes = 49
    Table1.Fields.Append Field1

    ' Create Field date
    Dim Field2 As New Field
    Field2.Name = "date"
    Field2.Type = 8
    Field2.Size = 8
    Field2.Attributes = 33
    Table1.Fields.Append Field2

    ' Create Field PrimaryKey
    Dim Index1 As New Index
    Index1.Name = "PrimaryKey"
    Index1.Unique = TRUE
    Index1.Primary = TRUE
    Index1.Fields = "+clientid;+date"
    Table1.Indexes.Append Index1

    DB.TableDefs.Append Table1

    db.close

End Sub

Hope this helps,

JP

Quote:

>(VB 4.0 Pro, Windows 3.11, default Jet 2.5)
>I don't understand how to write code, either DAO or SQL, for creating a
>table index composed of more than 1 field; i.e.,
>Index = (ASC)Field1,(DEC)Field2,(ASC)Field3
>I want to make this index the primary index (each record will have a
>unique field value combination).
>I appreciate any help.  (I am learning database programming slowly.)
>Dan Gewirtz


3(f) Eden Vale Road          Phone: 4972217
Ranelagh                     Fax  : 4972217 (Please ring first)
Dublin 6
Ireland


Fri, 02 Oct 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Request for help - limitation of 32 indexes including foreign indexes

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

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

4. Help: Creating Index with several Fields on Runtime

5. HELP with Creating and INDEX on an ACCESS TABLE

6. How to use multi-fields indexes in VBA

7. multi-field index

8. How to Create Multi-Key Indexes

9. remove index from field or delete a indexed field

10. Create table from a combined field in another table

11. NEWBIE : Help Requested - Using Index File

12. Adding fields to a table and index

 

 
Powered by phpBB® Forum Software