Without a doubt, Microsoft Access 2007 (Access 2007) is one of the most widely used reporting and data persistence tools. Quite often, I see clients export massive amounts of data from their enterprise systems into Access 2007. They tell me they do this because they have so many applications, or information workers skills in the development of applications, built with an Access front-end.
The main problems associated with this approach are as follows:
1. There are too many copies of the data. Maintaining multiple copies leads to redundant data costs.
2. There is too much latency with the data. It takes too long to get the latest data to the people who need it. In addition, everyone sees different data with inconsistent points in time with respect to when the data was captured.
3. The solution is too complex. This much data movement requires its own life cycle: maintenance, batch jobs, and more.
4. The data is not secure. Because of its portability, data typically resides on minimally secured laptops.
5. The solution is too expensive. Numerous components lead to increased costs. These costs are often hidden in distributed organizations, but they can be massive -- core operating system licensing costs, additional data server license costs, network costs, patching costs, security costs for a whole new layer of data access, and more.
What’s more, there often comes a time when the IT staff must try desperately to rein in data sprawl and to solve the problems just mentioned. For example, governance is a great motivator for solving the sprawl of Access databases. Faced with such a daunting task, some IT departments define a mission to move Access databases to an enterprise data server, likely Microsoft SQL Server.
I’m not going to perform a competitive analysis of these data servers in this series; however, I will note that I’ve seen dedicated DB2 shops feel that they didn’t have a choice but to select SQL Server for such a mission because they would incur a productivity hit to their project development cycles. I’m writing this series to give you the confidence to let a data decision be a data decision and a tooling decision be a tooling decision.
Now consider leaving all that data in your DB2 (including a DB2 for z/OS) data server and just accessing it. After all, if your enterprise data warehouse (EDW) is built on DB2, it can scale pretty much limitlessly and is designed to handle all kinds of workloads. Or perhaps take those Access 2007 tables and move them to a DB2 data server, but keep all the application logic, reports, forms, and so on, in Access so as not to lose any productivity for your analyst and application development teams.
If you did this, you could achieve the following benefits:
1. A dramatic reduction in the number of copies of the data, which can help support the goal of presenting a single view of the business through a single version of the truth.
2. Reduced latency time with fewer input and access paths and near-real-time load and access tools providing up-to-date business intelligence.
3. A simplified architecture making the environment easier to manage for faster solution development and quicker return-on-investment (ROI)!
4. A more secure solution since data is locked in one place with enterprise-wide policies.
5. A less expensive solution. Fewer components lead to reduced costs in both infrastructure and human resources; all this results in lower total cost of ownership (TCO).
The following figure summarizes these approaches:
In a previous series, I detailed how you can solve the same challenges that occur when Microsoft Excel is used to store (rather than just analyze) data in your enterprise. In this series, I’m going to show you how your database selection has no bearing on the productivity your information workers experience with Access; specifically, I’m going to show you how to hook up Access 2007 (the latest version) to a back-end DB2 for Windows data server because things have changed since Access 2003 and versions before that.
Before you start
This series assumes that you have DB2 for Windows installed on your local machine; furthermore, it assumes the SAMPLE database has been created on your local machine too. If you don’t, you can create it by entering the db2sampl –xml –sql command from your operating system’s command prompt. To get a free copy of DB2 that you can use even in production, check out DB2 Express-C.
You can have a remote connection to the SAMPLE database as well, but you need to ensure that you have the proper authorities to catalog data servers, administer the ODBC settings, access to tables, and so on. Generally, I like readers to follow my articles using a local machine, where they created the SAMPLE database personally and have Administrator authority since they will generally have the proper authorities to complete all the steps in the article.
A comment on Access 2007 file formats
Access 2007 has two ways you can connect to external data servers. First, you can link to the target data server. When connecting to an external data source, you can use a main database (MDB) file or an Access 2007 database (ACCDB) file. The ACCDB file format is new in Access 2007 and takes the place of the MDB extension used in previous versions; I’m commenting on both of them here in case you still have a lot of MDB files in your enterprise.
Alternatively, you can use an Access Data Project (ADP). ADP interfaces are built using OLE documents that only contain the front-end presentation items such as an Access report or a form that you may have created. In the background, these objects explicitly connect to a remote data server. ADPs have been around for a while and they don’t look like they’ve been enhanced much in Access 2007. This may lead one to surmise that they are being replaced by MDB and ACCDB files, but that’s my gut feeling.
In Access 2007, MDB and ACCDB files are more useful formats than ADPs: they can link to multiple data sources, don’t have the row limits that ADPs seem to have, and more. For this reason, I’ll try to stick with these more useful formats and stay away from ADP examples.
Getting your DB2 data into Microsoft Access 2007
To use Access 2007 as a front end to your DB2 data server, you first need to create a project. By default, when you start Access 2007, the Getting Started with Microsoft Office Access launch pad opens. For this example, in the Featuring tab, select Blank Database, enter the name DatabaseJournalAccess2DB2 in the File Name field, and click Create.
To connect Access 2007 to a back-end DB2 for Linux, UNIX, and Windows data server, you need to start with a database connection. You can link up Access 2007 to DB2 using the ODBC Database option available within the External Data tab in the Table Tools ribbon:
When you select the ODBC Database option, the Get External Data – ODBC Database window opens.
You can see in the previous window that Access 2007 gives you the option to import tables and their respective data such that they persist in Access 2007. You also have the option to front-end those tables directly by linking to the data source and creating a linked table. With a linked table, the data is available to Access 2007 users, but the data resides on a remote data server. Earlier in this article, I described why storing the same data in multiple places is not good for business. You can avoid doing this by selecting Link to the data source by creating a linked table and clicking OK.
The Select Data Source window opens, as shown below. This window should look familiar to you if you’ve worked with ODBC data sources before and perhaps even if you haven’t.
Assuming you aren’t working with your database connection via a DSN file, select the Machine Data Source tab, select the target data server from the Data Source Name field (in this case, SAMPLE), and click OK.
I didn’t mention in the “Before you start” section that you must have the SAMPLE database registered as an ODBC source (or at least access to the DSN file that represents a connection to the SAMPLE database). I didn’t mention it because many times this will be done automatically for you when you create the SAMPLE database, and typically, you won’t be working with the SAMPLE database for production environments anyway. You can specify that a DB2 database be added to the Machine Data Source list shown in the previous figure when cataloging a remote DB2 data server.
For example, you can use the Configuration Assistant to add a new data server connection using the Add Database using Wizard option and optimize the DB2 database connection for Access as well as register it as an ODBC data source on your server. Alternatively, you can have DB2 create a DSN file for you to redistribute the connection information or have the connection only appear for a specific user defined on your server:
If the target DB2 data server that you want to connect to doesn’t show up in the Data Source Name list, simply click New to add it. The Create New Data Source wizard opens. Select the appropriate radio button that corresponds to the way you want this target data server to be accessible on your system. For our example, select System Data Source, click Next, select the IBM DB2 ODBC Driver (it is automatically installed when you install DB2 on Windows or a client), click Next, and then Finish. This process is shown below:
You can see in the previous figure the two IBM DB2 ODBC Drivers in the Create New Data Source window. This is because DB2 9 supports multiple-installation copies of the product. For example, in this case, I have one version of DB2 9.5 on my machine (denoted by DB295Main) and another separate installation running at the DB2 9 level. This feature is terrific, especially for migration and maintenance.
IE 7
Firefox 2.0
