Connect/Compare Databases-Excel Macro

For comparing records between two tables in same database, or between two different Oracle databases, we may use the “MINUS” keyword in the query. But, if the databases under comparison are connected in different servers or any repositories, we can use simple Excel VBA script where the connection strings need to have the details of the repositories.

Steps to connect to an Oracle Database through a Macro

Here, we cover how to connect to an Oracle database through an Excel macro and based on the query, manipulate the tables in the database. In order to establish a database connection through a macro in Excel, below are the requirements. In this article, connection has been made for Oracle 11g database.

Install the Oracle client for Oracle 11g database, add the tnsnames.ora file path to environment variable, open the tnsnames.ora file and add a description for XE data source. Once done, create a new user for Oracle 11g database.

Once all of the above initial steps have been completed, open a blank Excel workbook and write the VBA scripts.

Steps to open a VBA editor and create a macro in Excel:

1. Open a blank excel
2. Press Alt+F11 to open VBA editor in Excel
3. Click ‘New Module’ in the editor
4. Go to Tools -> References and ensure that below options have been selected from the list
5. Create a sub function as below:

6. Declare variables for establishing database connection and record set.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

7. Add a step to open the record set with a query as below:

8. Then click the run button

This will retrieve all the records from the table specified and will populate the Sheet1 (default sheet) of the excel workbook. We can use any query with recordSet.Open to work on tables of a database.

The same connection script can be used to connect different oracle databases. Please refer below link to get the details of connection strings for different databases.

https://www.connectionstrings.com/oracle/

Steps to compare records from different Oracle Databases through an Excel Macro

Using the connection steps, connect the source and target databases. The recordset syntax will help us to retrieve the records of the tables.

The retrieved records will be populated in Sheet 1 and Sheet 2 of the workbook respectively, using below syntax.

The “Do While” loop is to get the column headers and the “While – Wend” loop is to retrieve the records and populate the Sheet1. Any looping type can be used, if working.

Create similar loops for populating Sheet2.

Finally, below script can be written to compare Sheet1 and Sheet2 and populate the PASS/FAIL result status in Sheet3. The comparison will be record-record.

The Output sheet will look like: