x

Migrating Access to SQL

Can someone help me figure out the best tool to use when migrating huge access mdb file to sql server? what is the advantage of using Upsizing wizard verse SQL server migration assistant? what are the most important things I should consider in planning the migration? I would appreciate your comments from experience. Thanks!

more ▼

asked Jan 21, 2010 at 10:28 AM in Default

sharon gravatar image

sharon
151 11 13 15

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I'm not familiar with the SQL Server Migration Assistant, but have some experience with the Upsizing Wizard.

The upsizing process is fairly easy and gives very good results. One gotcha I noticed on the data side is that all of the Access text fields were "upsized" to SQL Server text columns. I spent an extra day going through the resulting SQL Server database and setting up appropriately sized varchar() specifications on most of these columns so I could use string functions and operators, as well as apply indexes when needed.

Something else to consider are any non-table structures you may have in your access database.

If you have any forms, queries, macros or reports, you'll need to include your new SQL Server tables in the Access database as linked tables in order for these objects to continue to work.

If you want to include your Access queries within the SQL Server database, you'll need to rewrite them for SQL Server as views for select queries or stored procedures for update or insert queries. This is not always straightforward, as the syntax is different between the two products (You may use IIF() in Access which you would need to translate to a CASE statement in SQL Server, for example).

more ▼

answered Jan 21, 2010 at 02:04 PM

KenJ gravatar image

KenJ
20k 1 3 12

(comments are locked)
10|1200 characters needed characters left

Microsoft have an app for that, they call it their upsizing wizard, details are on technet

I have run through it previously to see what happens and its pretty straight forward.

more ▼

answered Jan 21, 2010 at 12:09 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

I have used the upsizing wizard in access several times and it is indeed quite painless most of the time.
Jan 21, 2010 at 01:42 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x4

asked: Jan 21, 2010 at 10:28 AM

Seen: 810 times

Last Updated: Mar 11, 2011 at 05:21 AM