SQLServer访问Access、Excel间的数据(国外英语资料).doc

SQLServer访问Access、Excel间的数据(国外英语资料) The so-called data transmission, in fact, refers to SQLServer access to data between Access and Excel. Why should we take this into consideration? For historical reasons, many of the customers previous data were stored in text databases such as Acess, Excel, and Foxpro. Now that system upgrades and database servers such as SQLServer and ORACLE often require access to data in a text database, such a requirement arises. A project that went on a business trip for some time ago was faced with a problem: data exchange between SQLServer and VFP. The need to complete the title is a very simple thing in SQLServer. Usually, there are 3 ways: 1, DTS tools 2, BCP 3, distributed queries DTS does not need to say, because it is graphical interface, it is easy to use. Here, we will talk about the following two examples, which are simple examples such as checking, adding, deleting and modifying: The following nonsense is not said, directly in the form of T-SQL show. One, SQLServer and Access 1, query the data in Access method: 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 tables) Select * from SQLServer table Or with BCP Master..Xp_cmdshellbcp serv-htjs.dbo.serv_user, out, c:\db3.mdb, -c, -S, -q, Sa, -P, Sa, -U The difference is that OpenRowSet needs MDB and tables to exist, and BCP generates the MDB when it doesnt exist 3, from Access to SQLServer write data: with the above foundation, this is very simple Insert, into, SQLServer, table select * from OpenRowSet (microsoft.jet.oledb.4.0,, database=c:\db2.mdb,select * from, Accee table) Or with BCP Master..Xp_cmdshellbcp serv-htjs.dbo.serv_user, in, c:\db3.mdb, -c, -S, -q, Sa, -P, Sa, -U 4,