Promote Access database with MSsql data conversion Service

Users 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, Access2002's. mdb and. adp files both limit the length of a database to 2 GB. This means that almost every Access

Users 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 data library to 2 GB. This means that almost every Access

Users 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, both the. mdb and. adp files of Access 2002 have one DataThe database length is limited to 2 GB. This means that almost every Access and SQL Server developer will end up with an Access DataLibrary PromotionSQL Server DataLibrary.

Because PromotionHas become a very common task, so Access provides a" PromotionWizard ". 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," Data Conversion Service(Data Transformation Services, DTS) can also PromotionOne Access DataLibrary. Next let's compare the two wizards and try to use DTS PromotionAn actual Access DataLibrary. In this way PromotionYou can select the most effective method for a task.

Compare AccessPromotionWizard and DTS

Access PromotionThe wizard (AUW) works inside Access, can DataCopy to the SQL Server table. Instead, DTS will DataLibrary DataCopy to the SQL Server table. Note that you can DataFrom a text file or an OLE DB DataSource (including Access DataDatabase) Import SQL Server. When importing files, you can also DataProceed Conversion.

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

You can import DataWhen you change the column (field) attribute.

Create a query during import to limit the actual import 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. DTS does not. It can only import DataYou cannot import DataLink 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 select Import and Export Data].

Run the Enterprise Manager and connect to DataA specific ServiceAnd DataLibrary, and then select 【 Data Conversion Service] | [Import Data]. In an existing DataDatabase Import DataYou can use this option.

In Enterprise Manager, connect to DataOf ServiceAnd right-click 【 Data Conversion Service] Node. Select all tasks | Import Data]. A prompt screen is displayed at the beginning of the wizard. Click Next to start the operation.

SpecifyDataSource

PromotionThe first step of Access is to specify DataSource and include Data. In this example, select Microsoft Access DataSource, and then find the Northwind. mdb DataDemonstration provided by Access DataLibrary ). It is usually in the following folders:

LocalDrive: Program FilesMicrosoft OfficeOffice10Samples

Note that you can Access DataLibrary Promotion. PromotionThe actual. mdb file and its DataAny impact.

In this example, you are not required to enter the password and user name. However, when operating a security DataYou may have to manage the administrator username and password. In other words DataBefore creating a database, you must have administrative permissions. Click Next to continue.

Figure





Specify DataSource and file. If the operation is secure DataLibrary, you also need to enter the administrator user name and password

On the contrary, if you use AUW, it first requires you to choose to create a new SQL Server DataLibrary, or select DataLink to an Access front-end. As mentioned above, DTS does not provide the link option.

Select target

On the next screen DataSelect a destination (region ). You can select the current ServiceAny DataLibrary, you can also create DataLibrary (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 it can be changed ServiceBut this example does not require you to do this. 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.

From 【 DataLibrary] select 【 <新建> ]. The [Create DataLibrary dialog box. Enter New DataLibrary name, which belongs to the Northwind DataImport this DataLibrary, as shown in B. Name a job DataAlways follow the naming rules of your company. After you click OK, DTS automatically updates 【 DataLibrary: the name in the drop-down list. Click OK to continue.

Figure B





Specify a new DataLibrary name

Restrict Data

Access DataSource, and a new SQL Server DataAfter the library, you can start DataSlave DataImport a new SQL Server from the source DataLibrary (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 need a query to limit DataYou 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 DataTo start the copy process. Specifically, we only want to copy the products that are active (not aborted. To do this, select [use a query to specify Data, C, and then click Next ].

Figure C





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

Figure D





Input pairDataSQL statement

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 view Data] Screen. To change the attributes of a column, click 【 Conversion] Button in the column, but this example does not require you to do that. Instead, when copying the entire table Conversion Data. 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. PromotionSelect this option.

Purpose of publishing using the replication method Data-Use the target table for copying. 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. PromotionOne DataLibrary, 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 imported task to one 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 ].

Figure E





You can choose to run the import task immediately. Proceed PromotionSelect this option.

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 added a table PromotionIs a new SQL Server DataLibrary (that is, NorthwindonlocalSS ). You also need to copy the remaining tables, so please restart DTS. At the beginning DataThe source is specified as Northwind. DataLibrary. Then 【 DataLibrary drop-down list, select NorthwindonlocalSS, and click Next ].

On the next screen, select DataCopy tables and views, and then click Next ]. Select the table to be copied, as shown in F. Remember, now you can skip the "product" table because PromotionThe table. Therefore, select all tables (and queries) except products on this screen ).

Figure F





Select the table and query (View) You want to import)

ConversionData

On the screen shown in F, you can change DataColumn attribute in the source. Below 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.

Figure G





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.

Figure H





Check SQL CREATE TABLE statements

In 【 Conversion] Card