Data Management optimize

Upsize your Access database with SQL Server Data Transformation Services

Data Transformation Services can often upsize your Access database more efficiently than the Access Upsizing Wizard. Get a comparison of DTS and the AUW along with step-by-step instructions on how to use DTS to perform this task.

It's not unusual for a developer to use Access as a prototype or to develop noncritical applications. But problems can arise when the business outgrows the Access environment. Currently, Access 2002's .mdb and .adp files both have a 2-GB limit on the size of a database. That means almost every Access and SQL Server developer will face the challenge of upsizing an Access database to a SQL Server database at least once or twice. Many of you may even do so routinely.

Upsizing is so common that Access comes with an upsizing wizard that does an adequate job, but the process still isn't foolproof—SQL Server and Access have several incompatibilities that the wizard can't handle. What you might not know is that SQL Server's Data Transformation Services (DTS) can also upsize an Access database. Let's compare the two wizards, and look at how to use DTS to upsize an Access database. That way, you can choose the most efficient method for your upsizing jobs.

Comparison of Access Upsizing Wizard and DTS
The Access Upsizing Wizard (AUW) works from inside Access to copy data to SQL Server tables. DTS imports data from an Access database into SQL Server tables. Specifically, you can import data from a text file or an OLE DB data source (that includes an Access database) into SQL Server. As you import a file, you can convert data.

Fortunately, DTS is versatile enough that some developers might consider it superior to the AUW because:
  • You can change column (field) attributes as you import the data.
  • You can create queries during the import process that limit the data that's actually imported.

The one advantage the AUW has over DTS is that it can link an Access front end to SQL Server data. DTS simply imports data—you can't link that imported data to an existing front end.

Using the DTS Import/Export Wizard
There are a number of ways to execute DTS:
  • From the Windows Start menu, choose SQL Server and then select Import And Export Data from the resulting submenu.
  • Using Enterprise Manager, connect to the particular server and database to which you're importing data, and choose Data Transformation Services from the Tools menu. Then, choose Import. You'll use this option when the database to which you're importing data already exists.
  • In Enterprise Manager, connect to the server to which you're importing data and then right-click the Data Transformation Services node. Choose All Tasks and then select Import. The wizard will display an opening informational pane. Click Next to start the process.

Specifying a data source
The first step in upsizing an existing Access database is to identify the data source and the actual file that currently contains the data you're importing. To follow along with our example, choose Microsoft Access as the data source and then point to Northwind.mdb (the example database that comes with Access). You'll probably find this file in the following folder:
LocalDrive:\Program Files\Microsoft Office\Office10\Samples

You can upsize any Access database you like. Doing so will have no effect on the actual .mdb file or its data.

As you can see in the example in Figure A, a password and username aren't required. However, when working with a secure database, you'll need to enter the admin username and password. That means you'll need administrative permission before you start the process. Click Next to continue.

Figure A
Identify the data source, the file, and enter the admin username and password if you're importing from a secure database.

In comparison, the AUW will begin by asking you to determine whether you're creating a new SQL Server database or linking SQL Server data to an Access front end. As I mentioned, DTS doesn't offer the linking option.

Choose a destination
On the next screen, select a destination for the imported data. You can choose any database on the current server or create a new database (which is what I'll do in my example). Don't change the Destination setting. You are given several choices, but you're upsizing to SQL Server.

You could change the server, but I won't for this example. In addition, you should retain the Use Windows Authentication setting. Windows Authentication is available only with SQL Server 2000. However, your security settings may be different, so be prepared to change that option if necessary.

Select <new> from the Database control's drop-down list. In the resulting Create Database dialog box, enter a name for the new database (into which you'll import the Northwind data), as shown in Figure B. When naming a working database, be sure to follow your company's naming conventions. Click OK and DTS will update the name in the Database control. Click OK to continue.

Figure B
Give your new database a name.

Limit data to import
After identifying the Access data source and creating a new SQL Server database, you're ready to start importing data from the source to the new file, NorthwindonlocalSS. But you might not get this all done in one session. You can copy any number of tables at one time. However, if you want to limit data by query, you must work with just that table. Fortunately, the extra work isn't terribly time-consuming.

Let's begin the copy process by creating a query to limit the data in the Products table. Specifically, let's copy only those products that are active (not discontinued). To do so, select the Use A Query To Specify The Data To Transfer radio button in the pane shown in Figure C, and then click Next.

Figure C
You can copy several tables or the results of one query.

Figure D shows the SQL statement that will limit the records imported to just those products that aren't discontinued. Use the Query Builder to display the tables and columns that you'll include. For simple statements such as this one, the Builder probably isn't necessary, but for more complex statements that specify several columns by name, the Builder can help you avoid typos. Click Parse to verify that the statement is valid. Click Next once you've entered the SQL statement and are ready to continue.

Figure D
Enter the SQL statement that limits your data.

On the next screen, click the Preview button so you can check the query's results. Specifically, every value in the Discontinued column should be FALSE. Click OK to close the Preview Data screen. If you wanted to change a column's attributes, you'd click Transform, but don't do that now. Instead, you'll convert data when you copy entire tables. At this point, you might want to rename the pending table. The wizard will use the name Results if you don't. Click Results in the center control and change it to Products. Click Next when you're done.

The next screen displays options for scheduling the import task:
  • Run Immediate—This option will execute the import task (known as a package in DTS) immediately. Choosing this option won't save the task; it will just run it. Choose this option when upsizing.
  • Use Replication To Publish Destination Data—Use this option to replicate data from the data source to SQL Server.
  • Schedule DTS Package For Later Execution—Use this option to create the job when you want to wait to actually execute it. When upsizing a database, you probably don't need to worry about saving any of the import tasks. Clicking the ellipsis button (Builder button) to the right of this option will display additional scheduling options, which I won't review in this article.
  • Save DTS Package—Save the import job to one of the following: SQL Server, SQL Server Meta Data Services, Structured Storage File, or a Visual Basic File.

For this example, choose Run Immediately as shown in Figure E, and then click Next.

Figure E
You can execute the import task immediately, which is what you'll probably want to do when upsizing.

To execute the package, click Finish on the final screen. When you do, the wizard will display a dialog box that will display the wizard's progress. The Status List will display individual tasks and note when they're complete. Click Done to close the wizard once the task is completed.

At this point, DTS has upsized one table to a new SQL Server database named NorthwindonlocalSS. You'll need to copy the remaining tables, so restart DTS. Identify the data source as the Northwind database in the first screen, just as you did before. On the second screen, choose NorthwindonlocalSS from the Database control's drop-down list, and click Next.

From the next screen, choose the Copy Table(s) And View(s) From The Source Database option, and click Next. Select each of the tables you want to copy on the next screen, as shown in Figure F. Remember that you can skip Products because you've already upsized that table. So select all the remaining tables (and queries) except Products.

Figure F
Check the tables and queries (views) that you want to import.

Transform data
From this screen, you can change column attributes in the data source. To illustrate a simple transformation, click the Transform button to the right of the Customers table. From the resulting screen, select the CompanyName row and change the Size from 40 to 60, as shown in Figure G.

Figure G
Change a column's Size attribute.

Click the Edit SQL button to view the actual CREATE TABLE statement that the wizard will run to create the Customers table on SQL Server. You can change the SQL statement from this window, but you may have to duplicate your efforts by making the same changes to the previous screen.

You might notice that the CustomerID column isn't identified as the primary key for the Customers table. You can quickly change that by altering the statement. In this case, you'd add the keywords PRIMARY KEY NOT to the second line, as shown in Figure H. Then, click OK to return to the previous screen. Also note that the CompanyName column's Size attribute is indeed 60.

Figure H
Check the SQL CREATE TABLE statement.

Use the Transformation tab when you can modify the transformation script that will be executed to copy data to the SQL Server destination table. In addition, you can change the scripting language that's used in the process. The truth is, you probably won't use this tab for upsizing an Access database, but it's good to know it's there. Click OK to return to the previous screen and then click Next. As before, select the Run Immediately option, click Next, and then click Finish to execute the wizard.

When the wizard's finished, click OK to clear the informational message that tells you how many tables and queries were copied. This particular message might surprise you because it says 23 tables were copied when Northwind has only eight tables. You'll learn why in the next section. Click Done to clear the wizard.

Viewing the results
Now, you're ready to view the results in Enterprise Manager. Figure I shows the new database on a local server. The list of tables now includes all the Access queries—the wizard converted the queries to tables. You may or may not have any use for them in this format; I just wanted you to see what happens when you upsize a query this way. You probably won't want to copy the queries when actually upsizing.

Figure I
Find the new database using Enterprise Manager.

For the most part, the tables upsize successfully using DTS. You may find the Discontinued column in the Products table and the CustomerID column in the Customers table of particular interest. View the contents of the Products table (shown in Figure J) and you'll see that the Discontinued value for each product is 0 (FALSE), as it should be. Remember, you used a query to limit the copied data while upsizing the Products table.

Figure J
The Discontinued column contains only 0 (or FALSE) values.

Figure K shows the Customers table in Design View. As you can see, the CustomerID column is the table's primary key. If you view the remaining tables, you'll find that the wizard didn't copy the primary keys. You can eliminate the need to reset primary keys by using the SQL window shown in Figure I.

Figure K
The wizard correctly set this table's primary key.

Finishing up
The wizard won't automatically copy primary keys from the data source to the destination tables. You can use the SQL screen to set those primary keys as you did with the Customers table, or you can simply set them in the finished destination tables. There are a few other things the wizard won't do:
  • The wizard won't maintain relationships; you'll have to recreate those in SQL Server.
  • The wizard won't enforce referential integrity rules set in the data source.

Upsize it
When faced with an upsizing task, use either the Access upsizing wizard or SQL Server's DTS. Either will do a good job of upsizing the data. However, you may find the DTS wizard less confusing and more dependable.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.