MS-Access to SQL 2005

Question

MS-Access to SQL 2005

Vijai-703291

SSC Eights!

Points: 959
More actions
May 19, 2008 at 5:49 am

#187765

I am having a project to transfer a huge MS-Access database (with lot of relations and constraints :hehe to SQL server 2005. Currently we plan to retain the frontend in MS-access and later to some web application. Please suggest your better way to accomplish this. If you have any good link please provide me.
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

OK, first off, there's no such thing as a huge access DB... but...

I would use the SSIS import wizard. It will connect directly to access and you can pull all your tables in with no trouble at all. It will even create the tables on the SQL side.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

GSquared SSC Guru Points: 260824 More actions · Answer 2

Access has an Upgrade Wizard that will directly convert the database to SQL. I've used it, and it works pretty well. Requires a little work on your part, but not too much. Try that out, it's the easiest way to accomplish this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jason-602680 SSC Eights! Points: 855 More actions · Answer 3

The Upsizing Wizard in Access works well enough. To upsize to SQL 2005, you will need Access 2003 or higher (2002 may work, 2000 will certainly not). But I found that it was not granular enough for what I needed, and would not alert me beforehand to a lot of problems that became major issues once the data was in SQL.

What I ended up using instead was the SQL Server Migration Assistant for Access. It's a Microsoft product, and while it's not perfect itself, it also has a lot more flexibility and pre-conversion validation. Find it here: http://www.microsoft.com/sql/solutions/migration/access/default.mspx

Also make sure you grab the Guide to Migrating from Microsoft Access to SQL Server 2005 white paper from that same page. Even if you don't use SSMA for your migration, there's a lot of invaluable information in that paper.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Assuming you still have access to the SQL Server 2000 tools, when in doubt you can also try the DTS import and Export wizard. It can help get past the various intricacies of the upsizing wizard.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vijai-703291 SSC Eights! Points: 959 More actions · Answer 5

Thanks but which one is best. Did you have any good web link for each.

1. Upsizing wizard in Access

2. SSMA (SQL Server Migration Assistant)

3. SSIS (SQL Server Integration servcies)

or

4. Data Transformation Services (DTS)

I am having Access 2003 and SQL 2005.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

I would certainly test SSMA.

Download it, install it, and see what kind of remark, warning, errors it gives.

You may need to do some stuff as a perparation for the migration.

Make sure you have a TEST-set the access file !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

ALZDBA (5/20/2008)
I would certainly test SSMA.
Download it, install it, and see what kind of remark, warning, errors it gives.
You may need to do some stuff as a perparation for the migration.
Make sure you have a TEST-set the access file !

Agreed - start with SSMA. If there are issues you can't seem to get around using it, then I'd head for SSIS or DTS. The upsizing wizard is simply an older, less capable version of SSMA, so I don't think it will help you if the others cannot.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vijai-703291 SSC Eights! Points: 959 More actions · Answer 8

Wow! I have downloaded and tested SSMA it worked perfectly.

But I closed the Error report unknowlingly. Is it possible to generate the error report only without migrating the database again.

Thanks for all your support

jason-602680 SSC Eights! Points: 855 More actions · Answer 9

Not the error log specifically, but you can open up the log. Its default location is C:\Documents and Settings\username\Application Data\Microsoft SQL Server Migration Assistant\a2ss\log\

Vijai-703291 SSC Eights! Points: 959 More actions · Answer 10

I found the location of error file. 😀

I stored my project in c:\Migration\MIGRATION1\SqlMigration1. So the error report is available in the below location in different report folders

c:\Migration\MIGRATION1\SqlMigration1\report\report_2008_05_27T10_05_18\mainindex.html