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

[Owen Jenkins]

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 recordbutton 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

[Allen Browne]

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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <[e-mail] in message
news:44***********************@news.optusnet.com.a u...

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 Jenkins]

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:

>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?

[Allen Browne]

Presumably you have eliminated the usual suspects such as Name AutoCorrect,
SubdatasheetName, and so on.

Tony Toews has a list of those:
http://www.granite.ab.ca/access/performancefaq.htm

Not sure what else to suggest.

Perhaps someone else has an idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <[e-mail] in message
news:44***********************@news.optusnet.com.a u...

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:

>>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?

[Owen Jenkins]

Yes, I've done all the relevant things as you suggested. No difference.

Miraculously though, the problem has disappeared today. No explanation.
I had removed and reinstated the index to the back end table by opening
the front end. I couldn't save the change to the indexing since it was a
linked table, but this procedure is all I did before the problem seemed
to resolve.

It's all a mystery to me. I'll cross my fingers that it doesn't reappear
with another client.

Thanks for your help. I'd be glad to hear if anyone has any similar
experiences.

Owen