Add index to make-table query

[RichardMGreen]

Hi all

I have 5 make-table queries and I would like to add an index to each table as it's made. The index would be on the same field in each table but the entries in each field are not unique, i.e. you can have the same value multiple times.

That gives me two questions:-
1) Is it possible?
2) How would I do it if it is?

I have googled the problem but could get anything useful/understandable out if it (I'm a bit of a novice when it comes to things like this).

Any help greatly appreciated.

 

[Trevor G]

Richard

Here is some code which adds a field to a table, so if you adjust this to your table name, and field names plus field type then run it, and then adjust and run for the other tables hopefully that will help you

Sub AddFieldCombined()
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("Combined")
Set fld = tdf.CreateField("BranchAndTeam", dbText, 50)
tdf.Fields.Append fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[RichardMGreen]

Thanks for that.
I'd want it to be an Autonumber field for use as an index.

Any pointers?

 

[RichardMGreen]

Just tried this on a copy of one of the tables I want to index:-

Sub add_index()
    Call AddFieldCombined("temp_table")
End Sub
Sub AddFieldCombined(table_name)
    Dim db As Database, tdf As TableDef, fld As DAO.Field, fld1 As DAO.Field, fld2 As DAO.Field
    Set db = CurrentDb
    Set tdf = db.TableDefs(table_name)
    Set fld = tdf.CreateField("Index", dbBigInt, 50)
    tdf.Fields.Append fld
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Sub

It add the field OK but doesn't put anything into it.

Any ideas?

 

[Trevor G]

Change the field type to dbLong and adjust the attributes

New code

Sub AddFieldCombined()
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("tblSomething")
Set fld = tdf.CreateField("INDEX", dbLong)
With fld
' Appending dbAutoIncrField to Attributes
' tells Jet that it's an Autonumber field
.Attributes = .Attributes Or dbAutoIncrField
End With
tdf.Fields.Append fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[RichardMGreen]

Thanks for that. I'll give it a go.

I was looking at the SQL command "createindex" (courtesy of Google).

Would that be any better/faster?

 

[Norie]

An index isn't the same as a field.

A field can be used an index, and one of the things it does is speed up sorting.

It's not really clear what you want/need?

 

[SydneyGeek]

Do you have an existing field that you would like to be indexed? If you need to combine, search or join the tables it would make more sense than creating a new index just for the heck of it.
Of course, adding a new Autonumber primary key would also be useful. I usually use the Counter datatype when creating an Autonumber.

EDIT: Here's a routine I built for creating a primary key from Excel. Most of the code is applicable here.

Private Sub CreatePrimaryKey(strTableName As String, _
        varPKColumn As Variant)
    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim idx As ADOX.Index
    Dim sDB_Path As String
    Dim MyConn
    
    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    
    'create a connection to the existing database
    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    
    'create the catalog and use the newly created connection
    'also set the table reference
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn
    
    Set tbl = cat.Tables(strTableName)
    
    'delete any existing primary keys
    For Each idx In tbl.Indexes
        If idx.PrimaryKey Then
            tbl.Indexes.Delete idx.Name
        End If
    Next idx
    
    'create a new primary key
    Set idx = New ADOX.Index
    With idx
        .PrimaryKey = True
        .Name = "PrimaryKey"
        .Unique = True
    End With
    
    'append the column
    idx.Columns.Append varPKColumn
    
    'append the index to the collection
    tbl.Indexes.Append idx
    tbl.Indexes.Refresh
    
    'clean up references
    Set cnn = Nothing
    Set cat = Nothing
    Set tbl = Nothing
    Set idx = Nothing
    
End Sub

Denis

 

[RichardMGreen]

Bascically what I want to do is add "something" to existing tables (created using make table queries) to speed up queries based on the created tables.

I have 640 (and growing) reports to create based on a unique member reference (C4C_ID). At the moment, the 640 reports (one per member) takes about three and a half hours to create so I'm looking for something to speed it up.

The index (or whatever) would be based on an existing field (C4C_ID)

My thinking was, adding an index to the tables usually speeds up data retreival so it would help in this case. Some of the temporary tables have upwards of 100,000 records in them.

If anyone has any better ideas, I'm open to suggestions.

 

[Norie]

Richard

You have 640 reports.

That doesn't seem right, are all these reports unique.

eg each report does something none of the others do.