Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form).
Option 1 is to simply create a linked table and set that as the record source for the sub-form. This of course requires little work.
Option 2 is to create a set of stored procedures for inserting, deleting, updating and reading records from the MySQL table to/from a local temporary table. The sub-form's record source is then set to the local table and the appropriate form events are tied to the appropriate stored procedure calls. Obviously this requires a lot more work than option 1.
Now the obvious question is why ever go for option 2? Well, I've been experimenting with replacing linked tables with stored procedures and have found the speed increase to be considerable, particularly over a slow network. I've done a bit of detective work too and dug around in the MySQL log files and I've noticed that the linked table approach generates a shocking amount of SQL calls. This explains the performance difference I suppose.
Anyway I'm now considering replacing all linked tables with the stored procedures option (well maybe just the important tables). Has anyone else had this experience? Any thoughts or advice?