Data exchange between Access database and SQL server 2000

SQL statement execution using SQL Server's query Analyzer:

1. Import Access data to SQLserver

Run the following statement in SQLserver to import data from Table B04 in Access to the sqltablenaem table in SQLserver.

Insert into sqltablename (sfield1, sfield2, sfield3, sfield4, sfield5, sfield6)
Select convert (char (10), 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

Note:
1. CONVERT (char (10), afield1) can CONVERT data types and control the length;
2. in "opendatasource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ doc \ accessdb. mdb "; Jet OLEDB: Database Password = abc )... data Source = "c: \ doc \ accessdb in B04. mdb is the physical path of the Access database to ensure that the two databases are on one machine );
3. In Password = abc, "abc" is the Password of the Access database. If the database has no Password, it is left blank;
4. B04 indicates the name of the table in the Access database;
5. You can also add conditions such as WHERE afield = 'A' and... to import data with selection conditions.

2. Import SQL server data to Access

Run the following code in the 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
Note: You can also add conditions such as WHERE afield = 'A' and... to import data with selection conditions.

Summary: these are all INSERT statements. In this article, only one form of INSERT is used, and Other forms can be used for data mutual import.