4

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?

2

When I use Access, I use both techniques as some form of Command Query Separation (CQS)

That is, the form can read the base tables but all writes are via stored procedures.

In my case, I chose this for

  • security/audit reasons
  • easier to deal data integrity
  • re-use of write code for other clients

Note: I used a view for Access not a base table also for some security.

Edit, for clarity, after comments:

"Read only" refers to server-side permissions that are transparent to Access. Access would try to write if we let it (instead of intercepting the call and invoking a stored procedure), and we'd get an error from the DB engine.

  • Do you use local tables to 'pull' the read-only data into? I'm curious as to whether there is any other way, in particular when using a form in datasheet view. – David May 30 '11 at 10:22
  • @david: no, I'd use linked tables direct onto the SQL Server – gbn May 30 '11 at 10:26
  • you'll have to excuse me if I'm being stupid, but if your linked table is read-only how do you go about editing or adding records. Would you have a separate form for editing/adding records? I want to avoid that and allow the user to directly edit/add data like in a spreadsheet style (datasheet view). This only seems possible to me using a local table. – David May 30 '11 at 11:23
  • 1
    @david: ah... "Read only" is the server side permissions that are transparent to Access. If Access writes it would get an error based on permissions, not on any flag or form. However, the form is still bound to the table. All data changes are trapped in VBA and stored proc calls made before Access would write to the table. – gbn May 30 '11 at 11:30
  • I knew there was something clever I was missing out on! I've given it a quick test and it seems to work just perfect, nice one! Thank you for your insight. – David May 30 '11 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.