DB2 9 with Microsoft Access 2007

There is no doubt that Microsoft Access 2007 (Access 2007) is one of the most widely used data reporting and maintenance tools. Most of the time, I've seen clients export a lot of tedious data from an enterprise system to access 2007. The reason they told me to do this is because they have a lot of apps, and it's customary for information technicians to build an Access front-end processor in software development.

The main issues covered by this approach are as follows:

1. Producing too many copies of data, and saving them will result in a large amount of unnecessary data costs.

2. Delay of data. For those who need it, it will take too long to get the latest data. In addition, when the data is fetched, the data seen at different points in time will vary.

3. The solution is too complex. Transferring this data requires its own lifecycle: maintenance, batch jobs, and so on.

4. Data storage is not secure. Because of their scalability, data is often stored on lower-security laptops.

5. This solution is too expensive. A large number of components can lead to an increase in spending. Although these costs are hidden in different parts of the organization, their total amount is quite large-such as kernel operating system licensing fees, additional data server licensing fees, network costs, patch costs, new data access layer of security costs, and so on.

In addition, IT staff always have to try desperately to control the expansion of the data and solve the problems mentioned before. For example, a good management method is to solve the problem of database clutter promotion factors. When faced with such a headache, some IT departments take the approach of transferring databases to enterprise data servers, such as Microsoft SQL Server.

In this series of articles, I will not make an analysis of the competitiveness of these data servers; But what I want to emphasize is that I see some very monograph DB2 stores that have no other choice than SQL Server when they solve such problems, which can lead to a heavy blow to the productivity of their project development cycle. The reason why I write this series of articles is to give you more confidence in dealing with data processing, and then to make decisions decisively.

Now let's assume that all of your data is stored on the DB2 data server (including z/Os DB2) and accessed. After all, if your enterprise Data Warehouse (EDW) is built on DB2, it can expand and handle a variety of workloads without limitation. Or you can move those Access 2007 tables to the DB2 data server, but keep all the application logic methods, reports, forms, and so on in access, so that your analysis results and application development teams will lose any productivity.

If you do this, you will receive the following benefits:

1. A significant reduction in the number of copies of data, which enables the goal of providing a unique business perspective through the only data version that reflects the facts.

2. Reduced latency, which includes fewer input and access paths, and near-real-time workloads and access tools to provide you with the latest business intelligence.

3. Simplified framework structure makes the operating environment easier to manage, resulting in faster solutions and investment net interest rates (ROI)!

4. The solution is more secure, as data is blocked in areas that are subject to enterprise constraints.

5. Solutions are more economical. Reduced components can effectively save on infrastructure and human resources, and ultimately effectively reduce total cost (TCO).

The following figure outlines these methods:

In the previous article, I explained in detail how to troubleshoot similar problems when you use Microsoft Excel to store (not just analyze) enterprise data. In this series of articles, I will show you that the choice of the database will not be different because of productivity and the experience of the information technician in Access; Importantly, I will tell you how to make Access 2007 (the latest version) connected to the backend DB2 on a Windows data server. Because of Access 2003 and previous versions, Access has changed a lot today.