Developers often develop as prototypes as prototypes or use Access to develop not very critical applications. However, with the growth of the company's business, the problem to be solved will become more and more complex, and the Access environment may not meet the needs. Currently, Access 2002's .mdb and .adp files limit the length of a database within 2 GB. This means that almost every Access and SQL Server developers will eventually entert a Access database to a SQL Server database.
Since the promotion has become an extremely common task, the Access support provides a "promotion wizard". Although it can do this, the operation process is not simple. SQL Server is not compatible with Access's place where the wizard cannot be processed. You may not know that SQL Server's "Data Conversion Services" (DTS) can also upgrade an Access database. Let us compare these two wizards and experience how to use DTS to upgrade an actual Access database. In this way, when you encounter a promotion task, you can choose the most effective way.
Compare Access Sheng Wizard and DTS
The Access Up Wander (AUW) works within Access to copy data to the SQL Server table. Instead, DTS copies the data from an Access database to the SQL Server table. Note that you can import data from a text file or an OLE DB data source (where the Access database is naturally included) into SQL Server. The data can be converted while importing the file.
DTS features very complete, some developers think it is much better than AUW, the reason is:
You can change the column (field) attribute when importing data.
Create a query during import, limit the actual imported data.
But AUW also has an advantage that DTS does not have, that is, it can link an Access front end to SQL Server data. DTS, otherwise, it can only import data, you cannot link the imported data to an existing front end.
Use DTS Import / Export Wizard
Several ways can be taken to perform DTS:
In the Windows [Start] menu, select [Microsoft SQL Server], select [Import and Export Data].
Run Enterprise Manager, connect to a specific server and database you want to import data, then select [Data Conversion Service] | [Import Data] from the [Tools] menu. This option can be used when importing data in an existing database.
In the Enterprise Manager, connect to the server to import the data, then click the [Data Conversion Service] node. Select [All Tasks] | [Import Data]. The wizard will start a prompt screen. Click [Next] to start the operation.
Specified data source
The first step in the promotion Access is to specify the data source and the actual files containing data to be imported. In this example, select Microsoft Access as a data source, find the Northwind.mdb database (Demand Database provided by Access Support). It is usually in the following folders:
LocalDrive: / Program Files / Microsoft Office / Office10 / Samples
Note that you can upgrade any Access database. The promotion will not have any effect on the actual .mdb file and its data.
As shown in Figure A, this example does not require you to enter a password and username. However, when you operate a secure database, you may have to manage administrator usernames and passwords. In other words, you must get administrative privileges before operating this database. Please click [Next] to continue.
Pica:
Specify the data source and file. If you are a secure database, you also need to enter an administrator username and password.
Instead, if you are using AUW, it will first ask you to choose a new SQL Server database or choose to link SQL Server data to an Access front end. As mentioned earlier, the DTS does not provide a link option. Choose the purpose
In the next screen, select a destination (place) for the imported data. You can choose any database on the current server or create a new database (this example is a second approach). Don't change the "destination" setting, although there are several options to choose, but the "purpose" of this example is [Microsoft OLE DB provider for SQL Server].
Although the server can be changed, this example does not require you to do this. In addition, [Use Windows Authentication] settings should also be retained. Only SQL Server 2000 supports Windows authentication. Of course, if your security settings are different, you can also modify that option accordingly.
Select [
PICB:
Specify new database name
Restrict data to be imported
The Access data source is specified and new SQL Server databases can begin to introduce the data from the data source into the new SQL Server database (NorthWindonLocalss). However, it may not be possible to complete all work in a session. Although any number of tables can be copied each time. But if you need to use a query to limit the data, you can only operate the table each time. Fortunately, additional work does not take much time.
First, let's create a query to limit the data in the "Product" table, start our copy process. Specifically, we just want to copy those activities (rather than stop) products. To do this, please select [Use a query to specify the data to be transferred], as shown in Figure C, then click [Next].
PICC:
Several tables can be copied, or copy a query result
The SQL statement of Figure D will limit the records to be imported as those that do not have abort. Use the query generator to display the tables and columns you want to include. For a simple statement like this, the query generator may have a little large material, but for more complex statements, if the name of the column is specified, the query generator can effectively prevent you from making mistakes. Click [Analyze] to verify the validity of the statement. After entering the correct SQL statement, click [Next].
Picd:
Enter the SQL statement to the data
In the next screen, click the [Preview] button to verify the results of the query. It is important to note that each value in the "abort" column should be false. After the preview is complete, click [OK] to close the [View Data] screen. If you want to change the properties of a column, you can click the omitial button in the [Conversion] column, but this example does not require you. Instead, we are ready to convert data when copying the entire table. At this point, you may have to consider renovating the upcoming target table. By default, the "Result" name is used by the guide. Please click "Result" in the [Destination] column to change it to "Product." Click [Next] to continue.
The next screen displays options for scheduling import tasks:
Run Now - This option immediately performs import tasks (called "package" in DTS). If you select this option, you will not save your task, but just run it. Please select this option when upgrading.
Publish purposes with replication methods - use the target table to copy. Use this option, the DTS Import / Export Wizard ends the creation publishing wizard after the operation is run. Scheduling the DTS package for later execution - if you want to post later, use this option to create a task. When promoting a database, you may not need to save any import tasks. Click the omitial button next to this option, a series of schedule options will be displayed, and details will not be described later.
Save the DTS package - Save the imported task to any of the following: SQL Server, SQL Server Meta Data Services, structured storage files or Visual Basic files.
For this example, please select [Run Now], as shown in Figure E, and then click [Next].
PICE:
Alternatively run the import task immediately. When promoting promotion, you generally choose this option.
In order to perform this package, click [Complete] on the last screen. Thereafter, the wizard uses a dialog to display the progress. In the status list, each individual task is displayed and at any time indicating whether they are completed. After all tasks are completed, click [Complete] Close the wizard.
Now, DTS has successfully promoted a table into a new SQL Server database (ie NorthWindonLocalss). You also need to copy the remaining table, so please restart the DTS. Start, or specify a data source as a Northwind database. Next, select [NorthWindonLocalss] from the [Database] drop-down list, click [Next].
In the next screen, select [From Source Database Copy Table and View], then click [Next]. Select each table you want to copy, as shown in Figure F. Remember, you can now skip the "product" table, because you have already upgraded that table. Therefore, select all other tables (and queries) other than "product" in this screen.
PICF:
Check the tables and query you want to import (view)
Conversion data
In the screen shown in Figure F, you can change the column properties in the data source. Let's take a simple demonstration conversion. Click the omitial button on the right side of the "Customer" table. In the subsequent screen, select the "Company Name" line, change the "size" from 40 to 60, as shown in Figure G.
Picg:
Change the "Size" property of a column
Click the [Edit SQL] button to see the CREATE TABLE statement that is actually run when you create a "Customer" on SQL Server. You can modify the SQL statement directly in this window, but it is best to modify it through the graphical user interface in the previous screen.
You may notice that the "Customer ID" column is not marked into the primary key of the "Customer" table. By modifying the statement, you can make a quick modification. In this case, you need to insert the keyword Primary Key Not in the second line, as shown in Figure H. Then, click [OK] to return to the previous screen. Also note that the "Size" attribute of the "Company Name" column is indeed 60.
Pich:
Check SQL CREATE TABLE statement
In the [Conversion] card, you can modify the conversion script used to copy data to the SQL Server target table. In addition, you can also change the scripting language used in this process. Although you may not use this card when you upgrade the Access database, it may be very useful in other situations. Click [OK] to return to the previous screen, then click [Next]. As before, choose [Immediate Run] option, click [Next], then click [Complete] to start the executive wizard.
After the wizard is complete, click [OK] Clear the prompt message (it tells you how many tables and queries have been copied). The message may surprise you because it claims to copy 23 tables, but Northwind actually only 8 tables. The next section will explain the reason. Click [Complete] Close the wizard. View Results
Now we can view results in Enterprise Manager. Figure 1 shows the new database on the local server. In the [Table] list, all Access queries are now included. Note that the wizard has converted the query into a table. This format of the query may have no use, but here is just what will eventually explain to you. You may not wish to copy the query when you really upgrade.
Pici:
View new databases using the Enterprise Manager
To a large extent, DTS's promotion of the table is successful. Please note that we have the "abort" column for specially handled "product" tables, and "customer ID" columns of the "Customer" table. View the contents of the "Product" table (as shown in Figure J), you will find the "abort" value of each product is 0 (false). This is what we hope, because when the "Product" table is promoted, we deliberately use a query to limit the copy of the data.
Picj:
"Abort" column contains only 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. View other tables, you will find that the wizard does not copy the primary key. Instead, using the SQL window shown in Figure H to avoid the necessary main keys in the future.
Pick:
The wizard correctly sets the primary key of this table
Last job
The wizard does not automatically copy the primary key from the data source to the target table. You can set the primary key (see Figure H) as the "Customer" table, or in the final destination table. In addition, there are still a few things that the wizard will not do:
The wizard does not maintain the relationship; you must rebuild them in SQL Server.
The wizard does not force the reference integrity rules set in the data source.
promotion
When a promotion task is facing a promotion task, Access's promotion wizard or SQL Server's DTS can be considered. Both can be well promoted. However, the DTS wizard is easier to operate, but it is fully functional and does not depend on Access.
