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? — user1321093
– user1321093, Commented May 14, 2012 at 3:39

Community · Accepted Answer · 2017-05-23 12:00:00Z

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, 2017 at 12:00

CommunityBot

11 silver badge

answered May 14, 2012 at 5:28

Heinzi

173k61 gold badges386 silver badges554 bronze badges

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

2 Comments

Karthic G Over a year ago

Can you show me some examples or link which you developed this?

Heinzi Over a year ago

@KarthicG: Sorry, I didn't implement it, I just thought it through.

Nickoli Roussakov · Accepted Answer · 2012-05-14 04:15:11Z

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, 2012 at 4:15

Nickoli Roussakov

3,40918 silver badges23 bronze badges

1 Comment

Admin_eric Over a year ago

Thank you while there seems no mention of relations.

ABCD · Accepted Answer · 2012-05-14 05:16:58Z

1

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.

answered May 14, 2012 at 5:16

ABCD

90717 silver badges39 bronze badges

Comments

Hemant Kumar · Accepted Answer · 2012-05-14 05:15:35Z

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, 2012 at 5:15

Hemant Kumar

4,6219 gold badges58 silver badges96 bronze badges

Collectives™ on Stack Overflow

MS Access to SQL Sever in C#.NET

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related