Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
Article Categories

Archive for the ‘Database Architecture’ Category

Recently on Channel 9

At the 2015 Microsoft MVP Virtual Conference (May 14-15) Crystal Long, Brent Spaulding, and Julian Kirkness presented an information packed session on Access, Access Web Apps, and connecting Access desktop applications to Azure servers.

Channel 9 is a Microsoft community site for Microsoft customers created in 2004. It has video channels, discussions, podcasts, screencasts and interviews with Microsoft. Wikipedia

If you are interested in expanding your knowledge and understanding of the power of Access, the video of this session is a must watch. In it you will see an introduction to data management using Access, a demonstration of a working Access Web app, and a tutorial on connection an Access Desktop frontend to a cloud-based Azure backend.

Let’s Split

The term ‘split database’ seems to strike fear and trepidation into the hearts of people just getting to learn about creating Access applications. This article is for Soma, who raised the question privately with me. Thanks for asking Soma, I hope these few notes help you to a better understanding of what a split database is and why you should split it.

What does ‘split database’ mean?

An  Access file (.mdb 2003 and earlier, .accdb – 2007 & 2010) can contain both user interface and the data of the database. Think of the user interface as being everything you need to display data to the user in useful and meaningful ways, everything, in fact, except the actual tables and relationships between them.

A split database architecture separates the user interface from the data. In Access, the user interface ‘lives’ in a file known as the frontend. This file contains all of the application’s forms, reports, and queries. The backend, on the other hand, is a second file that contains the tables and relationships of the database.

Why should a database be split?

Separating the user interface and data storage functions into two separate files makes the database easier to manage and greatly simplifies the enhancement and deployment of frontend modifications.

In a multiuser environment, a split database is mandatory so that all users can share and work with the same data. That is because their individual copies of the frontend all link to the same backend. To put it in somewhat techy terms, one data source serves all users of the database. If each user had his or her own copy of the data, managing changes to the data and keeping all copies of the data ‘in sync’ adds a whole new layer of complexity to managing the data.

However, even if there is only one user, a split database still makes a lot of sense. That is because you can continue to develop and enhance (and replace) the frontend as needed without jeopardized your valuable data. In my own applications, for example, I have a frontend that I use on a day to day basis. I have a second, development, version of the front end where I try out new ideas for form and report design. The development frontend is linked, not to my live data, but to a test dataset that I copy from my live data from time to time. When I am satisfied that the development frontend is working correctly and has been properly tested, I put it ‘into production’ by linking it to my real frontend. (Incidentally, UtterAccess moderator Cybercow has posted a nice utility for switching a frontend’s between test and live data in the UtterAccess Code Archive

How do you split a database?

Generally, splitting a newly developed Access database is quite straightforward. Access includes a Split Database wizard that will move the original database’s tables and relationships to a new file. There is also the Linked Table Manager to allow you to manually link a frontend to the backend when necessary.

This article is intended to be a brief overview of what it means to have a split database. The Access help files contain more complete descriptions of the concept and how to go about splitting your database. You might also what to do an advanced search for ‘split database’ at UtterAccess where you will find many questions on the topic posted by many new members over the years.