So this question has been bugging me and affecting our data analyses for some time now. Now that we have large tables and getting larger every month since a monthly import just keeps building on existing data to make them larger and larger (the largest right now is 1GB!), we need to know what some of the suspect parameters mean and figure out a way to optimize the connection between the MS Access frontend and the MySQL Server backend.
I had previously looked at http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-apptips.html
The section that begins with "If you have very large (long) tables in Access, it might" is the paragraph that seems to make a relevant point.
My problem is:
1. Whenever I try to select different flag options, and then close the Access db, and reopen, the dsn will prompt again asking for credentials and I can see that the flags go back to the way they were to begin with (in my case, the only flag set is Allow Big Results). I guess this would be overcome by opening the dsn through Control Panel and selecting the flags I want to set (hasn't reprompted me upon opening the database so can't see if it is going back to the settings it had, like as if it is 'hard-coded' in the DSN -- see #2??)
2. Not too clear about what the Return Matching Rows (2) and Allow BIG Results (8) flags do to help open the large (long) tables (I mean, I looked them up in the MySQL Reference Manual, but need to hear an expert's language rather than how they describe them). I selected those by opening the DSN through Control Panel, and then opened the Access DB, and tried to open the largest table, but then came up with an error "ODBC--connection to 'MySQL ODBC 5.1 Driver [IP address] failed' ??
If anyone can let me know how to easily resolve this issue, I would be greatly appreciative and thankful.