Use SQL server data conversion SERVICE TO PROMOTE ACCESS database

Developers often use access as the prototype or use access to develop applications that are not critical. However, as the company's business grows, the problems to be solved will become more and more complex, and the access environment may not meet the needs. Currently, the. MDB and. ADP files of Access 2002 limit the length of a database to 2 GB. This means that almost every access and SQL Server developer will eventually promote an Access database into an SQL Server database.

As promotion has become a very common task, Access provides a "promotion wizard ". Although it can do this, the operation process is not simple. The Wizard cannot handle the incompatibility between SQL Server and access. You may not know that the "data transformation services" (DTS) of SQL Server can also promote an Access database. Next let's compare the two wizards and try to use DTS to promote an actual access database. In this way, when you encounter a promotion task, you can select the most effective method.

Compare the Access Promotion wizard and DTS

The Access Promotion wizard (auw) works within access to copy data to the SQL Server table. Instead, DTS copies data from an Access database to an SQL Server table. Note: You can import data from a text file or an ole db data source (including the ACCESS database) to SQL Server. While importing files, you can also convert the data.

Some developers think that DTS has a much better function than auw. The reason is:

You can change the attribute of a column (field) When importing data.
Create a query during import to limit the actual imported data.
However, auw also has an advantage that DTS does not possess, that is, it can link an access front-end to SQL Server data. Otherwise, DTS can only import data. You cannot link the imported data to an existing front-end.

Use DTS import/export wizard

You can execute DTs in several ways:

In the Windows Start menu, select Microsoft SQL Server and then import and export data ].
Run the Enterprise Manager, connect to a specific server and database for which you want to import data, and select data conversion service | import data from the Tools menu ]. You can use this option to import data to an existing database.
In the Enterprise Manager, connect to the server to which you want to import data, and right-click the data conversion service node. Select all tasks and import data ]. A prompt screen is displayed at the beginning of the wizard. Click Next to start the operation.

Data Source

The first step to promote access is to specify the data source and the actual file containing the data to be imported. In this example, select Microsoft Access as the data source, and then find the northwind. mdb database (the demo database provided by access ). It is usually in the following folders:
Localdrive:/program files/Microsoft Office/office10/Samples

Note: You can promote any access database. Promotion will not affect the actual. mdb files and their data.

In this example, you are not required to enter the password and user name. However, when operating a security database, you may have to manage the administrator username and password. In other words, you must obtain the management permission before operating such a database. Click Next to continue.

Pica:

Specifies the data source and file. If you are operating on a secure database, you also need to enter the administrator username and password

On the contrary, if you use auw, it first requires you to create a new SQL Server database, or choose to link SQL server data to an access front-end. As mentioned above, DTS does not provide the link option.

Select target

On the next screen, select a destination (location) for the imported data ). You can select any database on the current server or create a new database (this example adopts the second approach ). Do not change the "purpose" Settings. Although there are several options to choose from, the "purpose" in this example is [Microsoft ole db provider for SQL Server ].

Although the server can be changed, you are not required to do so in this example. In addition, the [use Windows Authentication] setting should be retained. Only SQL Server 2000 supports Windows authentication. Of course, if your security settings are different, you can modify the option accordingly.

Select <New> from the database drop-down list ]. The create database dialog box is displayed. Enter the name of the new database. The northwind data will be imported into this database, as shown in B. When naming a working database, follow the naming rules of your company. After you click OK, DTS automatically updates the name in the database drop-down list. Click OK to continue.

Picb:

New Database Name
Restrict the data to be imported

After an access data source is specified and an SQL Server database is created, you can import data from the data source to the new SQL Server database (northwindonlocalss ). However, you may not be able to complete all the work in one session. You can copy any number of tables each time. But if you want to use a query to limit data, you can only operate on that table at a time. Fortunately, additional work does not take much time.

First, let's create a query to limit the data in the "product" table and start the copy process. Specifically, we only want to copy the products that are active (not aborted. To do this, select [use a query item to specify the data to be transferred], as shown in C, and then click [next ].

PICC:

You can copy several tables or query results.
 
Figure D's SQL statement limits the number of records to be imported to products that are not aborted. Use the Query Builder to display the tables and columns you want to include. For simple statements like this, the Query Builder may be a little useless, but for complex statements, if you specify the names of several columns, the Query Builder can effectively prevent mistakes. Click analyze to verify the statement validity. After you have entered the correct SQL statement, click Next ].

Picd:

Enter an SQL statement that limits data.

On the next screen, Click Preview to verify the query result. Note that each value in the "Abort" column should be false. After previewing, click OK to close the View data screen. To change the attributes of a column, click the ellipsis in the [convert] column, but this example does not require you to do that. Instead, we want to convert the data when copying the entire table. In this case, you may consider renaming the target table to be generated. By default, the wizard uses the name "result. In the [PURPOSE] column, click "result" to change it to "product ". Click Next to continue.

The next screen displays the options for scheduling import tasks:

Run now -- this option immediately executes the Import Task ("package" in DTS "). If this option is selected, the task is not saved. Instead, the task is run. Select this option during promotion.
Use the replication method to publish the target data-use the target table for replication. With this option, the create and release wizard is started after the DTs import/export wizard ends.
Schedule the DTS package for later execution-use this option to create a task if you want to postpone it to later execution. When you promote a database, you may not need to save any import tasks. Click the ellipsis next to this option. A series of scheduling options are displayed.
Save DTS package-Save the import task to any of the following locations: SQL Server, SQL Server meta data services, structured storage files, or visual basic files.
In this example, select run now, as shown in E, and then click Next ].

Pice:

You can choose to run the import task immediately. This option is generally selected for promotions.
 
To execute this package, click Finish on the last screen ]. Then, the wizard displays the progress in a dialog box. In the status list, each individual task is displayed and you can check whether it is completed at any time. After all the tasks are completed, click Finish to close the wizard.
Now, DTS has successfully promoted a table to a new SQL Server database (that is, northwindonlocalss ). You also need to copy the remaining tables, so please restart DTs. At the beginning, specify the data source as the northwind database. Next, select northwindonlocalss from the database drop-down list and click Next ].
On the next screen, select copy table and view from the source database, and then click Next ]. Select the table to be copied, as shown in F. Remember, now you can skip the "product" table because the table has been promoted. Therefore, select all tables (and queries) except products on this screen ).

Picf:

Select the table and query (View) You want to import)
 
Convert data
On the screen shown in F, you can change the column attribute in the data source. The following is a simple demonstration conversion. Click the ellipsis on the right of the customer table. On the screen that appears later, select "company name" and change "size" from 40 to 60, as shown in G.

Picg:

Change the "size" attribute of a column
 
Click Edit SQL to view the create table statement that runs when the wizard creates a customer table on SQL Server. You can directly modify the SQL statement in this window, but it is best to modify it on the screen through the graphical user interface.

You may notice that the "Customer ID" column is not marked as the primary key of the "customer" table. You can modify statements quickly. In this example, You need to insert the keyword primary key not, H in the second row. Click OK to return to the previous screen. Note that the "size" attribute in the "Company Name" column is indeed 60.

Pich:

Check SQL CREATE TABLE statements
 
On the conversion card, you can modify the conversion script used to copy data to the target SQL Server table. You can also change the script language used in this process. Although you may not need this card when promoting the access database, it may be useful in other cases. Click OK to return to the previous screen, and then click Next ]. As before, select the run now option, click next, and then click Finish to start the wizard.
After the Wizard is complete, click OK to clear the prompt message (which tells you how many tables and queries have been copied ). The message may surprise you because it claims to have copied 23 tables, but the northwind actually only has 8 tables. The next section explains the cause. Click Finish to close the wizard.
 
View results
Now, you can view the results in Enterprise Manager. Figure I shows the new database on the local server. In the [Table] list, all access queries are now included. Note: The Wizard has converted the query into a table. The query in this format may not be of any use, but I just want to explain to you what will happen when I promote a query like this. During the promotion, you may not want to copy the query.

Pici:

Use the Enterprise Manager to view the new database
 

To a large extent, DTS has successfully promoted the table. Note that the "Abort" column of the "product" table is specially processed, and the "Customer ID" column of the "customer" table. View the content of the "product" table (as shown in J). You will find that the "Abort" value of each product is 0 (false ). This is what we hope, because when we promote the "product" table, we intentionally use a query to limit the copied data.

Picj:

The "Abort" column only contains 0 (or false) values.
 

Figure K shows the "customer" table in the design view. As you can see, the "Customer ID" column is the primary key of the table. Check other tables and you will find that the wizard does not copy the primary key. On the contrary, using the SQL window shown in h can avoid the need to reset the primary key in the future.

Pick:

The wizard correctly sets the primary key of the table.
 
Final work
The wizard does not automatically copy the primary key from the data source to the target table. You can set primary keys as you did in the customer table (see Figure H) or in the final target table. In addition, there are several other things that the Wizard will not do:
The wizard does not maintain relationships; you must recreate them in SQL Server.
The wizard does not force reference integrity rules set in the data source.
Promotion
When facing a promotion task, you can consider the Access Promotion wizard or SQL Server DTs. Both can promote data well. However, the DTS wizard is easier to operate and has complete functions and does not rely on access.