Access Hacks

Hack #62. Use Word to Compare Data in Two Access Tables

Look for discrepancies the easy way, using Word's Document Compare utility.

Sometimes, you have to compare data in two Access tables. Usually you do this when you have one table that derives from two different copies of the database. The data might differ between the tables; for example, some data has been updated in one table, and now you need to uncover the discrepancies.

You can do this in a couple of ways. You can use some queries, but if there are many fields, query design could be difficult. Another option is to write code to read through both tables and identify the differences. This works but it also takes a bit of time to get the code working correctly.

Here's a great alternative: Word has a built-in feature that compares two documents and highlights the differences.

The first thing you need to do is export the Access tables as text files. Word then uses these to run a comparison. Figure 7-15 shows the two tables already saved as text. As you can see, they appear identical.

Figure 7-15. Two tables saved as text files

In Word, open one of the text files. Then, use the Tools → Compare and Merge Documents menu item to browse to the second text file. As shown in Figure 7-16, you have options for how to compare and merge the documents. I always choose "Merge into new document." That way, I know the original files aren't ...