Summary of how Access imports to SQL Server 2005 _mssql2005

Method One:

Name: DTS (This is also available in MSSQL2000)

Action: Run DTSWizard.exe in a command prompt window

The SQL Server Import and Export Wizard provides the easiest way to build a Microsoft SQL server integration Services (SSIS) package. The SQL Server Import and Export Wizard has access to a variety of data sources. You can copy or copy data from the following sources:
· Microsoft SQL Server
• Text File
· Microsoft Office Access
· Microsoft Office Excel
• Other OLE DB access interfaces
In addition, you can use only the ado.net provider and ODBC data source as the source.
Start the SQL Server Import and Export Wizard
In Business Intelligence Development Studio, right-click the SSIS Packages folder, and then click the SSIS Import and Export Wizard.
Or
In Business Intelligence Development Studio, on the Project menu, click the SSIS Import and Export Wizard.
Or
In SQL Server Management Studio, connect to the Database engine server type, expand the database, right-click a database, point to Tasks, and then click Import data or Export data.
Or
Run DTSWizard.exe in the Command Prompt window (located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn).

Method Two:

SQL Query
Action: Execute the following SQL statement:
EXEC sp_configure ' show advanced options ', 1;
Go
Reconfigure;
Go
EXEC sp_configure ' Ad Hoc distributed Queries ', 1;
Go
Reconfigure;
Go
INSERT into table name (Field 1, Field 2, Field 3)
SELECT Field 1, Field 2, Field 3
From OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' D:\source.mdb '); Jet
Oledb:database password= password ') ... Table name

Detailed steps in the text version:

After you install SQL Server Express, you can easily use the console for database management by installing SQL Server Management Studio EXPRESSCTP. However, SQL Server Management Studio EXPRESSCTP does not have the ability to import Access databases, and we should use the database utilities in access to import them.
This article takes Access2003 as an example of how to import an Access database into SQL Server Express, where the default settings are used where there are no instructions.

1, install Access 2003, and select the database replication component under Access.
2, open the Access 2003 database, and click Tools-Database Utilities-Upsizing Wizard.
3. In the Upsizing Wizard, select New database and click Next. Select the server with SQL Server 2005Express installed as the import server, using the default database name,
Click "Next". Add the tables that are available in to export to SQL Server.
Click "Next". Upsize The default table properties,
Click "Next". Create a new ADP file,
Click "Next". Click Open new ADP file, and click Finish.
4. Open the ADP file you just created, and click Tools-Database Utilities-Transfer database. Enter "myserver/sqlexpress" in SQL Server and click Next. Click Finish. Import complete.
5. Open SQL Server Management Studio Express to see the database you just added.

Such an import can be imported into a table or a link table and their relationship.