How does SQL + Express import an Access database?

This article mainly introduces the method of importing Access database in Sql2005express, the friend that need can refer to the following

　　Method One: Single table import

(1) Open the peripheral configuration of the SQL Server perimeter Application Configurator--> feature--> Some services are turned off by default and cannot be imported without opening.

Under Database Engine Select ad hoc remote query--> enable OPENROWSET and OPENDATASOURCE support (E)

The following figure:

(2) "New query" in SQL Server Management Studio Express

Input:

SELECT * into [table name in SQL2005]

From OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' Access database file path; user id=admin;password=; ') ... [Table name to import in Access]

For example, import the students table in the Access database file "D:210myaccessdb.mdb" into SQL2005

Can be written as:

SELECT * Into Student a

From OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' d:210myaccessdb.mdb '; user id=admin;password=; ') ... Students

After execution, a table named "Student A" is automatically included in the SQL2005, the same as the students table in the Access database file "D:210myaccessdb.mdb".

　　Method Two: Import data through the Upsizing Wizard for access

(1) Open an Access database

The following figure:

(2) Select--> from the menu bar "Database Utility (D)"--> "Upsizing Wizard (U)"

(3) How to upsize--in this case, by creating a new database

(4) < figure 1> to fill in the SQL Server name (see < Figure 2>), and the name of the promoted database

< map 1>

< map 2>

(5) Select the table to be exported to SQL Server

(6) Upsizing Table property settings

]

(7)

(8) Promotion completed

(9) After the promotion completed SQL2005 a Testsql database, the following figure