MS Access Programming Overview [closed]

Question

I'm a Java EE developer and was just contacted by someone who wants me to put a quote together for an application for his business that can integrate with their MS Access "backend".

I was hoping to post this and just get a general high-level overview of best practices involved with MS Access Programming. I assume the program would be entirely in VB, but didn't know if I would have the option of writing something in VB.NET or (preferably) C#.

Also, I despise the presentation layer: any good GUI-builders for Access applications?

What are some common tools and APIs (unit test frameworks, build automation systems, etc.) that MS Access programmers frequently use?

Any links or resources you would recommend?

It sounds like a pretty simple application: take inputted data, compare it to some tables, and throw some output to the screen. I'm a pretty decent programmer so even though I've never done an Access program before it couldn't be too difficult for me to pick up.

Thanks for any thoughts or suggestions!

Answer 1

Access as a front-end

Well, people feel strongly about Access. The main reason for the negative view is that it is widely used by non developers who have no concept of proper database development and end-up with these barely working, horribly designed applications that bring dread into the heart of developers.

Having said that, Access is nothing more than a Rapid Application Development tool with a very low barrier of entry.

The good and the bad

Access is a fairly old product line, spanning almost 20 years. A lot of people's objections to Access as a technology are grounded in its early history: since backward compatibility is something that Microsoft deems important, Access has retained most of its features, good or bad, over the years.

You can see the choice of VBA, non-winform forms, promotion of modules over OOP, to be bad choices, but the continuity of these early-on design decisions have made Access a stable platform to build on.

• VBA: is the language that never dies. It's clunky, outdated, lacks recent advances in languages, but it's also fast, simple to learn, flexible, easy to interop with (for instance calling Win32 APIs is really simple), and it can be interfaced with external libraries (event written in .Net).

• Bound forms: by default Access makes it easy to create working applications without a line of code. Most queries can also be created without having to go down to SQL at all. If you're a control freak, it can be annoying, but it's easy to drop down to code and control everything from there.

• Third-party integration: even though there are lots of add-ons to Access, I wouldn't recommend using most of the user-controls or third-party libraries unless you really have to. They can be very hard to deploy in environment with limited user rights and versioning can become hairy.

• Ribbon: it may be good or bad, depending on who you talk to, but Microsoft has invested a lot into it and it'll probably be there for a while. At least the Ribbon and the improved controls in Access 2007 and later make applications look and behave in a modern way. Gone the horrible UIs of old, you can now do really pretty things with themes, HTML layouts and a modern ribbon.

Reliability

Most of the objections, especially about reliability, are simply no longer true.
A carefully designed Access Application can support dozens of concurrent users. I have a decent-size application managing Procurement/Stock/Quality/Parts/Projects for a manufacturing company that has 150 users, of which usually 50 are connected at any given time. I haven't had any corruption in years.

Of course, you must always bear in mind that Access is a multi-user file-based database, so you cannot expect it to work without risk in environments with unreliable or slow networks, and connecting to a an Access backend through WiFi is really asking for trouble, just like you wouldn't work on large Excel file over wireless either.

Maintenance is part of the lifecycle of an application. Preventive maintenance is extremely important.
Don't wait for something to go wrong: build some admin tools within your app that will help check the state of your data (make sure everything is coherent, detect invalid user inputs, etc).
Also compact the database regularly (I do it nightly after I make a backup as part of the daily automated tasks on the backend server).

Some random tips

• Make sure your front-end and back-end are split: only data should be on the backend, and the front end should be installed on each user's machine.
• From your front-end, open a permanent link to a dummy table on the database back-end: keeping an always-on connection will greatly improve performance. For the reason why, have a look at this question.
• The backend should be in a shallow network share (don't put it deep under multiple directories).
• Make sure you think hard about how you will deploy your updates to all clients automatically.
  There are many ways to do this. I developed my own but you could use ClickOnce or Tony Toews's Auto-Front-End Updater
• Use the Runtime: your front-end application can run without users meddling with its internals if you deploy the Access Runtime on their machine. It's free too.
• Don't fight the tool: Access has a certain way of doing things. Just use the tools available without going to code until you have exhausted the capabilities of the RAD environment. You may be surprised by how much you can achieve without any code at all.
• Nothing stops you from using OOP: defining classes and binding your classes manually to your forms, controlling data updates, etc. The default behaviour in Access tends to encourage quick-development and is fine for small projects, but if you suspect that your project might grow, plan for the future, just like you would with any other framework.

Access as a backend database

Now, if you want to use a front-end written in something else, say C#, it's fairly easy to use Jet/ACE drivers to connect to an Access back-end database.

• Jet is the older driver for Access and only supports .mdb files. 32 bits drivers are installed by default in Windows (still there in Win8) and can always be relied upon.
• ACE is the new .accdb format used by Access2007/2010/2013. It has increased limits and enhancements over the older version but it can still talk to an .mdb.
  You will have to install the Microsoft Database Engine on all the clients (not necessary if either a full version or the Runtime of Access 2007/2010/2013 is already installed though).
• Don't try to use 64bit versions of Access/ACE driver. It's the future but it introduces many new issues, especially if you are using 3rd-party libraries or some of your clients have 32bit Office installed (your can't mix and match 32bit and 64 bit office components).
  Similarly, don't build your C++/C#/Java/Python front-end app in 64bit mode and expect the 32 bit ACE driver to work. Keep everything in 32 bit, make things work, then test 64 bits versions if you really need to.
• Do always keep at least a single connection open to the back-end database from your code, as mentioned above.
• Ideally, try to abstract any Access-specific code to make it easier later in case you need to switch to a server-based database. You could use an ORM that can talk to different back-ends transparently, or you could at least separate your SQL queries into resource files or separate objects that can be easily replaced later.
• Depending on the load (mostly how much data need to be changed in the DB), an Access back-end can easily accommodate between 20-100 users without issues. Things will deteriorate if there are lots of insert and updates occurring all the time. There are lots of tricks that can be used to make things better and make Access scale well, but you are inherently limited by the type of application your are building and how users are accessing its data.