Use SQL Server data conversion SERVICE TO PROMOTE Access database (1)

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 SQL Server's "Data Conversion service" Data Transformation Services, DTS) 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 accesskey promotion wizard (AUW) works within Access and can 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 an 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 column fields 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 select 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 from the Tools menu. | import data ]. 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 | 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 demonstration database provided by the Northwind. mdb database 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.
For exampleFigureAIn 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.

FigureA

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.