Indexing problem with Make Table query and Create Index code (repost)

Hi,
>
No-one replied to this when I sent it last week. Any bites out there
today??
>
-----
>
My application allows users to create a new back end for separate
purposes. It does this by using Make Table Queries and Indexing via SQL.
For example ...
>
sqlString = "SELECT * INTO " & TableName & " IN '" & NewDBName & "' FROM
" & TableName & " WHERE 1=2;"
DoCmd.RunSQL sqlString
'The WHERE 1=2 ensures that no records are copied - only the structure is
created.
>
sqlString = "CREATE INDEX AccountNumber ON PatientsT (AccountNumber)
WITH PRIMARY"
NewDB.Execute (sqlString)
>
When this new back end is attached to the front end, creating a new record
is up to 10 times slower than with the original back end. With the various
procedures that the front end goes through, if the back end is on a file
server, it can take up to 10 seconds between hitting my <New record>
button and the cursor appearing in the first field. I can reliably fix
this problem by opening the back end manually in Access, and deleting and
recreating the primary key index using the menu option in the table design
view.
>
I have also tried creating the back end using the following syntax for the
indexes, viz ...
>
Set tdf = db.TableDefs("PatientsT")
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("AccountNumber")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
>
Both indexing methods appear to work - the primary key icon sits next to
the correct field in the table design window. But the only thing that
makes the file work properly is manually deleting and recreating the
primary key in the table design window. I've tried this with empty tables,
and tables with a small amount of data - makes no difference. Compacting,
refreshing, relinking etc make no difference.
>
Any ideas on what is happening?
>
Owen Jenkins

>Owen, it seems that Access caches a bunch of info about the table in the
>link, even though that's not all exposed through DAO or other object model.
>
>If my "relinking", you mean the common approach of setting the Connect
>property of the TableDef, that might not be enough to get it to discover the
>new index. Does it make a difference if you delete the link (attached
>table), and recreate it?
>

Hi Allen,
>
Thanks for replying.
It makes no difference how I relink - whether I delete and recreate - or
just refresh. The result is the same.
If I create the back end tables with no indexes at all, I have the same
problem. Creating a record from the front end is agonisingly slow.
However, if I open the new back end, open the PatientsT table, set an
index on the AccountNumber field, then remove it again, then save the
table, this fixes the problem. Even though the table is never once saved
with an index. And this happens even if I don't then refresh the links to
the front end. So it is not the presence of the index that matters. There
appears to be something strange in the table creation process that causes
the problem. And this is done with just one sql command ...
>
sqlString = "SELECT * INTO " & TableName & " IN '" & NewDBName & "' FROM
" & TableName & " WHERE 1=2;"
DoCmd.RunSQL sqlString
>
The problem does seem to be limited to one table though. There are 11
tables in total, each created with the above line.
>
Owen
>
>
Allen Browne wrote:
>