Many of you know that I’m a big fan of Access with SQL Server, and if you’re a frequent blog reader you will also know it’s not easy to optimize the relationship between them, it takes work but it’s very rewarding when they’re working great.  Some developers however go to the extreme and use unbound forms, is it worth it?

To bind or not to bind, that is the question

Unbound forms are forms with no data source, whereas bound forms have one and it’s the default way to bind your form to your tables or queries. Having a data source is the normal way you create Microsoft Access forms,  but it can cause problems no matter how careful you can be:

  • Record Locks – Have you ever had an app grind to a halt because someone left for lunch while editing a record?
  • Slow performance in opening a form: A poorly designed form can be bring your app to it’s knees.
  • Lost Data – Stop me if you’ve heard this one: “You and another user are editing the same record, you can either copy to the clipboard or discard your changes”
Unbound forms avoid all of the hassles mentioned above:
  • Quick loading of data since you only retrieve data the user needs to see in that moment.
  • No locks placed on your tables
  • You can parse the data through all kinds of business rules before posting back to your database.

Wow, that sounds awesome! What’s not to like?

Unbound forms require a LOT of code to work. You basically must take over the tasks Access will normally handle: retrieve data from your database and store it locally, load the data into your forms fields, wait until user saves, discards or edits data, save said data back to your database. In short, you have just increased your workload tenfold, for a small increase in performance, if any.

Tips for using bound forms:

Rather than using unbound forms, here are some tips to optimize bound forms you can use to avoid issues:

  • Maximize the user of views and stored procedures. Ask yourself every time you want Access to do something if it can be done by the server better and faster.
  • Never display data the user has not asked for. For example, always use the where clause of the DoCmd.OpenForm and DoCmd.OpenReport to display the exact record(s) needed by the user.
  • Use temporary tables as much as you can. They are great for speeding up your app and provide some of the benefits of unbound forms. Example: Avoid mixing local tables with SQL Server tables and instead download the SQL Server data into one or more temp tables.
  • Use infrequently updated lookup tables in the frontend and not on SQL Server. For example, the list of 50 states and territories should be a native table in your FE. Have a duplicate copy on  your server for views you may create for your app.
  • Maximize the use of ADODB. If you haven’t learned how to use ADODB you are really missing out on the best method to communicate with SQL Server from Access.  For example, instead of using DAO and action queries on linked tables, send the action query to SQL Server, which can do it far faster in almost all cases. Ben has in exceptional cases, even bound forms to ADO recordsets, which offers full native functionality and is still easier than doing it unbound. However, it has some caveats that you should be aware of if you go this route.
  • Learn TSQL: To really turbo charge Access with SQL Server you’re going to have to learn SQL Server’s native language. What are you waiting for? Go out and purchase a good TSQL beginner’s book and learn it today!

Hope these tips help you out! If you want even more, look at those excellent guides:

UtterAccess’ Beginner’s Guide to ODBC

Andy Baron’s Optimizing Microsoft Access with SQL Server