VB.net/C# Copy records from one database to another

Question

I have 2 MS-Access databases with same structure but with differents data. I want to copy records from 1 database to the other.

Example :

Database Source, table Person
Record 1:
Field 1, PersonNumber, primary key, value : 1
Field 2, CompanyNumber, foreign key, value : 1

Database Source, table Company
Record 1:
Field 1, CompanyNumber, primary key, value : 1
Field 2, CompanyName, value : Microsoft

So, there is a relation between the 2 tables on CompanyNumber.

So, if i want to copy these 2 records of the 2 tables in my destination database, i will probably have a problem if i already have a person or a company with a primary key with value 1 in destination tables.

2nd problem, all my tables have many foreign key, how to copy records from person for example if records from company are not created ?

How can i do with vb.net or C# ?

Thanks for your help

I do not see how this can be done unless there is a lot more logic that you are not telling us about. If the copy-to table has 1, Bill and the copy-from table has 1,Joe which is right? And having decided which is right, which additional records should be copied? And what if they have matches? — Fionnuala
– Fionnuala, Commented Aug 23, 2012 at 8:22
In your example, i want : 1, Bill 2, Joe No data in destination database are modified. Steve, it's not a language problem, i know the 2 :) — Pachanka
– Pachanka, Commented Aug 23, 2012 at 8:35
There is no difficulty in copying from one table to another if you ignore the primary key (which is what assigning Joe to 2 is doing). So what about the related tables? Do you ignore the keys on them? It all depends on the scale at this stage, you say many foreign keys, which is a lot of programming. You will have to match the foreign keys with both the existing foreign table and the new database foreign table and ensure that they are the same, unless of course, that the foreign table is an exact match. — Fionnuala
– Fionnuala, Commented Aug 23, 2012 at 9:07
Yeah, your example is not a problem, just an insert and ignore the primary. The problem is if Joe record has another field with company number for example and with same way if i have inserted the company in destination DB with changing the primary key — Pachanka
– Pachanka, Commented Aug 23, 2012 at 9:14

Fionnuala · Accepted Answer · 2012-08-23 09:59:34Z

1

You can easily refer to another database in MS Access SQL, the language does not matter, just the connection:

SELECT Table1.AText, Table1_1.AText
FROM [;database=z:\docs\test.accdb].Table1 
INNER JOIN Table1 AS Table1_1 ON Table1.ID = Table1_1.ID;

After that, there is a great deal of coding to check everything.

answered Aug 23, 2012 at 9:59

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pachanka Over a year ago

Hum, nice idea, i'll look this way

Collectives™ on Stack Overflow

VB.net/C# Copy records from one database to another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related