It s important to realize that only certain types of table statistics and metadata for ODBC data sources are going to be relevant to Jet. In general, I think the indexes are the only part that really matters from the standpoint of Jet deciding what to do with a SQL statement -- as long as the indexes are there and it understands the server on the other end, it will hand everything over to the server to process unless you ve foolishly selected or sorted on an expression that the server database can t understand. In that case, Jet may still be smart enough to send parts of the SQL statement for processing by the server (such as joins and criteria on literals), and then use the result set returned with the expression.
In regard to the question about linked tables, thouh, unfortunately, there is no dynamic way to update ODBC linked tables -- for instance, add a column to a table or view and it won t be in the table returned, refresh the link and the new column will appear, but it might be read-only.
I have found that the only thing to do when changing your server s table structures and views is to simply delete and recreate the relevant linked tables. I don t know that there s any programmatic way to do this. If there s any metadata about the table/view that tells when its structure was last updated, you might be able to check that against the date of the linked table, and delete and recreate when the back end has been updated after the link, but I m just guessing here. But that would be back-end specific.
For completeness sake, I ll just give the basics with a Jet back end:
With Access to Jet, a compact should reset the table stats, and upon the next run of each SQL statement, the compilation for each statement will be discarded and the query plan recalculated based on the new stats. It s important if you re compacting a front end for the purpose of updating table stats that you compact the back end before you compact the front end, because that insures that all the back-end table stats are as accurate as possible (though most of them stay up-to-date even without a compact).
