setting index on temporary table

[icccapital]

I have created a temporary database and table that is then linked to my front
end database. I found the queries to be slow until I indexed the two fields
that I was searching on in the linked table. I would like to do this in the
code that creates the table and fields but am having trouble.

I have tried to execute the sql statement create table and to call the
createindex method then appending the index to the tableddef of the linked
table without success. Sorry for the open ended question but I figured I
would let you tell me how to do it or direct the questions instead of me
telling you the 10 things I tried and failed. Thanks for the help. Below is
the code creating the tabledef of the linked table with one of the attempts
at create index.

Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("ApprID", dbLong)
Set idx2 = .CreateIndex("Index2")
With idx2
.Primary = False
.Unique = False
.Fields.Append .CreateField("ClientCode", dbText)
.Fields.Append .CreateField("ReportDate", dbDate)
End With
.Indexes.Append idx2
.Fields.Append .CreateField("SecuritySymbol", dbText)
.Fields.Append .CreateField("SecurityName", dbText)
.Fields.Append .CreateField("Quantity", dbText)
.Fields.Append .CreateField("UnitCost", dbDouble)
.Fields.Append .CreateField("TotalCost", dbDouble)
.Fields.Append .CreateField("Price", dbDouble)
.Fields.Append .CreateField("MarketValue", dbDouble)
.Fields.Append .CreateField("PercentOfAssets", dbDouble)
.Fields.Append .CreateField("SecurityType", dbText, 4)
.Fields.Append .CreateField("AssetClass", dbText)
.Fields.Append .CreateField("Sector", dbText)
.Fields.Append .CreateField("InterestRate", dbText)
.Fields.Append .CreateField("MaturityDate", dbText)
dbsTemp.TableDefs.Append tdfNew
End With

 

[Jon Ley]

Not sure, but I think you need to have the fields created in the table before
you add them to the index:

....
..Fields Append .CreateField("ClientCode", dbText)
..Fields.Append .CreateField("ReportDate", dbDate)
Set idx2 = .CreateIndex("Index2")
With idx2
.Primary = False
.Unique = False
.Fields.Append .CreateField("ClientCode")
.Fields.Append .CreateField("ReportDate")
End With
..Indexes.Append idx2
....

Regards,

Jon.

 

[icccapital]

That will run through fine, but when I go check the table in the temporary
database, those fields don't have the indexed property set to yes. Are they
set but the gui isn't showing it? Thanks

 

[Tony Toews [MVP]]

Not sure, but I think you need to have the fields created in the table before
you add them to the index:

...
.Fields Append .CreateField("ClientCode", dbText)
.Fields.Append .CreateField("ReportDate", dbDate)

You probably also need the following in between these two chunks of
code before you can create the index:

dbsTemp.TableDefs.Append tdfNew

Set idx2 = .CreateIndex("Index2")
With idx2
.Primary = False
.Unique = False
.Fields.Append .CreateField("ClientCode")
.Fields.Append .CreateField("ReportDate")
End With
.Indexes.Append idx2

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[icccapital]

It looks like this code doesn't change the indexed property of the field, but
it did create and affect the indexes collection. I didn't know it existed
before, but found in table design -view - indexes that the indexes are there
and the queries run well. Thanks for the help seems to be working.