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

Hello Andy, I didn’t big progress , but now I’m stuck on inserting data table (excel table) inside access with Insert activity. Since I have IN and Language as headers , and they are restricted words inside Access. I manage to bypass this error , but renaming the headers inside Excel with double quotation like “IN” and “Language” , but when I check what is inserted those 2 columns are empty and don’t fill them with data from my excel. Can I import the data from excel with SQL and not Insert activity from Access ?

Hi,

You can try using Import Wizard in Access and try to export the excel in. If that works, you can save that as a template and run that from RPA.

Thanks

Hello Andy,

Using the import wizard and importing the table works it’s importing all the fields and column without issue even IN and Language , but I need to do that inside UiPath with access activities without opening the database since I need to do this for a lot of databases. and I don’t want to use click activities to open each file and so on. I have tried to export the table from Access in Excel format and then use insert activity , but no luck the same issue with those fields…

Sounds like something that should be done in Access, not with an automation. Always ask the question “is RPA right for this?”

Hi @Shwapx - It would be tough to diagnose without actually seeing what you are doing. This insert part must work.

I was thinking if there is any AutoCommit setting that may have been disabled. Or if the Access file isn’t saving changes after the import. Is this something you can look into closer. Maybe something is missing?

@postwick - You are right, but I see why @Shwapx needs to repeat this process across multiple Access DBs. I did work with a company that managed its business with almost 100 MS Access databases - all identical but one for each client.

It was a terrible situation to be in if something changed in one Access DB and we had to make identical changes to all the other databases.

Hello Andy,

Yes insert is not working since I have restricted words as my column headers both words are IN and Language.

I have tried to make them like Microsoft suggest with , but don’t work as well. Strange part is if you use the regular import wizard is working as intended. Maybe then access know how to bypass this. The thing is if I build an SQL with execute SQL activity will work if I put those brackets , but the issue is that you cannot use excel table in regular SQL activity you need to use Insert access activity to insert any table outside access.