Upsizing an Existing Access Database to SQL Server 2000 Desktop Engine

Organize with Office 365 Course

Organize With Office 365

Get Instant Access

The Upsizing Wizard of Access 2000 and Access XP allows you to open an existing Access database and convert it to the equivalent SQL Server database. In this section, we are going to convert the sample Northwind database from Access to SQL Server. Once the conversion is complete, we will analyze the report produced to ensure that no errors occurred. We will then take a look at the resulting project file that allows us to view and manage the newly created SQL Server database.

Note that Access 2000's Upsizing Wizard works differently to the Upsizing Wizard in Access XP and, if you use the 2000 wizard, you may end up with slight differences in the resulting upsized database (such as different views, stored procedures, or functions).

Try It Out - Upsizing the Northwind Database from Access to SQL Server

44 . Open Microsoft Access XP.

45 . Select File | Open and browse to find the Northwind sample database (northwind.mdb) supplied with Access and click the Open button to open it up. A common location for this database file is C: \Program Files\Microsoft Office\Samples. This may not necessarily be its location on your machine if you installed the sample databases somewhere else. You can search for the file using the Start | Search menu option. If you still cannot find the Northwind database on your hard drive, then you can re-run Microsoft Access setup and install the sample databases.

46. Close the Main Switchboard of the Northwind application so that you are left with the database design view.

47. From the Tools menu, select Database Utilities | Upsizing Wizard, as shown below:

48 . The Upsizing Wizard will begin, and the following screen will appear:

49. Select the Create new database option if it isn't already selected and click the Next button so we can create a brand new SQL Server database from the existing Northwind Access database.

50 . Fill in information about the SQL Server (which in our case will be the name of our Desktop Engine installation) that you want to create the new database on, as shown in the example below. A list of available servers should be in the drop-down list. In some cases, (local) will be the correct one if you are creating the database on a version of Desktop Engine on the current computer. For the User Id and Password, fill in information for a valid SQL Server user with create database permissions. By default, you can use "sa" (system administrator) with a blank password. Of course, when you have a database containing sensitive information, you really need to change these user details to something less obvious.

Below the UserId and Password boxes, specify the name you want use when referring to the new Northwind database from Desktop Engine. I'm going to call mine NorthwindSQL and, to keep things simple, it's a good idea if you do the same. Once all necessary information is filled in, click the Next button.

Please note that, with Access 2000, you may receive an "Overflow " dialog box at this step. The solution is to apply a patch that can be downloaded from the Microsoft Web Site. There is a knowledge base article that discusses this issue. You can read this article at the following URL: http://support.microsoft.com/support/kb/ articles/Q272/3/84.ASP.

51 . The next screen prompts you to specify the tables in the existing Northwind database that are to be included in the new SQL Server database. Click the double right arrow button on the screen so that all the tables are moved to the list of tables to export, as shown below, and then click the Next button.

52 . The next screen allows us to specify which database attributes (that is, fields) we wish to carry over to the upsized version. In this case, we want to upsize the table structure as well as the data, and thus should make sure that the following options are checked before clicking Next:

Note that for the table relationships, you have the option to Use DRI or Use triggers. DRI creates relationships between the tables during the creation of the table. Triggers, on the other hand, should be selected when you need to support cascading updates and deletes.

For the timestamp option, generally, you should allow the wizard to determine whether a timestamp should be used. Using a timestamp column in a table makes sure that its timestamp column is updated with a value that reflects that last time it was updated.

53 . The next screen allows us to specify where we want to create the Access project file and what we want it to be called. This screen gives us the option to link to an existing application or create a new clientserver Access application. We are going to do the latter, which will convert all the database details from the Access database to the SQL Server version, while leaving the user interface components in Access. This allows us to create a client-server application with Access as the front-end and SQL Server as the back-end. You can change the file location to whatever destination on your computer you wish to place the Access project file, but save it with the name NorthwindSQL.adp.

We could also specify to save the password and user ID so that the user ID and password you use for connecting will be stored in a connection string that the project uses.

Once this is done, the Finish button becomes enabled, as you can see below. However, we aren't going to click Finish just yet; instead we're going to click the Next button to see what other choices are available.

Upsizing Wizard

The Upsizing Wizard can modify the existing application or create a new application to work with the 5QL Server database.

What application changes do you want to make? f* Create a new Access client/server application,

ADP File Name: |C:\Program Files\Microsoft Office\OfficelO\5amples\Nortl Browse... | Link SQL Server tables to existing application No application changes.

Save password and user ID.

Help

Cancel

< Back |

| Next >

1

Finish |

54 . The screen shown below is the final one of the wizard. Notice how the Next button is no longer enabled while the Finish button still is. Here we can decide whether we want to Open th e new ADP file (our new client-server project) once the wizard completes, or whether we wish to remain in the original Access Northwind file.

We are going to select the option to Open the new ADP file, as we are eager to see what it looks like.

55 . After clicking the Finish button, Microsoft Access works its magic and converts our Access Northwind database to a SQL Server database with a client-server Access front end. You will see a progress bar as shown below detailing each step as it is taken, and the overall progress of the upsizing process:

56. Any errors with the upsizing attempt will be displayed in dialog boxes. For example, in this instance, a syntax error was encountered while copying a particular record into the newly created tables. This error was caused by attempting to place the text value Qtr into an integer field. The wizard notifies you of any such error, and continues with the upsizing process once you have clicked OK.

57. Once the upsizing is complete, a summary report will be displayed on the screen. The summary report gives you a record of the work you did and helps you determine why database objects failed to update. The first piece of important information in this report is the detailed listing of each table that was converted, what data type it was converted to, which indexes were converted, etc. An example of the sort of information described for each table is shown below. Notice how the original Access values are shown on the left and the SQL Server values they were translated to appear on the right. The CategoryID, for example, was a Number (Long) in Access but, in SQL Server, it was converted to the int data type. At the bottom we see that the original CategoryName index was successfully converted to a CategoryName index in SQL Server. This tells us that the index was and is a Unique Index on the CategoryName field, which, you may recall from Chapter 1, means that the index is based on a field that may contain unique values only.

Table: Categories

Microsoft Access

SQL Server

Table Name: Categories

Categories

Attached Table Name:

Aliasing Query:

Validation Rule:

Timestamp fíe Id added to SQL Server table.

Fields Microsoft Access

SQL Server

Field Name: CategorylD

CategorylD

Data Type: Number (Long)

int

Field Name: CategoryName

CategoryName

Data Type: Text(15)

nvarchar(15)

Field Name: Description

Description

Data Type: Memo

text

Field Name: Picture

Picture

Data Type: OLE Object

image

Indexes Microsoft Access

SQL Server

Name: CategoryName

CategoryName

Fields: CategoryName

CategoryName

Type: Unique

Unique

58 . After all the details for each converted table have been listed, the summary report describes the queries that were converted. Notice how, in the example below, some of the stored Access Queries were converted to Views in SQL Server, and some others were converted to Stored Procedures. Views and Stored Procedures will be explained in more detail in the next two chapters but, for now, it is enough to know that Views and Stored Procedures in SQL Server are similar in concept to the Stored Query in Access, and that the report shows the results of the conversion.

Queries

Query

Alphabetical List of Products Upsized using SQL:

CREATE VIEW "Alphabetical List of Products" AS

SELECT Products.*, Categories.CategoryName AS ExprlOOl

FROM Categories INNER JOIN Products ON (Categories.CategoryID=Products.CategoryID) WHERE (((Products.Discontinued)=0))

Query

Product Sales for 1997 Upsized using SQL:

CREATE VIEW "Product Sales for 1997" AS

SELECT Categories.CategoryName, Products.ProductName, sum(convert(money,"Order Details".UnitPrice*Quantity*(l-Discount)/100)*100) AS ProductSales, 'Qtr' + datepart(q,ShippedDate) AS ShippedQuarter

FROM (Categories INNER JOIN Products ON (Categories.CategoryID=Products.CategoryID))

JOIN (Orders INNER JOIN "Order Details" ON (Orders.OrderID="Order Details".OrderlD)) ON (Products.ProductID="Order Details".ProductID) WHERE (((Orders.ShippedDate) Between '1/1/1997' And '12/31/1997')) GROUP BY Categories.CategoryName, Products.ProductName, 'Qtr' + datepart(q,ShippedDate)

INNER

59 . You can print the log file or use the report that is created and saved automatically for you.

Congratulations again! You have now successfully upsized the Northwind database to create a client-server project, including the table structure of your SQL Server database and an Access project file containing the user interface elements (forms, and so on) that links the tables to the SQL Server database.

We should have the new Access project open (NorthwindSQL. adp) since, above, we chose the option to open it after the wizard completed. Take a moment to look around the project and get a feel for the new structure. You will see that it doesn't look very different to the standalone Access Northwind database prior to the upsizing process. The biggest difference takes place behind the scenes. The tables are displayed just like before but now the data in the tables and the tables themselves are physically located in a Desktop Engine database as opposed to an Access file.

Was this article helpful?

0 0

Post a comment