Upsizing 100 Databases

[Stephan Zaubzer]

Hi Folks
I am currently working on a database project with MS SQL Server 2000.
The company used to work on a seperate mdb database for each project.
Now the company wants all former projects to be represented in a single
database on a database server. Therefore the new database design has
already been developed and now the data of 94 access databases (which
use all exactly the same design) have to be imported. The first step
would be to bring all the databases on the sql server. There the import
to the old databases would be managed with stored procedures.
My problem now:
How can I efficiently bring 94 databases to the SQL server using the
upsizing assistant or some similar tool? I really don't want to spend
one afternoon, just waiting 5-10 minutes to start the upsizing assistant
by performint 10 mouse clicks and then waiting for the next clicks to be
done. If there is no mechanism to automate this job I probably should
train a monkey to do this job in exchange for some bananas ;-)
Regards
Stephan

 

[DLG]

The best way to do this will be from SQL Server Enterprise Manager where you
can create DTS packages to bring in the data from the various databases. you
can create one DTS package and pass in a listing (variables) of all of your
access mdb files and the data will be extracted from the old access tables
into the new SQL server tables.

 

[Tony Toews]

I am currently working on a database project with MS SQL Server 2000.
The company used to work on a seperate mdb database for each project.
Now the company wants all former projects to be represented in a single
database on a database server. Therefore the new database design has
already been developed and now the data of 94 access databases (which
use all exactly the same design) have to be imported. The first step
would be to bring all the databases on the sql server. There the import
to the old databases would be managed with stored procedures.
My problem now:
How can I efficiently bring 94 databases to the SQL server using the
upsizing assistant or some similar tool?

Alternatively why not just create an MDB and link to the SQL Server
database containing those stored procedures.

The link to all the tables of the first of the 94 MDBs. Run the
stored procedures to copy the records from the MDB to the server.
Relink to the next and continue.

Obviously you may need to rewrite those stored procedures a bit to
access the Access tables.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm