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 altered.

A new document is created, but you immediately run into a problem. Word's spellchecker and grammar checker will flag nearly everything as incorrect because the export from Access creates records with no space breaks. This is correct for the data, but not as far as Word is concerned. So, the next thing to do is turn off the spellchecker and grammar checker in Word's Options dialog, as shown in Figure 7-17. By the way, the first opened text file didn't flag any errors because it was still a text file. The new merged document, on the other hand, is a proper Word document.

Figure 7-16. Setting up the document comparison

Figure 7-17. Turning off spellchecker and grammar checker in Word

Once you can see the document for what it is, you can see places where the data doesn't match because the data is formatted with strikethroughs, as shown in Figure 7-18.

Scrolling through this data is a breeze. You can quickly see where the data is different and decide what to do about it.

Andrea Moss