access-How to query multiple databases (cross-Library queries)

Test Pass:
ACCESS
SELECT * from F:\MYk.mdb.tablename
Description: 1. query statement 2. Where to come from (no password is a path) 3. Table name of the query




=======================================
I have two databases .
A, B
and then I'm going to display the two table combinations of two databases as a table C.
Judging condition is
a database of AA tables in fields A and B database in the BB table in the same field B
and a database of AA table in the field A or B database in the BB table field B equals a value

Example:
Sql= "Select B.filetitle as T1, a.publishtime as T2 from OpenDataSource (' SQLOLEDB ', ' data source =192.168.0.2; Uid=sa; pwd=123456 '). F_FS_DB_site_yaoban_200702.dbo.content_table b,content_table a where b.filetitle=a.filetitle "

Oracle Cross-Library queries

first create a database link:
CREATE Public Database link Data link name CONNECT to login user name identified by password USING ' (DESCRIPTION =
(address_list =
(address = (PROTOCOL = TCP) (HOST = IP address of the other Oracle server) (port = port number))
     )
(Connect_data =
(service_name = other Oracle Server service name)
     )
) '

where the data link name is the service name added to the local Oracle Database Console (Oracle Enterprise Manager Console) tree node

the table tablename statement to query the offset database is as follows:

SELECT field name from [email protected] data link name;

due to the diversity of business logic
you often have to query data in a different database in SQL Server
This creates the need for distributed queries
now I will be in the development of several types of queries summarized as follows:


version 1.access

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' AI ',
--Product name
' Access ',
--ole DB character
' microsoft.jet.oledb.4.0 ',
--Data source
--Format:
--Drive letter: \ path \ filename
--\ \ Network name \ share name \ file name (network version)
' D:\testdb\db.mdb '
GO


--Create mappings between remote logins on a linked server
--The linked server is set by default to use the login context
--Now we modify the link to connect the server without using any login or password
exec sp_addlinkedsrvlogin ' ai ', ' false '
Go


--Query data
SELECT * from Ai...mytable
Go


version 2.excel

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' ai_ex ',
--Product name
' ex ',
--ole DB character
' microsoft.jet.oledb.4.0 ',
--Data source
--Format:
--Drive letter: \ path \ filename
--\ \ Network name \ share name \ file name (network version)
' D:\testdb\mybook.xls ',
NULL,
--ole DB Provider-specific connection string
' Excel 5.0 '
GO


----Creating mappings between remote logins on a linked server
--The linked server is set by default to use the login context
--Now we modify the link to connect the server without using any login or password
exec sp_addlinkedsrvlogin ' ai_ex ', ' false '
Go


--Query data
select * from AI_EX ... sheet3$
Go


3.ms SQL Version

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' Ai_mssql ',
--Product name
' Ms ',
--ole DB character
' SQLOLEDB ',
--Data source
' 218.204.111.111,3342 '
GO


--Create mappings between remote logins on a linked server
EXEC sp_addlinkedsrvlogin
' Ai_mssql ',
' false ',
NULL,
--Login user name of remote server
' Zhangzhe ',
--Login password for remote server
' fish2231 '
Go


--Query data
SELECT * from Ai_mssql.pubs.dbo.jobs
Go


--There's an easier way.
--This method is the default open RPC after the linked server is established

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
--Use the data source as the name
' 218.204.111.111,3342 ',
' SQL Server '
GO


--Create mappings between remote logins on a linked server
EXEC sp_addlinkedsrvlogin
' 218.204.111.111,3342 ',
' false ',
NULL,
--Login user name of remote server
' Zhangzhe ',
--Login password for remote server
' fish2231 '
Go


--Query data
select * FROM [218.204.253.131,3342].pubs.dbo.jobs
Go


version 4.Oracle

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' O ',
--Product name
' Oracle ',
--ole DB character
' Msdaora ',
--Data source
' ACC '
GO


--Create mappings between remote logins on a linked server
EXEC sp_addlinkedsrvlogin
' O ',
' false ',
NULL,
login user name for--oracle server
' f02m185 ',
login password for--oracle server
' f02m185185 '
Go

--Query data
--format: Linkserver. Oracle user name. Table name
--Pay attention to uppercase because it is capitalized in Oracle's data dictionary
select * from O.. f02m185. AI
Go

access-How to query multiple databases (cross-Library queries)