Data Mutual guide between Access database and SQLserver2000

This article describes how to implement an Access database and SQLserver2000 data transduction by executing SQL statements using Query Analyzer with SQL Server

Execute SQL statement implementation by using SQL Server's Query Analyzer:

I. Import access data into SQL Server

Executing the following statement in SQL Server enables you to import data from the B04 table in Access into the Sqltablenaem table in SQL Server

INSERT into Sqltablename (SFIELD1,SFIELD2,SFIELD3,SFIELD4,SFIELD5,SFIELD6)

SELECT CONVERT (char, afield1), Afield2,afield3,afield4,afield5,afield6

From OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:\doc\accessdb.mdb '); Jet oledb:database password=abc) ... B04

Description

1.CONVERT (char (), afield1) can convert the type of data to control the length;

2. In "OpenDataSource" (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:\doc\accessdb.mdb '; Jet oledb:database password=abc) ... B04 "Medium Data source=" C:\doc\accessdb.mdb the physical path to the Access database (guaranteed two databases on a single machine);

3.PASSWORD=ABC "ABC" is the password of the Access database, if the database is not filled out without a password;

4.B04 is the name of the table in the Access database;

5. You can also add some conditions, such as where afield= ' a ' and ..., import data with selection criteria

Two. Import SQL Server data into Access

Execute the following code in SQL Server Query Analyzer

INSERT into OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:\doc\accessdb.mdb '); Jet oledb:database password=abc) ... B04 (AFIELD1,AFIELD2,AFIELD3,AFIELD4,AFIELD5,AFIELD6)

SELECT Sfield1,sfield2,sfield3,sfield4,sfield5,sfield6

From Sqltablename

Description: You can also add some conditions such as where afield= ' a ' and ..., with the selection criteria of the import data.

Summary: This is all INSERT statements, which use only one form of insert, and other forms can be used for data transduction.