Upsize Access database

Upsizing the Learningsite database to SQL Server 2000

First step to upsizing the database is opening the Enterprise manager, in the enterprise manager we expand the Servers folder, then we expand the desired server and we rightclick on Databases and select All Tasks -> Import Data as in figure 1.

Figure 2 pops up.

After we press next we have to specify our datasource, we change the driver so that it looks for a .mdb file (the Access database) and then in the file name we use the folder box to navigate to our database and we select it, this leaves us with Figure 3.

After pressing next we are asked to specify the destination Server AND database, as shown in Figure 4, I'm importing the Learningsite database in a NEW database which pops up another window after we use the Database list to select <new>.

In Figure 5 we are asked the name of the new database, I called it SQL_Learning and I'm going with the default Datafile size and Logfile size.

After we click ok in Figure 5, we are back in Figure 6, make sure your server login data is correct and press next to advance to figure 7.

In Figure 7 we are asked what we want to copy, we can feed our DTS (Data Transformation Services) Wizard a query or copy whole table(s) and/or view(s), we want everything so we simply press next.

In Figure 8 we are asked which objects to copy, a simple click on Select All will do for this database

After we clicked Select All we get Figure 9, here we can use Transform to alter the design of Fields, which is really usefull but can be a little confusing.

If you click on Transform the following window pops up:

Here you can change what type the fields are and if nulls are allowed. Here we can check if SQL Server is going to transform the data properly. This particular database is a bad example to demonstrate how to convert auto-number / primary key fields since there is only 1 instance of autonumber in the database:

So we need to only check that table and see how it gets transformed, so let's close the transform Activities table and open Transform Session_IDs:

Oh, this looks fine ! It spotted the auto-nr in Access and recommends a Integer field which is NOT nullable, which is almost SQL Server's equivalent of AutoNumber, there's only one thing missing, we need to turn on Identity insert or this will horribly fail if there is allready data in the Access database:

Now it should work, let's finish the wizard.

Click OK to close transformations and click on next for figure 10.

In Figure 10 we can save the DTS package for future use, run it immediately or schedule it to run later, we are going to run it now so simply pressing next will suffice.

In Figure 11 we have a last oppertunity to check what is about to happen in the summary view and when we are happy with the settings we press Complete.

After a few seconds the message in Figure 12 pops up telling us what just happened, in this case 6 tables were copied from MS-Access into SQL Server.

Press OK in Figure 12 and review Figure 13 for more information about what SQL Server just did, after reviewing click Done.

Now where is our database ? We first need to refresh the databases so that it becomes visible:

And there it is, now let's check if everything is transformed properly, expand the new SQL_Learning database and click on tables, followed by a rightclick on our Session_IDs table where we select Design for the visual Design tool:

Now we still have no auto-nr field even though it does look like it does:

But notice this in the lower section:

No identity, this is going to return a nasty "Cannot insert the value NULL" if we try to insert a record, so we make 1 final alteration to our table which is give the SessionID field a Identity, like so:

PERFECT ! SQL Server automatically adds a seed and a increment value to use when inserting new records, not convinced ? Then let us make sure it actually WORKS, to do that i'm going to open the Query Analyzer and insert a record, go to Tools and open Query Analyzer:

In the Query Analyzer type the following instruction after you checked that you use the PROPER database to insert into:

Click on Execute:

And we see this in the message window:

Now lets switch back to our Enterprise manager and view the table rows inside the Session_IDs table:

Hurrah, our new record is inserted as Session 1:

Now our Upsizing is done and I bet you are wondering why nothing shows up in your Dreamweaver Recordset window when you access this database through a DSN, this is normal behaviour since we did not add permissions to the database yet, I have written a seperate FAQ on permissions which is located here.

Note: If you create a new table, view or other object you need to give the proper users the proper permissions or your new table won't even show up in the recordset window, this is something that is easily forgotten so be aware of this.

Download the Access database

Download the SQL script