Indexing a table in another database

[WSF]

Access97

From my FE I create a table [tblJobInfo] in a temporary mdb [TempData.mdb]
using SELECT.....INTO... which I use for referencing in subsequent queries.
This works fine. In order to execute queries faster I have been told to
index the common field [JobNo] in the created table [tblJobInfo]. Can I do
this in code from the front end mdb while or after creating the table?

TIA

WSF

 

[PC Datasheet]

One thing you can do is to use an autonumber for the PK in the TempData file.
You will get two benefits, first, an autonumber is automatically indexed.
Second, just from the fact that your keys will be numeric rasther than string
will make your queries process faster.

 

[Marshall Barton]

Access97

From my FE I create a table [tblJobInfo] in a temporary mdb [TempData.mdb]
using SELECT.....INTO... which I use for referencing in subsequent queries.
This works fine. In order to execute queries faster I have been told to
index the common field [JobNo] in the created table [tblJobInfo]. Can I do
this in code from the front end mdb while or after creating the table?

You can do it after running the make table query. One way
is to open the temp db so you can get to the table's
TableDef object.

Dim dbTmp As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set dbTmp = OpenDatabase("pathtotempdb")
Set tdf = dbTmp.TableDefs("tablename")
Set idx = tdf.CreateIndex("indexname")
Set fld = idx.CreateField("JobNo")
idx.Fields.Append fld
tdf.Indexes.Append idx
tdf.Indexes.Refresh

Set fld = Nothing
Set idx = Nothing
Set tdf = Nothing
dbTmp.Close : Set dbTmp = Nothing

Another way would be to run an SQL DDL statement to create a
Constraint on the table:

CurrentDb.Execute "ALTER TABLE tablename " _
& "IN ""pathtotempdb"" " _
& "ADD CONSTRAINT indexname UNIQUE (JobNo)"

Or, instead of using a make table query, you could create
the table with the index and then use an append query to add
the data.

Dim db As DAO.Database
db.Execute "CREATE TABLE tablename " _
& "IN ""pathtotempdb"" " _
& (field1 TEXT 255, f2 LLONG, f3, DATETIME, " _
& "JobNo LONG
& "CONSTRAINT indexname PRIMARY KEY)", _
dbFailOnError
db.Execute "INSERT INTO tablename " _
& "IN ""pathtotempdb"" " _
. . . , _
dbFailOnError

 

[WSF]

Access97

From my FE I create a table [tblJobInfo] in a temporary mdb [TempData.mdb]
using SELECT.....INTO... which I use for referencing in subsequent queries.
This works fine. In order to execute queries faster I have been told to
index the common field [JobNo] in the created table [tblJobInfo]. Can I do
this in code from the front end mdb while or after creating the table?

TIA

WSF