Struggling with Access

Here’s one of the issues I’m currently working on. At work, we’ve “inherited” a non-trivial access application – maybe 20 tables, a bunch of forms, queries and reports. For now, we’re struck with the architecture, but we still have to add a bunch of functionality (new/changed tables and forms).

Getting accustomed to developing in Access was pretty quick – if you have a relatively good understanding of databases and application development, you can get some functionality working pretty quick. Some “interesting” issues, but nothing major so far.

The thing that has been bugging me (as I’m the one stuck with the development) is the “development cycle”. When you’re used to Enterprise Development in environments like SAP, this is a big shock. Development, QA and production environment? Forget about it, it’s three separate copies of the Access file. Moving functionality “up the chain”? Replace the production file with the qa file. Add a new table and a new form – no (simple) way to “move” just that part of the functionality. Push new functionality while people are using the application? Wait till everyone’s gone home … quite frustrating. And don’t get me started on multi-user handling, groups, rights …

We’ve tried to make things a bit more workable, but we’re still struggling:

• Changing data and developing the Access file at the same time Without any tricks, when you want to work on the application you need exclusive access to the file, so that no one else can change data in the application’s database. Of course, that is not tolerable if you have more than a handful of users. One workaround is to “split” the Access file into a database or backend file (which contains just the tables and the data) and application file (which contains the queries and forms). The application file has “links” to the tables in the backend file. This seems to make working on code and data in parallel possible, it’s still a bit tricky to add new tables or change existing ones, but that’s okay for now.
• Performance Especially after splitting, the performance has gone South – it is now on the verge of being unusable. All our files have to be on the network (which is not very fast, but shouldn’t be much of a problem as the file is just a few MBs). Also the variations of performance are very strange – and it has been degrading over the last days without any reasonable explanation.
• Usage Reports Just one example: A form relies on a query for its data. You can change the name of the query, but the form does not pick up this change. So the next time it is called, you just get an error. We’ve got a couple of “Query 1” or “Macro 1” or “Makro 1” etc., but are afraid to delete or rename them because we don’t know where they are used. Any “compilers” for Access code that would show us these errors or help us in renaming?
• I’ll be adding more over the next few days …

Maybe some of you have some ideas on what we could do to improve the user and developer “experience”? Let me know in the comments …