VBA code to "Change the current Index?" or must one Delete then Create new?

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003

In Access is there VBA code to "Change the current Index?" or must one Delete then Create new?

I can Create with:
Currentdb.Execute "create index CLOA on ChooseRev (CONCACT);"

and Delete with:
tdf.Indexes.Delete CLOA.Name

But can I create two Indexes and switch between them

TIA EagleOne
 
Therefore, can I assume that the Order By is part of the RecordSet coding process as opposed to
Currendb?
 
In the code next, I have created two indexes on two tables:

CurrentDb.Execute "ALTER TABLE STARSRev ADD COLUMN DOV_ABS VarChar(50);"
CurrentDb.Execute "ALTER TABLE CHOOSERev ADD COLUMN DOV_ABS VarChar(50);"
CurrentDb.Execute "Update STARSData Set DOV_ABS=DOV_NUMB & REG_NUMB & AMT;"
CurrentDb.Execute "Update CHOOSEData Set DOV_ABS=DOV_NUMB & REG_NUMB & AMT;"
CurrentDb.Execute "create index DOV_ABS on STARSRev (DOV_ABS);"
CurrentDb.Execute "create index DOV_ABS on CHOOSERev (DOV_ABS);"

CurrentDb.Execute "ALTER TABLE STARSRev ADD COLUMN DOCN_ABS VarChar(50);"
CurrentDb.Execute "ALTER TABLE CHOOSERev ADD COLUMN DOCN_ABS VarChar(50);"
CurrentDb.Execute "Update STARSData Set DOCN_ABS=DOCN_NUMB & REG_NUMB & AMT;"
CurrentDb.Execute "Update CHOOSEData Set DOCN_ABS=DOCN_NUMB & REG_NUMB & AMT;"
CurrentDb.Execute "create index DOCN_ABS on STARSRev (DOCN_ABS);"
CurrentDb.Execute "create index DOCN_ABS on CHOOSERev (DOCN_ABS);"


In VBA, how could I "Order By" or change indexes from DOV_ABS to/from DOCN_ABS?

Thanks for sharing your time/knowledge

EagleOne
 
Bob,

The code should have been:

CurrentDb.Execute "ALTER TABLE STARSRev ADD COLUMN DOV_ABS VarChar(50);"
CurrentDb.Execute "ALTER TABLE CHOOSERev ADD COLUMN DOV_ABS VarChar(50);"
CurrentDb.Execute "Update STARSRev Set DOV_ABS=DOV_NUMB & REG_NUM & AMT;"
CurrentDb.Execute "Update CHOOSERev Set DOV_ABS=DOV_NUM & REG_NUMB & AMT;"
CurrentDb.Execute "create index DOV_ABS on STARSRev (DOV_ABS);"
CurrentDb.Execute "create index DOV_ABS on CHOOSERev (DOV_ABS);"

CurrentDb.Execute "ALTER TABLE STARSRev ADD COLUMN DOCN_ABS VarChar(50);"
CurrentDb.Execute "ALTER TABLE CHOOSERev ADD COLUMN DOCN_ABS VarChar(50);"
CurrentDb.Execute "Update STARSRev Set DOCN_ABS=DOCN_NUMB & REG_NUMB & AMT;"
CurrentDb.Execute "Update CHOOSRev Set DOCN_ABS=DOCN_NUM & REG_NUMB & AMT;"
CurrentDb.Execute "create index DOCN_ABS on STARSRev (DOCN_ABS);"
CurrentDb.Execute "create index DOCN_ABS on CHOOSERev (DOCN_ABS);"
 
Back
Top