SQL Server, Access, and Excel data transmission summary

The so-called data transmission actually refers to the Access between SQL Server and Excel.
Why should we take this issue into consideration?
Due to historical reasons, many of the customer's previous data is stored in a text database, such as Acess, Excel, and Foxpro. Currently, after system upgrades and database servers such as SQLServer and ORACLE, you often need to access data in the text database, which leads to such a requirement. A project that has been on a business trip for some time ago is faced with the following problem: data exchange between SQLServer and VFP.
To complete the title, it is very simple in SQLServer.
Generally, there are three methods: 1. DTS tool 2, BCP 3, and distributed query.
DTS does not need to be mentioned, because it is a graphical operation interface and is easy to use.
Here we will mainly talk about the following two examples: query, add, delete, and modify:
The following nonsense will not say, directly in the form of T-SQL.
I. SQLServer and Access
1. How to query Access data:
Select * from OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: db2.mdb ', 'select * from serv_user ')
Or
Select * from OpenDataSource ('Microsoft. Jet. OLEDB.4.0 ', 'data Source = "c: DB2.mdb"; User ID = Admin; Password =')... serv_user
2. Write data from SQLServer to Access:
Insert into OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: db2.mdb ', 'select * from Accee table ')
Select * from SQLServer table
Or use BCP
Master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" out "c: db3.mdb"-c-q-S "."-U "sa"-P "sa "'
The main difference above is: OpenRowSet requires the existence of mdb and tables. BCP will generate the mdb when it does not exist.
3. Write data from Access to SQLServer: with the above Foundation, this is simple.
Insert into SQLServer table select * from
OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: db2.mdb ', 'select * from Accee table ')
Or use BCP
Master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" in "c: db3.mdb"-c-q-S "."-U "sa"-P "sa "'
4. Delete Access data:
Delete from OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: db2.mdb ', 'select * from serv_user ')
Where lock = 0
5. Modify Access data:
Update OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: db2.mdb ', 'select * from serv_user ')
Set lock = 1
SQL Server and Access are roughly the same.
II. SQLServer and Excel
1. Query from Excel
Select * from OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'Excel 8.0; HDR = yes; database = c: ook1.xls;', 'select * from [Sheet1 $] ') where c like' % f %'