Argee's Access Blog

The Microsoft Office 2010 Engineering Blog has announced a May 12, 2010 worldwide launch date for “the 2010 set of products, including Office 2010, SharePoint 2010, Visio 2010, and Project 2010” for business users. Office 2010 will be available for consumers on-line and in retail outlets in June 2010.  If you want to have an sneak preview look at what is coming you can download the beta at www.office.com/beta.

I have been poking around the Access 2010 beta for the last while and I think it is going to be a nice version to work with. In case you are wondering, the Ribbon stays. In fact, I believe that the Fluent User Interface as the Ribbon is officially, will now be found throughout the Office suite. The Office Button has been replaced with a File tab on the ribbon. This tab leads to the backstage of each application.

Quote of the Day

Perhaps the most valuable result of all education is the ability to make yourself do the thing you have to do, when it ought to be done, whether you like it or not; it is the first lesson that ought to be learned; and however early a man's training begins, it is probably the last lesson that he learns thoroughly.

- Thomas H. Huxley

The single-most exciting new development as far as Access is concerned is that it is now possible to develop Access Web Databases and publish them to SharePoint. Ryan McMinn present this new development in this video.

If you are interested in general Office development, have a look at John Durant's presentation.

Pej Javaheri and Steve Tullis present Excel and Excel Services 2010 developments in this video.

All in all , Office 2010 looks pretty exciting with lots of new ‘stuff’ to learn but with big paybacks for taking the time you will spend on learning.

As a design principle, I apply a naming convention when creating new database objects. For example, field names begin with a lowercase letter. Recently, I was putting together a small application in Access 2010 beta so that  I could gain a little familiarity with the new Access version that will be released later this year. In a weak moment, I accidentally named some of the fields in a new table with uppercase first letters.

In the grand scheme of things, this is not a particularly serious problem but inconsistently formed names lend an unprofessional look to an application. When I tried to correct the names I discovered an inconvenient quirk. After replacing the first letter of each field name with its lowercase equivalent, saving and closing the table and then re-opening it again in design view, I found that the first letter of each field name had changed back to uppercase.

No matter what I tried, I couldn’t get the lower-case letter to ‘stick.’ That’s when I turned to my favourite forum for help. Thanks to UtterAccess VIP member (and Access MVP) datAdrenaline, I quickly had a reasonably workable solution.

Quote of the Day

I have never been especially impressed by the heroics of people who are convinced they are about to change the world. I am more awed by those who struggle to make one small difference after another.

- Ellen Goodman

All I had to do is change the first letter of each field name I wanted to modify, save and close the table, re-open the table and replace whatever I had entered as the first letter of the temporary field name back to the lowercase letter with which I wanted to start the name. Step by step this is the method I applied:

• open the table in design view
• pre-fix each field name with a single letter
• delete and replace the original uppercased letter
• save and close the table
• reopen the table in design view
• remove the leading character for each field to correct name
• save and close the table

This problem occurred specifically in Access 2010 beta so it may be a non-issue when the new version is released but if you run into similar situations perhaps a similar approach will help you out of a bind “when all else fails.”

New articles in Access Wiki

Links

CamelCase

Access: What is it?

Quote of the Day

Real, constructive mental power lies in the creative thought that shapes your destiny, and your hour-by-hour mental conduct produces power for change in your life. Develop a train of thought on which to ride. The nobility of your life as well as your happiness depends upon the direction in which that train of thought is going.

-Laurence J. Peter

By the end of the day, the Access Wiki  will have been ‘open for business’ for two weeks. The body of Access knowledge that the site  promises to be is slowly building. As of this moment, there are 26 articles published in the wiki with articles including such topics as Normalization, Sharing (Access Databases through splitting), and Error Handling. Access Wiki content is expected to grow as UtterAccess members continue to write and publish articles. The wiki features a table of contents and an index if you want to look over a list of topics.

Meanwhile, on the forums side of UtterAccess, there is a whole new ‘look and feel.’ The new appearance is similar to what you will see one the Wiki side.

If you haven’t yet visited UtterAccess.com Forums or the Access Wiki, why not check them out now and see if you don’t agree that UtterAccess is the only source for all of your  Microsoft help needs. The wiki and forums are freely available to anyone on the World Wide Web but only registered members can download code samples from the forums or write and edit wiki articles. Membership is free and confidential. Signing up will give you access to thousands of downloads and the opportunity to contribute your own articles to the Access Wiki

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.

Gord over at UtterAccess is putting the finishing touches on an enhancement to the UtterAccess forum that will be as significant for the Access community as was the introduction to the web of UtterAccess itself almost eight years ago. I’m afraid you will have to wait for the public launch to learn more details but I can guarantee that you won’t be disappointed. At UtterAccess, we pride ourselves as the only source for all of your Microsoft Access help needs. If every you had any doubt about that claim, this new feature will convince you that UtterAccess is what we clam and much, much more.

Clint Covington posted  Access 2010 deprecated features and components in the Access Product Team Blog yesterday that certain features have been deprecated in Access 2010:

• Calendar Control
• Snapshot Format
• Data Access Pages
• Paradox, Lotus, and Red2 ISAM’s
• Replication conflict viewer

If you have applications developed in older Access versions that you want to port to Access 2010, read Clint's article to see what effect these changes will have on your application and some suggested workarounds.You should also keep these deprecations in mind for any new applications you may develop in pre-2010 Access versions.

Quote of the Day Not only is the universe stranger than we imagine, it is stranger than we can imagine. -Sir Arthur Eddington

Using forms to control routine data entry should be standard operating procedure for any non-trivial production Access database. In plain language, don’t give users (even yourself when you acting as a user and not the developer) access to the raw tables. Provide instead forms for entering, viewing, and editing records. In this article we will look at how to use two simple form event handlers to automate recording of when a record is created and when each record was most recently updated.

Event handlers are the key to adding custom functionality to applications. In this case, we want to keep track of when a record is created and that date it was last edited so we will need to work with the Before Insert and Before Update events. First, a little background.

When you create and edit a new record, the record does not exist in a table until you do something to cause the record to be saved. You are actually editing a memory image of what will become the new record when it is saved. Actions like moving to a different record, or pressing Shift-Enter initiate the save (or insert) process. As its name implies, the Before Insert fires before the record is inserted into the table.

To be completely accurate, the Before Insert event fires when the user types the first character of a new record (see msdn article). At this point the record does not yet exist. This give you, the developer, the opportunity to take some additional actions by adding appropriate code to the Before Insert  event handler. When the Before Insert handler finishes, the saving process continues. Along the way, the Before Update event fires, offering another opportunity to do something in addition to simply saving the record.

The chain of events is quite similar for existing records which are being edited. The only real difference is that the Before Insert event doesn’t fire because we are updating an existing record. The record is already in the table so the memory image of the record doesn’t have to be inserted into the table; the table just has to be updated to reflect the changes you have made to the record’s data. So when you do something to put the save process in effect, the Before Update  event will fire before that table data is actually modified.

If you want only a date stamp (without time) the function Date() would do the trick. However if you want the date and time of additions and modifications to be recorded, the Now() function will return a serial date value which it has read from the system clock. The two event handlers are so simple as to appear almost trivial:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.DateCreated = Now
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateModified = Now
End Sub

That’s all it takes. With these two form events in place, every time a new record is created using the form, the record’s DateCreated value will be set to the date and time the user started typing data. Similarly, when the form is used to edit data the, the record’s DateModified value will be set to the time that the changes are saved. An interesting side effect of using this approach is that new records have values assigned to both fields. The difference between the DateCreated and DateModified values indicates how long it has take the user to enter data into the new record.

Keep in mind that, while this approach might appear to be the basis of an audit trail solution, it only keeps track of record creation and last modification date and time. If you want to maintain modification history data you should use a separate, related table with a new record appended to it for each record creation/modification/deletion action.

Download the sample database from here. This is a zip compressed file contained the example in both Access 2000 and 2007 format.