Argee's Access Blog

Quote of the Day   The most important thing in the Olympic Games is not winning but taking part; the essential thing in life is not conquering but fighting well. -Pierre de Coubertin

The best forum in the Access community, UtterAccess, is about to get even better.  Tomorrow, The Access Wiki will take its place alongside UtterAccess Discussion forums. This is the most significant enhancement to UtterAccess since its inception some eight years ago. In the coming months and years, the wiki is expected be become a treasure house of in-depth information for Access database developers as members contribute and update original articles discussing all aspects of developing and enhancing Access applications.

Sometime overnight tonight, UtterAccess.com will go off-line and will be unavailable for a brief period. Then, with little more fanfare than the flipping of a switch, the wiki will be ‘live to air’ and the forums will be back on-line. If you have ever visited the UtterAccess site, you will immediately notice the forum’s new look and feel, even a brand new logo, thanks to UtterAccess member and moderator Mark Davis. The wiki will share the new ‘look and feel’ of the forum. UtterAccess members will be able to log in to the wiki using the same user id and password as they use for the discussion forums.

What is a wiki? A wiki (pronounced /ˈwɪki/ WIK-ee) is a website that allows the easy creation and editing of any number of interlinked web pages via a web browser using a simplified markup language or a WYSIWYG text editor. Wikis are typically powered by wiki software and are often used to create collaborative websites, to power community websites, for personal note taking, in corporate intranets, and in knowledge management systems. (Wikipedia, the free encyclopedia)

The UtterAccess Access Wiki and Discussion Forums will naturally complement each other. Discussion Forums provides a question and answer peer support environment where members can raise and respond to specific problem oriented questions. The wiki will provide in depth discussions of Access related topics to which a user may point when responding to questions. Access Wiki readers will turn to the Discussion Forums from time to time when they need help in understanding topics discussed in the wiki. Together Access Wiki and Discussion Forums will be a win-win partnership in Access education, re-affirming that UtterAccess.com is the only source for all of your Microsoft Access help needs...and more!

I’d like to thank UtterAccess.com Owner/Administrator Gordon Hubbell for keeping UtterAccess in the forefront of the Access community over the years and for his tireless efforts along with help from Microsoft’s Clint Covington and Kerry Westphal over the past couple of months to bring the Access Wiki to fruition.

As soon as the relevant links are available, I will be publishing a follow up article.

Congratulations Gord and UtterAccess!!

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 advances search for ‘split database’ at UtterAccess where you will find many questions on the topic posted by many new members over the years.