When I mention the term Microsoft developer, it’s likely to conjure up images of someone working into the wee hours of the morning in Visual Studio.NET® (VS.NET). If you are up to speed on the latest features in DB2 Universal Database (DB2 UDB), you’ll know that if the back-end data to VS.NET was from DB2, the developer would have access to key

features that aren’t available in SQL Server, such as CLR-based stored procedures, Explorer window filtering, and more! However, there is another class of Microsoft developers whose numbers are into the millions: 4 million+ to be more specific.

Microsoft Access® (Access) has long been a reporting, application development, and database product all rolled into one, loved by power users, and at times loathed by database administrators (DBAs) in the Information Technology (IT) department.

Most large enterprises have a significant investment in Access applications and they don’t even know it. Why? You likely don’t have to go to IT and fill out a requisition order for hardware or software licenses. (You can sneak it “in-the-back-door”, as they say, because the software is readily available on most desktops?) You don’t require a DBA to use Access; the opposite is true, really. Power users can invest a couple of weeks of hard work and build a decent application or data repository to manage business data.

So far so good, right? Well, perhaps not …

What often happens is that data or the application outgrows Access and the schema is subsequently passed to the IT department to implement on an enterprise-class relational database management system (RDBMS). One particular issue I am seeing with customers today is around compliance and regulatory issues. Sarbanes Oxley (SOX) and the Basel II accord are key examples of how data that is driven by lines of business (LOB) has to be moved to more rigorous, secure, and streamlined data stores. It’s really not an overstatement today to note that ROI can stand for “remaining out of incarceration” as much as its more traditional “return on investment” translation. The point is that there is a lot of data now, and not having control of it all can cost more than just your job.

Customers often tell me that they are considering Microsoft SQL Server to host their Access data because they need to maintain the application, reporting, and other infrastructure built on the Access platform. Quite simply, customers either don’t have the skill set to move to a different platform, or they feel they have too much legacy investment to give up the countless hours of invested in their the old platform.

Obviously, SQL Server is a natural fit — but it doesn’t have to be the only choice. In this article, I’ll show you how to generate reports, build forms, and call DB2 business logic from your Access workbench. This is really a continuation of the DB2 application developer mantra: “You choose the API and the programming model, and we’ll ensure you remain productive”. In other words, make database decisions based on data.

When you take into account that you can host your Microsoft Access application data on a DB2 platform, along with the fact that the list price of a DB2 solution can be as much as 75 percent cheaper in a highly available environment, it’s easy to see why DB2 is a perfect choice for you to migrate your Access schema to. The key here is that the skill set and simplicity behind the building of the existing reporting or application infrastructure can remain intact, enhanced, or migrated. The choice is yours!

Getting to DB2 Data from Access

In order to access any data from Access that does not reside in the Jet engine (this is the name of the database engine that underpins Access), you typically use a linked table. Whether you’re accessing SQL Server or DB2 data, the process to set up a linked table is pretty much the same.

To set up a linked table:

1. Select Tables, right-click, and select Link Tables…

2. Select ODBC Databases() from the Files of type: drop-down box

Note: This method requires that your database be registered as an ODBC data source. When you set up connectivity to a DB2 database, you can have this done automatically for you. Alternatively, you can set up your DB2 database for ODBC using the Data Source (ODBC) application in the Administrative Tools folder. (You pretty much follow the same steps for both SQL Server and DB2.)

3. Select the data source for the database you want to connect to.

You can see in my example that I’ve cataloged the DB2 SAMPLE database as ODBCSAMPLE. Notice the other vendors’ databases (including SQL Server) that are cataloged on my system.

4. You will be prompted to connect to the database. If the connection is successful, Access will enumerate all of the tables in the specified database. Select the table you would like to link to.

5. You can optionally set a primary key when the linked table is created. When you are finished, you have a linked DB2 table.

6. Double-click your linked table to verify that you have access to the underlying data (which, in this case, is coming straight out of a DB2 database).

As you can see, you’re accessing DB2 data. It was that easy.

What’s more powerful is the fact that this data could have resided on a DB2 for z/OS® database, in an IBM WebSphere® Application Server message queue, or in an IMS data source, or in so many other places. DB2, along with DB2 Information Integrator (DB2 II), is able to present all of these disparate data types as simple tables that get exposed to your applications or development environments as simple DB2 tables. The developer has no idea: they just see the data — and that is the whole point.

Building a DB2 Query in Access

Now that you have a linked table, you can leverage this data source to build all sorts of Access artifacts.

To build a query:

1. Click the Query icon on the left side of the Access toolbar and select Create query by using wizard. If you choose the Create query in Design View option, you can create a more complex query using the Microsoft Query Editor.

2. Step through the Wizard. You can choose simple restrict or projection operations for your data, and some handy built-in summary options are also available. Notice that I select Table: PAULZ_STAFF (the linked table) in the Tables/Queries drop-down box.

As its name indicates, you build simple queries using this Wizard. If you wanted to build a more complex query, you would use the query editor.

When your query is built, the results of that query are automatically returned to Access and the query is saved. You now have a query that you can refer to in your forms, reports, and so on, called PAULZ_STAFF Query.

Building a Form that Uses DB2 Data in Access

One thing that concerns IT managers who are looking to move data managed in Access to an enterprise RDBMS is the untracked investment in Access-built forms. The good news is that you leave this logic for those forms in place (preserving your investment), while at the same time ensuring that the data (and its schema) resides in a more robust and scalable database management product, like DB2.

To create a quick reporting form:

1. Click the Form icon on the left side of the Access toolbar and select Create form by using wizard. If you choose the Create form in Design View option, you can create a richer and more complex form using the Access form designer.

2. Step through the Wizard. Select your linked table the same way I did in Step 2 “Building a DB2 query in Access”.

If you had previously created a query and wanted to use the result set of that query for this form, you could select that query (instead of the table) from this list as well. This could be particularly useful if your query joined two or more tables and restricted the result set to specific rows of data.

3. Access gives you a lot of options with respect to the way you want your form to display the data. To keep things simple, I selected Columnar. You could create richer and more complex forms with this Wizard by selecting other options (like PivotChart).

4. Use the remaining steps in the Wizard to specify the formatting for your form. When you finish, you’ll have an Access data form that is populated with DB2 data.

If you have the right authorities, you can write-back into the database using the  button on this form.

For example, I added Kelly to the SAMPLE database through the Access form I just created:

And it inserted the new data back into the SAMPLE database (as expected) on the DB2 server:

I am sure that last step caused a couple of DBAs’ hearts to skip a beat. Keep in mind that you control who can and cannot read, write, update, and delete data in your database. I am just showing some examples here.

And a Whole Lot More

There are more artifacts that you can build in a very similar fashion to the one I used to create a form and a query in the previous sections.

For example, you can create a report with summaries and some basic statistical analysis. The following report, generated from Access, takes all the employees, groups them by department (using an interval of ten for each set) and returns the core and statistical data defined in the Wizard:

You can also create Web-based forms with mere clicks of a button:

Aside from standard data access, forms, queries, and reports, you can also call stored procedures or Web services that are hosted in DB2. The following picture shows an example of calling the DEPT20 stored procedure (I used a Pass-Through query in the Microsoft Query Editor tool):

While this was a simple stored procedure, it shows that Access can leverage multiple artifacts in DB2, not to mention that it could be a good idea for DBAs to create “canned” queries using stored procedures for their Access developers. This way, you don’t have to unnecessarily grant table-level privileges, you can lock in access plans, and so on.

Wrapping it Up

It’s a cliché, but with DB2, you can truly “have your cake and eat it too”. If you’re a shop with a heavy investment in reports, forms, or applications built on the Access platform and you’re looking for an enterprise-worthy data store, you need not look any further than the DB2 family. The point is that you can actually choose a DB2 solution; you don’t have to use what you may have thought you did before reading this article.

In a future article, I’ll discuss using the world’s most popular business intelligence (BI) tool, Microsoft Excel.

--

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.

Trademarks and Disclaimers

DB2, DB2 Universal Database, IBM, WebSphere, z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

© Copyright International Business Machines Corporation, 2004. All rights reserved.

The opinions, solutions, and advice in this article are from the author’s experience and not intended to represent official communication from IBM. Neither the author nor IBM is liable for any of the contents in, or the actions that result from, reading this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.