Can a new index on Sql Server 2000 table break existing linked tab

[Guest]

I added new indexes to some SS2000 tables and it appears to have broken our
Access/Jet application which has links to those same SS2000 tables. The
Access/Jet application ran like a real pig until the new indexes were dropped
from the SS2000 tables. Personally, I am suspicious that the new indexes
actually caused the problem but I thought I would ask you as I am a DBA and
not Access literate. I know that Access/Jet stored table schema and index
info when a table is linked, but since a new index would be unknown to the
Access/Jet application the new indexes should be ignored by the application.

What do you think?

Thanks,

Michael

 

[Guest]

Try re-linking the tables. That will at least refresh any stored schema and
index info.

Steve

 

[Guest]

Steve,
I still need my questions answered! Does adding a new index to a table
referenced by an Access/Jet application break the Access/Jet application???

 

[Douglas J. Steele]

I've never heard of adding an index causing a problem, but Steve's answer is
correct. Whenever you make schema changes to your table, it's probably a
good idea to delete and recreate the linked tables in Access.

 

[David W. Fenton]

I've never heard of adding an index causing a problem, but Steve's
answer is correct. Whenever you make schema changes to your table,
it's probably a good idea to delete and recreate the linked tables
in Access.

This applies to any external database, not just SQL Server. For
instance, adding fields in MySQL breaks any Access table links, and
they have to be recreated from scratch.

 

[Guest]

David,
I think adding columns to a table is a little different in that I read that
Access/Jett does things like "Select * from . . .", so adding columns would
certainly surprise Access/Jet. But adding a new index after a table is
linked implies to me that Access/Jet would remain ignorant of the new index
and should continue as if the new index did not exist. Unless
Access/Jet/ODBC requests metadata at some later time, how would it possibly
know? Relinking tables is like re-booting Windows: it fixes things but
does not provide any understanding!

Thanks,
Michael

 

[David W. Fenton]

I think adding columns to a table is a little different in that I
read that Access/Jett does things like "Select * from . . .", so
adding columns would certainly surprise Access/Jet. But adding a
new index after a table is linked implies to me that Access/Jet
would remain ignorant of the new index and should continue as if
the new index did not exist. Unless Access/Jet/ODBC requests
metadata at some later time, how would it possibly know?
Relinking tables is like re-booting Windows: it fixes things but
does not provide any understanding!

Indexes are part of the metadata that is stored in the link,
precisely because Jet needs to know about that without having to
look up the metadata each time it uses the table. This does not
surprise me at all.