CREATE INDEX Multiple Variables SQL

[Jerad via AccessMonster.com]

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 use
my variables for table names and field names in the SQL statement. Heres my
code, so far

Public Sub tryIndexing()
Dim sqlDeal As String
Dim strFieldName As String
Dim strTableName As String

strTableName = "tableOne"
strFieldName = "fieldOne"

sqlDeal = "CREATE INDEX " & strFieldName & " on " & strTableName & "(" &
strFieldName & ");"
DoCmd.RunSQL (sqlDeal)
End Sub

Now i need to create index for fieldTwo and fieldThree and then (tableTwo,
fieldOne and FieldTwo Ect.)
Im not sure how to go about looping this to change these variables each loop.
Any help appreciated,
Thanks in advance.
Jerad

 

[Douglas J. Steele]

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?

 

[Jerad via AccessMonster.com]

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

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

 

[Douglas J. Steele]

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

(no private e-mails, please)

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

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