Click here to Skip to main content
15,797,721 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I have a .net winform that I use to insert and update data in a few ACCESS tables.
the same system suppose to be installed in 3 computers and one in a while, I want to merge the data from all the access DB files to one of them.

Beside the fact that I need to merge the tables (they are all formated the same way), I need to insert only new rows (meaning I can't have duplicates)

Is there a way to do that?

thank you

1 solution


One way is to create a simple application for the purpose. This will connect to the ACCESS databases in pairs and then, for each table, it will do something like the following:

INSERT INTO Left.Table(Columns)
SELECT Columns
FROM Right.Table
   (SELECT PrimaryKey FROM Left.Table)

Clearly, the above is pseudo-SQL; I haven't played with ACCESS for ages, and haven't connected to it ever from a .Net app. In particular, my shorthand 'Left.Table' and 'Right.Table' almost certainly will not translate directly into anything ACCESS accepts (if you were working with SQLServer there would be a variant that would resemble that shorthand; but you're not).

You may even have to use an intermediate .Net object collection, and separate the above query into two (or even three) steps: (1) get the set of left primary key values); (2) get the set or right rows whose primary keys are not in the left set; (3) insert those rows into the left database.

Once you've done this for one pair repeat it for the result, plus the next unprocessed database, until you've merged them all.

Another approach that I think you could do, in MS Access itself, is to select one database as the merge destination (making a copy of the original, first), then: link to each of the tables in one of the other database, and run the above pseudo-sql (where the 'right' table is the linked table'). Delete the links, link to the next database, and repeat. This is the same as the .Net variant, but perhaps is simpler because you're not creating a whole app just to move data.
Share this answer
danait25 15-Feb-12 0:17am    
that sounds good- I'll try it.
But I still have a question. the first DB is already connected to my application so that's not a problem, but the others will be on a disk on key or somewhere on the computer, so how can my app recognize where to find the other DB if it's location is not consistant and don't have a relevant connectionstring in my app?
Chris Ross 2 15-Feb-12 5:35am    
What I had understood from your question (perhaps I was wrong?) was that this task of merging the various databases would be a one-off event. With that in mind I had assumed that you would write a small, distinct, app which existed just to do the merge - and you would have all the various database files already at your disposal, to copy into a temp directory, or whatever.

If you need this behaviour built into your main app, perhaps making 'merging databases' a user-selectable function on a menu would be appropriate. When they pick this function, it prompts the user to specify the location of the second database (and would merge that one into the 'first' database - which is the one the app already knows about and, presumably, is configured to use on startup).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900