The so-called data transmission is actually referring to data between Access, Excel, SQL Server. Why do you want to take into account this problem? Due to historical reasons, customers previous data are in the text database, such as Acss, Excel, FoxPro. Now the system upgrade and database server such as SQL Server, Oracle often need to access data in the text database, so this requires. Projects in front of the previous period of time are a problem with such a problem: data exchange between SQLServer and VFP. To complete the needs of the title, it is a very simple thing in SQL Server. Usually there can be 3 ways: 1, DTS Tool 2, BCP 3, distributed query DTS does not need to be said, because that is a graphical operation interface, it is easy to get started. Here mainly talks about the two, respectively, to check, increase, delete, and change as simple example: The following nonsense will not say, directly in the form of T-SQL.
I. SQLSERVER and Access 1. Query data in Access: 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 SQL Server: Insert: Insert: INSERT INTO OPENROWSET ('Microsoft.jet.OleDb.4.0', '; Database = C: /Db2.mdb', 'Select * from accept * SELECT * from SQL Server table or BCP master..xp_cmdshell'bcp "serv" htjs.dbo.serv_user "OUT" C: /Db3.mdb "-c -q -s" The difference between "sa" -p "sa" "is mainly: OpenRowSet requires MDB and table existence, BCP will Generate this MDB 3 when there is no existence, write data from Access: With the above foundation, this is very simple in Insert Into Sql Server Table Select * from OpenRowSet ('Microsoft.jet.OleDb.4.0'; Database = C: /DB2.MDB ',' SELECT * ACCEE table ') or use BCP master..xp_cmdshell'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 ASE = C: /DB2.MDB ',' Select * from serv_user ') Set Lock = 1 SQLServer and Access are roughly so much.
