Where are you going to get the Table and Field names from?
What you're trying to do is certainly possible, but how you do it all
depends on how you are going to provide the values.
For example, you could have two tables: one containing the names of the
tables you want to do this for, and the other containing the names of the
fields you want to create indexes on (linked to the first table). You could
then have code like the following untested air-code:
Dim dbCurr As DAO.Database
Dim rsTables As DAO.Recordset
Dim rsFields As DAO.Recordset
Set dbCurr = CurrentDB()
Set rsTables = dbCurr.OpenRecordset("SELECT TableName From Tables")
Do While rsTables.EOF = False
strTableName = rsTables!TableName
Set rsFields = dbCurr.OpenRecordset("SELECT FieldName FROM Fields " &
_
"WHERE TableName = '[" & strTableName & "]'")
Do While rsFields.EOF = False
strFieldName = rsFields!FieldName
sqlDeal = "CREATE INDEX " & strFieldName & _
" on " & strTableName & "(" & strFieldName & ");"
dbCurr.Execute sqlDeal
rsFields.MoveNext
Loop
rsTables.MoveNext
Loop
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Jerad via AccessMonster.com" <u17799@uwe> wrote in message
news:5dbe8cb5c9c44@uwe...
> Douglas,
> Thanks for the speedy response.
> Sorry if I was confusing, the field names are NOT the same in the seperate
> tables, i was just using the names as generic example. I need an index for
> fieldOne and fieldTwo on table1, and then for Table2 I need indexes for
> anotherField1 and anotherfield2. Basicly, instead of repeating the sql
> statement :
>
> strTableName = "tblInventory"
> strFieldName = "DE_CAT1_PART"
> sqlDeal = "CREATE INDEX " & strFieldName & " on " & strTableName & "(" &
> strFieldName & ");"
> DoCmd.RunSQL (sqlDeal)
>
> Over and over, changing the variable strings each time( id have like 5
> pages
> of repetative code just with different table names and fields). Id like
> for
> it to loop, and change the strings in the variable to specific pre defined
> field names. let me know if that helps.
> Thanks again,
> Jerad
>
>
>
> Douglas J. Steele wrote:
>>Are you also saying that tableOne and tableTwo will have the same fields
>>in
>>them? That sounds extremely unusual, and is probably indicative of a
>>database that isn't properly normalized.
>>
>>As well, are you saying that you want an index on fieldTwo in tableOne and
>>an index on fieldThree, or that you want one index that contains both
>>fields?
>>
>>> Okay, heres my Problem. I need to create indexes on multiple fields in
>>> multiple tables. These fields and table names are static, but i'd like
>>> to
>>[quoted text clipped - 23 lines]
>>> Thanks in advance.
>>> Jerad
>
> --
> Jerad
>
> Message posted via http://www.accessmonster.com
Similar Threads
