Our shop has used this extensively for a number of years, i.e., linking to SQL tables from MS-Access. I have recently noticed a problem and I am not sure how long it existed and/or if there is a fix. Our environment is Windows XP(SP2), Office 2007/MS-Access 2007, SQL 2005 and SQL 2000. The error occurs when linked to either SQL 2005 or 2000.
We have many large tables, 40,000+ records, and most have datetime fields, some with multiple datetime fields and these are the problem.When I create a new linked table I choose the External Data tab in the MS-Access "ribbon" at the top, I choose "More" under Import and go through the steps to link a table. After completing the link, if I open the table, still within the "External Data" ribbon tab, the table opens and I can focus on any cell, use arrows or tab to move quickly from field to field NO PROBLEM. However, if I select the "Home" tab from the top ribbon, the linked table remains open and I can focus into any cell and move using arrow keys or tabs, BUT, if I move into a datetime field, there is a long delay(30 secs - 5 minutes) before control returns. When it does, the total record count now appears in the bottom status bar("1 of 40,000" for example). If I move down the same datetime column there is no more delay. However, if I move into another datetime column, the delay recurs. It seems like the first time each datetime column receives focus it loops through each record doing something and it takes way longer than simply clicking the "Last Record" arrow in the bottom status bar.
If I close the table and open again the same problem occurs where the initial focus to a datetime column results in long delays. This only occurs when the current ribbon tab is "Home". Pretty weird, huh?
I would appreciate it greatly if you can solve this problem but, whether you can or not, would you recommend another good forum for this type of SQL/Access question. Mostly I deal with ASP.Net stuff.