We have a strange problem with joining tables from SQL Server 2005 and MS Access 2003.
There is a large desk on the server and there is a small table in locally accessible access. The tables are added through 3 fields, one of them is the day-to-day field (one day; idea is to get additional data (daily) from the large server table to add data to the local table).
Up until the weekend it was fine every day since yesterday we have experienced strange non-time-out in reach with this question. Non-time-out means that the query always runs with high network transactions, but there is no time-out. The login progress bar also does not show that the server trace tells us that SQL Server exectuted the same query without error and without any consequences. We have narrowed down the problem, it seems that the server table is being used with a large table and is included in it or there is a date, but we are not really able to narrow it down. We have already rebuilt the index and have currently restored the backup data, but maybe there are some things we can try here.
Thank you, Mike.
If you enter a linked table in a local table in SQL Server, and the query Link data is not really trivial according to specific limitations, it is very likely that access will pull the entire table from SQL Server and connect locally with the whole set. This is a known problem.
This does not directly address the question you ask, but how far are you away from keeping all the data in one place (SQL Server)? IMHO you can expect the same type of performance problems as long as you have some data in each system.
If all this happens in SQL Server, then a pass-through query will optimize and use the available index, etc.
Comments
Post a Comment