Back to Top
The nuts and bolts for
Creative web development

Upsize Access database

A short SQL Server 2000 tutorial about upsizing a MS-Access database to SQL Server.

The project used is the standard Access database that comes with the Learningsite command for Dreamweaver.

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 1

Figure 2 pops up.

Figure 2

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.

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>.

Figure 4

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.

Figure 5

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.

Figure 6

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.

Figure 7

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

Figure 8

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.

Figure 9

If you click on Transform the following window pops up:

Figure 9a

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:

The only instance of Autonumber in the Learning site 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:

Figure 9b

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:

Figure 9c

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.

Figure 10

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.

Figure 11

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.

Figure 12

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

Figure 13

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

Figure 14

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:

Figure 15

Figure 16

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

Figure 17

But notice this in the lower section:

Figure 18

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:

Query Analyzer with INSERT statement

Click on Execute:

Execute Query batch

And we see this in the message window:

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

Dennis van Galen

Dennis van GalenDennis started his career as order picker in warehouses. In the past 10 years he did alot of data-entry work for Government agencies and around the age of 20 he helped clean the KPN Telecom customer databases. At the age of 27 Dennis returned to KPN Telecom where he was a full time webmaster / webdeveloper. In his spare time he used to be a voluntary Manager for DMXzone.com. After leaving KPN in 2012 Dennis worked for Tevreden.nl on webbased customer satisfaction platforms.

In the past 12 years Dennis became experienced with various webtools, web-languages and database systems.

See All Postings From Dennis van Galen >>

Share this Article

Comments

Great Dennis!

Yeah, I see that you added the enable identity insert, etc.....great job, Dennis!

Kind Regards,

Jean-Marie Bonnar
Manager, CCMMUG
www.ccmmug.org

RE: Excellent, Dennis!

Thanks Jean-Marie, it is better now. I updated it today because it was missing crucial info.

Dennis

Excellent, Dennis!

This tutorial is excellent, Dennis! Just excellent! Your scripting a database tutorial is excellent too. Keep up the great work!

Kind Regards,

Jean-Marie Bonnar
Manager, CCMMUG
www.ccmmug.org