Migrating accdb to sql server 2008r2

Question

Post reply

Migrating accdb to sql server 2008r2

phineas629

SSC-Addicted

Points: 426
More actions
March 13, 2014 at 4:42 am

#284581

Not sure if this is the right place to post. I was looking for information in regards to migrating from access database accdb to sql server 2008r2. My plan would be to create the whole back-end by script. I've never done this before so any suggestions would be appreciated.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Check out the possibilities with SSIS.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

phineas629 SSC-Addicted Points: 426 More actions · Answer 2

I was looking at it but haven't got a chance for a deeper dive. Will it offer me a completely scripting solution?

Thanks

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

It will do much more than you think. Please refer SSIS in BOL for further detail.

You can also think about database import which also support Access source.

Refer following links which might be useful:

http://technet.microsoft.com/en-us/library/ms140052.aspx

http://www.sqlservercentral.com/Forums/Topic1139114-391-1.aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

Take a look at SSMA, it is not a scripting solution but makes it very easy to migrate:

Microsoft SQL Server Migration Assistant for Access v5.2

http://www.microsoft.com/en-us/download/details.aspx?id=28763

"SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft that simplifies database migration process from Access to SQL Server. SSMA for Access automates conversion of Microsoft Access database objects to SQL Server database objects, loads the objects into SQL Server, and then migrates data from Microsoft Access to SQL Server.

SSMA for Access v5.2 is designed to support migration from Microsoft Access 97 and higher to all editions of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Azure."

I have used this in the past to move Access into SQL and it works very well. In some cases you may find that you need to rename tables / columns if they violate MSSQL naming rules - if so then a great tool is Find & Replace from rickworld.com which costs about $40 USD.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 5

Agree...Start with SSMA. I've used it in the past without any issues.

aaron.reese SSChampion Points: 13498 More actions · Answer 6

but remember that both solutions are only going to migrate the database and data, not any GUI. (please tell me that your Access application has a front-end database with the user controls and back-end database with the data..)