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”
- 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
I’ve been using unbound forms or 19 years and agree there is lots of coding to be done. Yet there are significant advantages:
1. See what you want.
You chose the data you want to view/edit which makes life easier.
Unbound forms perform better.
3. Time saver.
I know it sounds odd yet by using the form’s underlying datasource and link it to the report, there is no need for daily/weekly/yearly reports. One report fits all!
4. User friendliness
Entering multiple criteria in an unbound form is ways easier than applying filters.
5. Simple capturing screens.
Controls are used for all: view/add/edit/search.
While I agree with anyone that unbound forms aren’t a necessity, it pays off big time when it comes to data analysis (CRM, analysis, stats).
Since you can’t use “Bound forms” in a web application, if you ever plan to migrate your application UI from MS Access to a web browser, you’ll be happier if you had already gone through the trouble of leveraging unbound forms to begin with…
Can you clarify a bit what you are thinking regarding “a web application” —
If you are referring to web database available with Access 2010 and Access 2013, they do have bound forms that works even in web browser, so there is no need for unbound forms.
But if you’re thinking a generic web application made using PHP or ASP.NET, then you’re going to have to refit the application entirely and you probably can’t use any code from VBA used to manage data access anyway. You’d be dealing with entirely different object models.
But maybe I’m missing something? Can you elaborate?
Thanks for commenting!
Can we use database Acces (.mdb) as the front end and use the SQL Server as the back end? How can we do that?
Thanks a lot!
I’m going to have to disagree. I have recently started moving 20+ access databases to SQL server. I store the connection string and all of the SQL commands it’s to run in tables in the access file. I then write functions that will execute those SQL statements or return a recordset as a string that I can drop into a value list for a listbox or combo box. Yes, it’s more work to get set up but the performance is incredible. Also, using this method I do not have to manage DSNs on the client machine (which in a company spread over a 3 building campus would be a huge ordeal). Another advantage to this method is that when moving it from a dev to live environment I merely have to change the connection string in the table and do not have to do any re-coding. From a maintenance standpoint, it’s not a lot of work. Changing the behavior of the app isn’t any different than it was before. And if I need it to do something server side, I merely write the SQL statement in Studio, drop it on the table in the Access file and then call it in my code when I need it. I have also noticed a substantial drop in Access file bloating. Don’t have to do nearly as many compact/repairs.
Great feedback Joel!
It proves there are major benefits to unbound forms but as you mention, there is work involved to make it transparent to the user.
I’ll be writing a series of post on how to manage DSN-Less apps starting next week, look forward to your review on it.
Thanks for sharing