Consolidate SQL data and access data into one dataset

access| data Sometimes we want to get a part of the data in the SQL database, while the other part of the data in the Access database, but sometimes it is necessary to link the data in these two databases to query, it is a good way to integrate them into a data collection. By following this procedure, you can combine SQL data and access data with only SQL connection database statements

First of all, you need to use the account with the database administrator to execute the following script 01_addlinkserver.sql,01_addlinkserver.sql's contents are as follows:

Use master

EXEC sp_addlinkedserver
@server = ' Valinkmdb ',
@srvproduct = ',
@provider = ' microsoft.jet.oledb.4.0 ',
@datasrc = ' C:\McMo.mdb '
Go

EXEC sp_addlinkedsrvlogin
@rmtsrvname = ' Valinkmdb ',
@useself = ' false '
Go

Valinkmdb is a mock Access database that confirms security on the Enterprisemanager of SQL Server-linked server > projects,
Please make sure Valinkmdb's entry is made. Then you can query the Aceess database directly on the SQL Query Analyzer, for example: SELECT * from Valinkmdb...test, where test is the name of the table you want to query in the Access database, Of course you can also make a joint query at this time, for example: SELECT * from Valinkmdb...test inner join sqldatabase on sqldatabase. Number = test. number

You can apply in ASP or. NET or other languages

If you have any questions, please ask for a common communication