Comparing tables in database access

Hello everyone,

I want to ask if there is a way to read 2 database access files and compare their tables.

Structure of the access files are the same with same name of the tables , just one of them have information which need to be transferred in the other file.

Example: Robot go inside both access files check first table of both files if it find new data in first access database in one of the tables transfer it to the other access file in the same table.

We need to do this to several access database files , but I guess if we found a way to do it for 2 then will be easier.

Well you need two separate connections to fetch the table data from one connection , and then do the same thing with another connection.

You then read table 1 and table 2 into DT_Table1 and DT_Table2

Then you figure out the differences between the two data tables using a DataTable Join operation of some kind (left, right depending on which direction the changes are).

Example if Table 1 has records that are missing in Table 2 then :
Then insert the difference records from Table 1 over connection 2 into the target Table2 in the second database.

If you know that the table structures are going to be identical across the two databases, then you can pass in File 1 and File 2 to the automation as input arguments.

Yes , but how do you actually can read the table inside the database, since each database have 4 tables. For example how to make it read first one from first file and first one from second file.

You might want to check the UiPath.Database.Activities Package. It isn’t installed by default if I recall correctly. You can add it to your project, and then send SELECT commands over your access connection to read the table or view that you need.

Yes I was able to connect to the Database and read the table ,but how to import the data in the next database ? Struggling to find SQL code for that.

There are general articles on MS Access on how to create SQL insert queries and pass values into them .

What you need to do is to execute those SQL queries via the UiPath SQL Libraries by looping through each source table, and each record in that source table.

Have you considered the Access Import/Export wizard as an option?
You can set up imports from source to target and have your automation execute those wizards instead.

Thanks

Here is something I quickly set up by creating a Sample Access Database. The SQL Query to insert data into a table looks something like this.

In your case you have to replace all the values to be inserted in the SELECT statement with values you have pulled from your source table.

In addition you don’t have to have the FROM CLAUSE in the SELECT statement because you will be inserting straight up values from your source table.

Therefore, with string operations, if you have your automation build a query using the values retrieved from each row of the source table, each insert SQL would translate to something like this:

insert into MY_TARGET_TABLE(column1, column2, column3)
SELECT 'Column1 value', 'column2  value', 123

Alternately, you may craft the SQL this way as well. T-SQL supports it as far as I know.

insert into MY_TARGET_TABLE(column1, column2, column3)
VALUES('Column1 value', 'column2  value', 123)

You have to go through the SQL library documentation for UiPath to see how to pass this SQL via UiPath SQL Library. This is the package normally used.

image

I hope this gets you going.

Thanks