DTS: Access to SQL 7.0 migration

This article is part of a larger article.

In the source drop down box, labelled Source: select Microsoft Access. As you make your selection the dialogue box changes to reflect your choice of datasource. Now you can specify the access database you wish to migrate. Click on the little button with the … to browse for your database. As an example why not choose the sample Northwind database that comes with MS Access. If you have password protected your database you can now also specify the username and password. If on the other hand you haven’t set any security, then you can click Next.

The ensuing dialogue box allows you to specify the destination, i.e. where you are going to copy your database (it’s tables and data) to. By default the Microsoft OLE DB Provider for SQL Server is selected. This is the service we will be using to connect to our SQL Server box.Alternatively you could select the Microsoft ODBC Driver for SQL. Below this drop down menu you can now specify the server name, in the Server: field. Now you have a choice of either using the Windows NT authentication method or the SQL Server authentication method. Choose SQL Server authentication and specify your login account. As you will be creating a new database in this tutorial, make sure that you have enough permissions to do this. Else contact your DBA and ask him to set up a database for you. [Please note that you have to click the Refresh button in order the database that was created for you] From the Database: drop down menu choose <New>. This opens another dialogue where you specify:

Name of the database, e.g. Northwind SQL in this case.

Next specify the Data File Size, so check your existing database size and give it a little more space. E.g. Northwind is 2.3 Meg in size so give it 3 Meg (you can only specify increments of one Meg).

Finally you can specify the Log file size. Set that to 1 Meg.

By clicking OK you create the database on the SQL server. Now you can click Next. Here you can either specify to transfer one or more tables across or alternatively the results of a query. Choose the default option Copy table(s) from the source table. Click Next. From the next dialogue you can select which tables you wish to transfer. Just click Select All. Again you can either copy the tables and the data they contain as it is, or use ActiveX scripts to modify it. The use of ActiveX scripts goes beyond the realms of this document, so copy everything across as it is. Next!

Right this concludes this section of the Access to SQL migration, please go back to the previous document by clicking here.