Migrate data from Access to SQL 2005

9 Sep 2006 2:05 PM

mikepallorad

hello everybody

using the SSMA migration from Access 2003 to SQL 2005 is cool.

then Robert Burke saved me a lot of strain

http://blogs.msdn.com/robburke/archive/2006/05/30/610803.aspx

but i couldnt quiet understand the link to

"reproduce the data from your local tables on the remote server"

There are certain data in the Access database which are necessary
before running the
application.

what am trying to figure is how to use >> Script << to add these
"data" from Access to

SQL 2005 .

11 Sep 2006 8:08 AM

John Bell

Hi

I am not sure why you are scripting the table definitions and data unless
you can not put the access database or a backup (which would be my preferred
method as you can sort out most if the problems without having to do alot of
remote working)of an upgraded database onto the remote server!

You should be able to migrate both structure and data using the access
upgrade wizard (the article does assume SQL Server is available!) You could
also use the import/export wizard; to do this right click the database in
SQLServer Management Studio's object browser and select Import Data or Export
Data from the menu. For importing from Access change the datasource to
Microsoft Access from the drop down menu and input the mdb file information.

The link on the blog points to http://vyaskn.tripod.com/code.htm#inserts
which allows you to generate insert statements for your data. If you have a
large amount of data this method will take a long time to load in the data.
The only thing that I know if that may be an issue is that it may not cater
for a large number of columns or extreemly long column names.

John

Show quote

"mikepallo***@ausi.com" wrote:

> hello everybody
>
> using the SSMA migration from Access 2003 to SQL 2005 is cool.
>
> then Robert Burke saved me a lot of strain
>
> http://blogs.msdn.com/robburke/archive/2006/05/30/610803.aspx
>
> but i couldnt quiet understand the link to
>
> "reproduce the data from your local tables on the remote server"
>
> There are certain data in the Access database which are necessary
> before running the
> application.
>
> what am trying to figure is how to use >> Script << to add these
> "data" from Access to
>
> SQL 2005 .
>
>

12 Sep 2006 1:21 AM

Gil Lapid

SSMA for Access
(http://www.microsoft.com/sql/solutions/migration/default.mspx)let you
generate a complete script of the converted Access database before or
after applying it to SQL Server. After that you can go to the data
migration phase using SSMA as well. Are you facing any spesific problem
?

mikepallo***@ausi.com wrote:
Show quote

> hello everybody
>
> using the SSMA migration from Access 2003 to SQL 2005 is cool.
>
> then Robert Burke saved me a lot of strain
>
> http://blogs.msdn.com/robburke/archive/2006/05/30/610803.aspx
>
> but i couldnt quiet understand the link to
>
> "reproduce the data from your local tables on the remote server"
>
> There are certain data in the Access database which are necessary
> before running the
> application.
>
> what am trying to figure is how to use >> Script << to add these
> "data" from Access to
>
> SQL 2005 .