DAO and Adding Index to a Table

DAO and Adding Index to a Table

Post by Frank Woo » Fri, 08 Sep 2000 22:16:16



I'm using DAO to create a database with an index for a given table with two
keys as the index field.

Here is a snippet of the code:

                Dim sKey as String

                sKey = "Customer_ID,Product_ID"   ; two fields in the
customer pricing table

                Set NewField = NewIndex.CreateField(sKey)     <----- here is
where the error occurs
                NewIndex.Fields.Append NewField
                NewIndex.Primary = True
                NewIndex.Unique = True
                NewTableDef.Indexes.Append NewIndex

The problem is that it doesn't like the "sKey" to have a comma separating
the two fields I'm using for the key to the index. I tried a semi-colon, but
that doesn't work either; same problem.

I can say "sKey="Customer_ID" and that will work just fine.... albeit I
don't have the index properly defined as I want it utilizing both fields.

I can set up an index this way in Access, or even the utility that comes
with VB - just can't do it in code, yet I'm sure there is something I'm
doing wrong.

If anyone can help, I'd sure appreciate it.

thanks in advance,
Frank Wood

 
 
 

DAO and Adding Index to a Table

Post by Robb » Sun, 10 Sep 2000 20:11:52


Hello Frank. In DAO you simply define and append each separate field to the
index, one after another, before finally appending the index to the table.
Does that not work in ADO too?


>I'm using DAO to create a database with an index for a given table with two
>keys as the index field.

>Here is a snippet of the code:

>                Dim sKey as String

>                sKey = "Customer_ID,Product_ID"   ; two fields in the
>customer pricing table

>                Set NewField = NewIndex.CreateField(sKey)     <----- here
is
>where the error occurs
>                NewIndex.Fields.Append NewField
>                NewIndex.Primary = True
>                NewIndex.Unique = True
>                NewTableDef.Indexes.Append NewIndex

>The problem is that it doesn't like the "sKey" to have a comma separating
>the two fields I'm using for the key to the index. I tried a semi-colon,
but
>that doesn't work either; same problem.

>I can say "sKey="Customer_ID" and that will work just fine.... albeit I
>don't have the index properly defined as I want it utilizing both fields.

>I can set up an index this way in Access, or even the utility that comes
>with VB - just can't do it in code, yet I'm sure there is something I'm
>doing wrong.

>If anyone can help, I'd sure appreciate it.

>thanks in advance,
>Frank Wood


 
 
 

1. Trouble Adding Indexed Field with DAO

I'm writing an app to add tables and fields to an existing database. While I
am able to add fields and tables without a problem... I am currently unable
to add an indexed field to the table. I'll post the code below (this example
is more or less taken from the microsoft help files).

tblQW is a tabledef object
idx is an index object

basically tblQW is a new TableDef object.. Other fields are added below
this. The following error occurs when I try to append tblQW to the tabledefs
collection:

Error 3409, Invalid Field Definition 'IDEvaluation' in definition index or
relationship.

            With tblQW
                Set idx = .CreateIndex("IDEvaluation")
                With idx
                    .Fields.Append .CreateField("IDEvaluation")
                End With
                .Indexes.Append idx
            End With

Changing Index Names or field names does not appear to help. Am I going
about this the right way? Any help is greatly appreciated.

2. Where can i download a SQL6.5 server ? Thanks!

3. DAO & Indexes - Adding records/Current record

4. VB5 and access database

5. HileStorm:how to add a picture into table using ado/dao code not using controls

6. Datetime datatype

7. DAO 3.5 Adding Relationships to tables

8. Free trial: Cygron DataScope data mining

9. Problem adding record to blank Access Table using DAO

10. Add new table and add fields to table - Syntax - Working Code Example

11. DAO - Fill in foreign key with correct PK index from a string-data table

12. DAO - Fill in foreign key with correct index from a string-data table

13. Lookup Field in a Table, add an index