Linked Tables - indexes

[osupratt via AccessMonster.com]

I have most of my tables in my front-end Access 'linked' to a sql server back-
end. I have some reports that take a couple of minutes to run (sql reports,
not access). When running a query plan or optimizer it tells me of some
indexes to add on the back-end sql tables. When adding these indexes the
reports now run very fast or instantaneous. The problem I have now is that I
get errors on my front-end saying Timeout occurred. Has anyone had any
experience with this and why would my front-end act like this all of a sudden?
Thanks.

 

[John W. Vinson]

I have most of my tables in my front-end Access 'linked' to a sql server back-
end. I have some reports that take a couple of minutes to run (sql reports,
not access). When running a query plan or optimizer it tells me of some
indexes to add on the back-end sql tables. When adding these indexes the
reports now run very fast or instantaneous. The problem I have now is that I
get errors on my front-end saying Timeout occurred. Has anyone had any
experience with this and why would my front-end act like this all of a sudden?
Thanks.

If you're using ODBC to link to the tables, Access has stashed some
information about the backend table in the frontend, and by changing the
structure of the backend (by adding an index), the information is out of date.
I've gotten in the habit of routinely *deleting* the link from the frontend
and using File... Get External Data... Link... ODBC to relink to the table
after any structural change to the table.

 

[Roger Carlson]

John,

Have you had trouble with the Relinker?

 

[John W. Vinson]

John,

Have you had trouble with the Relinker?

Yes, very much so, if there has been a structural change to the backend table.
I've had wierd things - data coming into the wrong field (even different
datatype fields), no records displayed at all, searches failing. I now ALWAYS
delete and recreate the link after making any table structure change in the
backend.

 

[Roger Carlson]

Good to know. Thanks.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/