MS Access to SQL Sever in C#.NET

Question

Export. We are doing the export Ms access to SQL Server.

We know how to export that table by table. What we are facing and no way to handle is that the relations which existed in MS Access between the table will lost after exporting. That is pretty annoying. Is there a way or tool that can allow us to deal with several tables one time or is there some workaround?

You wish to maintain the relations between the tables during transfer? — , May 14 '12 at 03:39

score 2 · Accepted Answer · edited May 23 '17 at 12:00

2

Since you tagged this with c#, I'm going to assume that you want to implement this yourself. Here are the building blocks that you need for this:

Create a DAO connection to your Access database (add a reference to the DAO library, create a new DBEngine and then use its OpenDatabase method to get a Database reference):
```
DBEngine dbe = new DBEngine();
Database db = dbe.OpenDatabase(pathToDatabase);
```
Iterate through the Relations property to access the relations stored in the MS Access database.

Create the relations in your SQL Server database. To do this, you can either

use SMO or

plain old T-SQL:

ALTER TABLE table1 
ADD CONSTRAINT table1_fkfield_fk FOREIGN KEY (fkfield) 
REFERENCES table2 (pkfield)

edited May 23 '17 at 12:00

Community

1
1

answered May 14 '12 at 05:28

Heinzi

167,459
57
363
519

Can you show me some examples or link which you developed this? – Karthic G Aug 29 '18 at 07:39
@KarthicG: Sorry, I didn't implement it, I just thought it through. – Heinzi Aug 29 '18 at 07:49

score 1 · Answer 2 · answered May 14 '12 at 04:15

1

Looks like Microsoft has an answer for this. There is an option in Access called Upsizing Wizard, which "Preserves database structure, including data, indexes, and default settings."

Here is the link to the KB article explaining more: http://support.microsoft.com/kb/237980

answered May 14 '12 at 04:15

Nickoli Roussakov

3,434
16
22

Thank you while there seems no mention of relations. – Admin_eric May 14 '12 at 06:21

score 1 · Answer 3 · answered May 14 '12 at 05:16

Use SQL Server Data Transformation Services (DTS) to import the data from your Access database to a SQL Server database. For more information about DTS and the DTS Import Wizard, see the "Data Transformation Services" and "Using the Import and Export Wizards" topics in SQL Server 7.0 Books Online. For an example, see the "Importing Data from an Access Database Example" topic in SQL Server Books Online.

score 0 · Answer 4 · answered May 14 '12 at 05:15

0

For migrating the Access Database to SQL Server we have many third party tools and Microsoft Assitant also there .Check this links Link 1 Link 2

answered May 14 '12 at 05:15

Hemant Kumar

4,593
9
56
95

MS Access to SQL Sever in C#.NET

4 Answers4