Best practices for implementing an Access (2007) application

views:

205

answers:

+2 Q:

Best practices for implementing an Access (2007) application

Hello,

Where can I find an overview (website) of best practices for implementing an Access (2007) application (with a FE/BE architecture) regarding to security, performance and maintainability? I know about designing tables, queries, forms and so on and I'm a reasonable programmer, but I'm wondering what's the "best" and most efficient way to implement my "application".

Thanks in advance for your help.

+3 A:

Take a look at Tony Toews' Best Practices for Microsoft Access. Since you're developing a split application you owe it to yourself to also consider Tony's Auto FE Updater. That utility (which he offers for free) makes it fairly easy to roll out your application front end to the users, and make sure that they are always working from your latest app version.

I will also suggest you try to avoid basing your application's forms on large data sets. For example, rather than binding a form directly to a large table, bind it to a query which returns only one or a few rows from the table. Allow your users to select which row or rows they are interested in at any given time. The point here is to limit the amount of data Access must retrieve when the users open your form.

HansUp 2010-05-03 14:56:18

The great thing about @HansUp's advice about limiting the recordsets behind forms is that not only does it make your app upsize to a server-based back end much more efficiently, it also makes your Access app more efficient. I have been designing all my Access apps with upsizing in mind since c. 1998, and the result has been better performance in some cases that ended up allowing upsizing to be postponed years beyond what I'd have expected.

David-W-Fenton 2010-05-03 22:25:38

HansUp, thanks for your kind words.

Tony Toews 2010-05-04 20:41:21

+2 A:

Here are a few links to get you started

http://www.mvps.org/access/

http://www.lebans.com/

http://www.blueclaw-db.com/

Right time to don my flame suit once more and fire up the bound –v- unbound debate once again. Personally I almost always use unbound forms for new work for the control and performance they offer.

The counter argument to that is by using unbound forms you are chucking away a lot of what makes access access however for me anyway I maintain that the greater control and performance outweighs that and once you get into the swing of it I would say the development time is only very slightly longer.

Kevin Ross 2010-05-04 07:27:38

The links are fine, but the idea that unbound Access forms are good for data editing is perverse. I use lots and lots of unbound forms in my apps, but almost never for editing data, simply because there's so little downside to doing the editing in a bound form with only the edited record loaded. If you often feel you need more control than that provides, then you simply shouldn't be using Access at all.

David-W-Fenton 2010-05-07 00:59:15

This article refers to Access 2003, but it contains a good deal of interesting information :

http://msdn.microsoft.com/en-us/library/aa167840(office.11).aspx

Remou 2010-05-04 09:12:56

Sorry, but that article completely lost my confidence by recommendating COMPACT ON CLOSE. It's an idiotic recommendation because it compacts only the front end (users don't open data files, unless you're not following the best practices outlined in that very article), and it is not cancellable. Certain forms of exotic data corruption can be recovered from if the file is not compacted (i.e., the data is still available for pulling out into a non-corrupt file), while a compact can cause the data that was recoverable before the compact to be lost. TOTAL FAIL for the recommendation in the article.

David-W-Fenton 2010-05-07 01:02:54

+1 A:

Interesting topics around Access 2007: http://allenbrowne.com/Access2007.html

2010-05-04 11:14:26

An also interesting page at this website is: http://allenbrowne.com/tips.html

waanders 2010-05-06 08:57:03

Thank you all for the contributions. I will check them all.

waanders 2010-05-06 08:59:57

ansaurus

tags:

views:

answers:

Best practices for implementing an Access (2007) application

related questions