Exporting Database from Access 2007 to Sql 2005

Question

Fellow Developers I have been working on creating a Database with SQL Server 2005, now I finished that step, its a huge and massive one. Now the company wants me to export their data from their Access 2007 db to this sql 2005 database I created.

I know that I will be using field mapping, but is it actually possible to do that ?

Any Suggestion ?

Have you considered the upsizing wizard and once its in SQL server moving the data as you'd like — Conrad Frix
– Conrad Frix, Commented Jan 30, 2012 at 18:32
@ConradFrix That would be good solution provided that both databases are identical. If the databases are identical I can post a script that I created that will build the INSERT statements for you. You would need to import the Access data into the SQL Server instance or create a linked server first. — Mark Kram
– Mark Kram, Commented Jan 30, 2012 at 18:58

Mark Kram · Accepted Answer · 2012-01-30 19:31:16Z

2

A major portion of my job is converting data from one data source into another. I am actually doing a Filemaker Pro to SQL Server conversion right now. I have tried some of these Automatic solutions and they are hit or miss depending upon the complexity of the data involved. Usually, I do it by mostly by hand writing scripts (I do automate some of the scripts using a VB6 application I created many years ago).

I have found that doing the scripts by hand always produces the best conversions.

edited Jan 30, 2012 at 19:31

answered Jan 30, 2012 at 18:55

Mark Kram

5,8527 gold badges56 silver badges73 bronze badges

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

2 Comments

0bserver07 Over a year ago

thnx for your replies, I'm feeling somehow stuck and down at this point since I'm on deadline! I will look into getting scripts. To be honest never got this far!

Mark Kram Over a year ago

No worries, if the two databases are identical then you maybe better off using SSIS.

DOK · Accepted Answer · 2012-01-30 18:46:45Z

2

This fellow describes how he did it. There is a tool called "SQL Server Migration Assistant (SSMA) for Access" that you can download from Microsoft. The blog also has a Word document that you can download for a step-by-step description of what to do.

You can download SSMA here and possibly just figure out how to do it without searching further.

answered Jan 30, 2012 at 18:46

DOK

32.8k8 gold badges64 silver badges93 bronze badges

1 Comment

0bserver07 Over a year ago

Thanks, this sound promising! When I googled this was my only choice, that is why I posted here! Thanks again!

Beth · Accepted Answer · 2012-01-30 18:41:27Z

1

It depends on the complexity of the import. Try using SQL's import wizard first. If that's too limited, you may need to create an SSIS package.

answered Jan 30, 2012 at 18:41

Beth

9,6171 gold badge26 silver badges44 bronze badges

1 Comment

0bserver07 Over a year ago

Thanks My friend, I will look into SSIS. its my first time to hear about this. I feel so novice at this point! Thanks again!

Collectives™ on Stack Overflow

Exporting Database from Access 2007 to Sql 2005

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related