Any ADOX experts?

[Your name]

I have a simple problem here in Access 2003.

I import 18 .csv files into 18 unique tables.. I then programatically want
to add an index to each of the 18 tables. The code creates the index for
the first table then gives a message on the 2nd thru 18th table that I try
to create the index for.

The message: Run-time error: '3367':
Object is already in collection. Cannot append.


I am trying to create a "TradeDateIndex" index on each of the 18 tables.


The code:

Public Sub CreateIndexOnStockTables()
Dim tbl As New ADOX.Table
Dim index As New ADOX.index
Dim cat As New ADOX.Catalog

Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection

For Each tbl In cat.Tables
If tbl.Type = "TABLE" Then
If (UCase$(Left$(tbl.Name, 10)) = "STOCKTABLE") Then
With tbl
index.Name = "TradeDateIndex"
index.Columns.Append "Date"

tbl.Indexes.Append index

End With
End If
End If
Next

Set tbl = Nothing
Set index = Nothing
Set cat = Nothing

End Sub



I have a reference set to "Microsoft ADO Ext. 2.8 for DDL and security.


Thanks in advance for your help.

 

[Sylvain Lafontaine]

I don't use ADOX but if I were you, I would try changing the name of the
index "TradeDateIndex" each time, for example by adding a number or the name
of the table.

 

[Allen Browne]

You might find there is a problem with the declaration:
Dim index As New ADOX.Index

That line is actually a contraction of:
Dim index As ADOX.Index
Set index = New ADOX.Index
After you have assigned the index to an actual index (i.e. after the first
pass through the loop), it is no longer set as a new index, i.e. it needs to
be re-initialized.

Also, it might not be a good idea to name your variable the same as one of
the objects in the library.

So, at the top of your procedure try:
Dim ind As ADOX.Index
Then, inside the loop, just before "With tbl":
Set ind = New ADOX.Index
Optionally, after the loop just after "End With", you might also like to
dereference your variable:
Set ind = nothing

 

[Brendan Reynolds]

One small point to add to what Allen has said. I believe you'll find that
the error is occurring on the line ...
index.Columns.Append "Date"

Because, as Allen says, the index variable is initialized only once, when
declared, on the second and subsequent passes through the loop the Columns
collection already contains a Column object named 'Date', hence the 'object
is already in collection' error message.

It might be possible to continue initializing the index object only once,
before entering the loop, if you also append the 'Date' column only once,
before entering the loop - but I have not tested this.

BTW: 'Date' is a reserved word, and therefore not a good choice for a field
name. It can cause problems which can be difficult to debug, as they are
easily mistaken for similar problems caused by a missing or mismatched
reference.