Bug #36331 Microsoft Access 2007 freezes when switching to Design View
Submitted: 25 Apr 2008 4:08 Modified: 13 Oct 2009 6:02
Reporter: Gable Baldridge Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1 OS:Windows (Vista Ultimate SP 1)
Assigned to: CPU Architecture:Any
Tags: Access 2007, Design View, freeze, vista

[25 Apr 2008 4:08] Gable Baldridge
Description:
I have a large (3M records) linked MySql table within Microsoft Access 2007 SP 1 using ODBC Connector 5.1 on Server 5.051a.

When I first create a query in Design View against the linked table and run the query, everything works fine.  At the point that I go back to Design View to modify the query and place my cursor within the Design Grid, I get an hour glass for 1 to 5 minutes.  It seems that Access is inadvertently pulling the entire table into Access.  On the MySql Administrator I see the "copying action taking place.  Once the app unfreezes and I once again place the cursor within the Design Grid, the same thing may happen again or it may not, and I can go ahead and modify the query and run it.  But each time I go back to Design View, the problem again occurs.  It is important to note that on a fresh reboot of my PC, the problem does not occur until I have modified the query several times, but then, inevitably, the problem occurs until a fresh reboot of my PC.  This issue seems to be caused by an eventual degrading of either Access or MySql.

I have been experiencing this problem for 1 year.  The same problem occurred on another system that I used daily as well (OS: Windows Vista Home SP 0, ODBC Connector: 3.51, App:  Access 2007).  I was hoping with a my brand new PC that I began using 1 week ago, that the problem would go away but it did not.  Also, over the last several years, I never experienced this problem when under Windows XP, ODBC Connector 3.51, Server 4.1, Access 2000.

How to repeat:
Using Access 2007, link a large MySql table.  Create a query against this table in Design View.  Run the query.  Go back to Design View and place cursor within the Design Grid.  Repeat until Access begins freezing up.

Suggested fix:
Unknown.  I do not know if the problem lies with Access 2007 or MySql.
[25 Apr 2008 4:46] Tonci Grgin
Hi Gable and thanks for your report.

Besides the fact that I don't like being beta tester for such an expensive product as Office, I don't have Office2007 at all. So we'll have to work from your side mostly.

First of all, I would like you to confirm that Access2000 (I'll check Access2003) has no such problem linked against same connector/database.

Next, I would like you to turn MySQL server logging on and see what's really sent to server. I would also like you to turn ODBC DM tracing on (see manual, Start / Control Panel / Administrative tasks / DataSources (ODBC), "Tracing" tab, "Start tracing now") and attach relevant part of trace to this report. Further more, we might also benefit from "SHOW PROCESSLIST" and by looking at task manager while "lockup" occurs.
[25 May 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Oct 2009 0:51] Scott Lindberg
I can confirm the same issue with linked tables to SQL Server 2005, so it is appears to be a MS Access 2007 issue or OBDC driver issue.
[13 Oct 2009 6:02] Tonci Grgin
Thanks for sharing this with us Scott!
Connectors and 3rd party tools can really make you mad dealing with problems like this one...
[13 Oct 2009 16:12] Scott Lindberg
I believe this is the issue (Subdatasheet Name property set to [Auto]) is explained in the following MSFT KB, I still need to test and will confirm results.

http://support.microsoft.com/kb/275085