Microsoft Releases Office 2010 SP1

Office 2010 Service Pack 1 has been released by Microsoft.  See MS Knowledgebase article: http://support.microsoft.com/kb/2460049.  The service pack can be downloaded from the article as well.

The main improvements to Access appear to be in the area of bug-fixes and stability.  There are only three improvements listed in the KB article, but in the downloadable fix list there’s a much longer list of fixes.

Everybody has different priorities, but to my mind, here are some of the more important problems that were fixed:

• Access does not activate or return the user to the correct Ribbon tab for a previously opened database object when the user returns to that object.
• Access Wizards are not loaded correctly when "Disable all controls without notification" is selected in Trust Center.
• The program crashes when you apply a sort to a query that is based on a multi-value field.
• "Reserved error -5500" occurs when you try to run a cross-tab query that would generate null values in the column names of the query.
• "Object invalid or no longer set" error occurs when you try to use an ALTER TABLE query to change a field type or size.
• You cannot relink tables in Access databases that have linked tables to other MDBs/ACCDBs that cannot be found
• The file format that is displayed in the title bar for Access 2010 databases is "(Access 2007)."
• Incorrect data is displayed when a user's query has a list that includes a combination of GroupBy and either OrderBy or Where
• "Invalid precision for decimal data type" or results are truncated when the user runs a crosstab query.

There are also a lot of “crash fixes”, all of which are important.

Should I upgrade to Access 2010?

My take is that Access 2007 will become the Access 95 of the 21st century, that is, an interim release that few people will use once 2010 comes out.

I say this, because a number of features that were introduced in 2007 aren't really useful until 2010. For instance, I consider multiple-value fields (for tables) and Layout View (for forms) to be either useless or down-right dangerous in 2007. However, with the introduction of Web Databases in 2010, both of these features become necessary. I would never use either in a client database (that is, one that opens and runs in Access on the desktop) but I would use these in a database that runs in a web browser.

Access 2007 introduced a new type of template, that is more useful than those in earlier versions, but 2010 introduces another new type of template called Application Parts, which is meant to incorporate generalized functions into an existing database application. To my mind, that's more useful than the kind of template that creates an entire application from scratch.

The result is that many Access 2007 features were really aimed at 2010. Once 2010 comes out, I expect I'll use that exclusively.

.

A Business Case for Micrsoft Access

I read the following article some years ago and just recently ran into it again. It details how Access can be used as a part of an organization's over-all business strategy.

Database Evolution: Microsoft Access within an Organization's Database Strategy
by Luke Chung, President of FMS, Inc.

Here's a part:

Abstract

There has been a lot of confusion over the role of Microsoft Access within an organization. Sitting between the power of Excel and client server databases, Access extends from simple end-user tasks to mission critical operations. This paper hopes to cover the issues surrounding Access:

• Why it's become problematic in large organizations including the Sarbanes-Oxley Act (SOX)
• Where it's appropriate to be used, and
• Where it's not

It also focuses on the overall principle that most Access applications that become mission critical did not start out that way, but evolved into that role.

Software applications share many similarities with biology and Darwinian forces. Some applications evolve and survive, while others go extinct. Anticipating, rather than fighting, the inevitable process of database evolution and natural selection is the key to using Access effectively within an organization.

Read the rest of it here: http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp.

It's well worth it.

.

What is Microsoft Access?

Microsoft Access^® is the most popular database product on the planet. People argue about what this means exactly, but it is clear that more people use Access (the actual product) than any other database management system.

But many people don't realize that Access is not just one product, but a suite of tools integrated into one product. In other databases, these tools are separate programs, some of which must be purchased from a third-party. Together, these tools make Access one of the most powerful database products in existence.

Now, some may scoff at this. They would say that Access is just a tinker toy compared to a "real" database like Oracle or SQL Server. But that is just ignorance speaking. It is true that the database component of Access (the Jet database engine) is limited in terms of size, speed, and number of users. However, the Jet engine is only one part (and not the most important part, in my opinion) of Access.

So let's look at the component parts of Access:

First of all, as I already mentioned, there is the Jet database engine. In fact, there are two different versions of Jet. There is the traditional version (mdb) which has been around since version 2.0. With Access 2007, there is a new version, the Access Database Engine (accdb). Although it has limitations, it is nonetheless a fully functional relational database management system.

Secondly, there is the Access User Interface (AUI). This is the part of Access that the user sees when they open a table. With the AUI, a user can create tables, modify tables, add data, modify data, and create filters to view data. In SQL Server, this functionality is done in the SQL Server Management Studio (SSMS) or the older Enterprise Manager. In fact, the original Enterprise Manager was patterned after the Access UI.

Third, there is the Query Builder. This is an ad hoc querying tool. Queries can be built in the graphical user interface (gui) or directly in standard SQL. Access does not have "Views" like SQL Server does, but it does have "Select queries" that server the same function. In addition, Access has Data Definition Language (DDL) queries that create and modify table structure, Data Manipulation Language (DML) queries that display or modify data. In SQL Server, you would need a tool like Query Analyser or SSMS to accomplish this.

Another type of query Access uses is the Pass-through Query. A pass-throrgh query bypasses the Jet engine altogether and passes the query on to a linked source like SQL Server. The versatility of Access connet to and manipulate data from various data sources is truly astounding.

Fourth, Access has a built-in application generator. This itself consists of several parts.

1. Forms builder. Access allows you to create form in much the same way as Visual Basic. However, Access forms are much easier to create and the controls available are specific to database development.
2. Report builder. At some point you have to report the data in your database. In Access, you do this in the report builder. In SQL Server, you would have to use a separate product like SQL Server Reporting Services or Crystal Reports depending on how you want them published.
3. VBA modules. All database applications require a procedural language at some point. There are just some things that can't be done in SQL. In Access, this capability is provide in Visual Basic for Applications modules, both general (global) modules and programming behind the forms and reports (class modules).

Creating an application in another database, SQL Server for instance, would require multiple products: SQL Server as the data engine, SSMS to create and modify tables, Query Builder to run ad hoc queries, Visual Basic to create the application, and Crystal Reports for reporting. In Access, you get it all in one.

So you can see that Access is really a Rapid Application Development (RAD) product than it is a database. In fact, Access might more accurately be called an application generator with a complementary database provided.