No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.