Best way to 'Mass' migrate Access DBs to SQL Server

[Shammas@discussions.microsoft.com Mike Shammas]

My customer is on a path to moving away from Access databases to SQL Server
(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
fuller analysis of their data.

They need to get 100's to 1000's of user-built Access databases migrated to
SQL Server in a controlled way, with minimal intervention. Ignoring the
fact that data migration can't be dealt with properly this way, what is the
MS recommended route for carrying out such an operation? Is there a standard
tool for doing this, or is a 3rd party tool available (that won't go down
well), or should be craft some control code around a simpler tool?

Any advice would be welcome.

[Tony Toews [MVP]]

AFAIK there is no tool for automatized bulk upsizing. One question
though is do these database have just tables or other objects as well?

There is a tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

[Patrick Jackman]

Although I haven't tried these products I am intrigued by them:
http://www.vb123.com.au/up/orders.htm

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

"Mike Shammas" <Mike Sha...@discussions.microsoft.com> wrote in message
news:B3AA85D1-2C71-4FE7...@microsoft.com...

[a a r o n . k e m p f @gmail.com [MCITP: DBA]]

I'd reccomend using something called a 'SCRIPT' to automate this

you can write a SQL statement, and run the batch via command line

you can automate anything in the world that you could possibly want to
do

I've always had to write my own automation / scripts to do stuff like
this

PS - I wouldn't trust Tony talking about migrations to SQL Server, he
tried one once.. because he's a wussy-pants canadian, he wasn't able
to complete it

On Mar 30, 9:42 am, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:

> Mike Shammas <Mike Sham...@discussions.microsoft.com> wrote:
>
> >My customer is on a path to moving away from Access databases to SQL Server
> >(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
> >fuller analysis of their data.
>
> >They need to get 100's to 1000's of user-built Access databases migrated to
> >SQL Server  in a controlled way, with minimal intervention.  Ignoring the
> >fact that data migration can't be dealt with properly this way, what is the
> >MS recommended route for carrying out such an operation?  Is there a standard
> >tool for doing this, or is a 3rd party tool available (that won't go down
> >well), or should be craft some control code around a simpler tool?
>
> AFAIK there is no tool for automatized bulk upsizing.   One question
> though is do these database have just tables or other objects as well?
>
> There is a  tool from the SQL Server group.

> SQL Server Migration Assistant for Access (SSMA Access)http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx

>
> Also see my Random Thoughts on SQL Server Upsizing from Microsoft

> Access Tips page athttp://www.granite.ab.ca/access/sqlserverupsizing.htm

>
> Tony
> --
> Tony Toews, Microsoft Access MVP

> Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

[a a r o n . k e m p f @gmail.com [MCITP: DBA]]

SQL Server DTS has some great tools to do this.

With SQL 2000, you can use the data import / export wizard in order to
create a DTS package, and then you can save this package as a Vbs
script

then it's easy to copy and paste it into Access Modules and add a
couple of loops

I'm pretty darn positive that I could move hundreds of Access
databases into SQL Server in my sleep

what city are you in?

On Mar 30, 7:41 am, Mike Shammas <Mike