Reconciliation Ms Access records VBA and generate error message

Question

I have got a requirement to reconciliation the records between excel sheet & MS access 2010 table with same data in different columns.. Need to validate the excel sheet based on the values from MS access with the help of unique id and to generate the error message for that unique id like below format in MS access reports.

Currently I have created one table based on below schema and mapped it to reports.. Now I need to compare the records and populate the error message in below table..

I'm new bie to VBA , Please help me with the best approach with no performance tolerance like avoiding much looping..

Table Schema: Employee

Employee ID, Employee Name, Employee Salary

1,Tester,5000

Excel Sheet Schema: Employee

ID, Name, Salary

1,Tester,4000

Required Output Report: Ms ACCESS 2010

File Name, Unique ID, Column Name, Difference

Employee, 1, Salary, access salary <5000> is different from excel sheet salary <4000>

Please edit your question to provide a lot more detail. What is the structure of the source Access table? What is the structure of the data in the Excel sheet? What do the data values look like? (That is, show us some representative sample data.) Have you at least gotten as far as creating a Linked Table in Access that points to the Excel data so you can query it? — Gord Thompson
Thanks Gord Thompson, I'm not sure about Linked table.. we are just comparing data in excel to access table for reconciliation.. please help me to achieve this through VBA. — userece25

Gord Thompson Gord Thompson · Accepted Answer · 2013-11-21T22:52:31

You can achieve your desired outcome without using any VBA at all. Start by creating a Linked Table in Access that points to your Excel data. Detailed instructions can be found here:

Import or link to data in an Excel workbook

I named my Linked Table [ExcelData], so when I open it in Access it looks like this:

To perform the reconciliation all we need to do is create a query like this:

Note that the Field: value for the fourth column is

Difference: "Access salary <" & [Employee].[Employee Salary] & "> is different from Excel sheet salary <" & [ExcelData].[Salary] & ">"

For reference, the entire SQL statement for that query is

SELECT 
    "Employee" AS [File Name], 
    Employee.[Employee ID] AS [Unique ID], 
    "Salary" AS [Column Name], 
    "Access salary <" & [Employee].[Employee Salary] & "> is different from Excel sheet salary <" & [ExcelData].[Salary] & ">" AS Difference
FROM 
    Employee 
    INNER JOIN 
    ExcelData 
        ON Employee.[Employee ID] = ExcelData.ID
WHERE (((Employee.[Employee Salary])<>[ExcelData].[Salary]));

That query returns the following:

Reconciliation Ms Access records VBA and generate error message

1 Answers