Access database cross-Library queries and recordset differentiation

Medical Device software is generally a stand-alone software, if it is a Windows platform, often choose Access database storage structured data, because he is lightweight, easy to deploy. However, with the development of medical information, doctors want to manage the data of multiple stand-alone devices, the use of network database can certainly solve this problem, but the software deployment will become more troublesome, so if access data can support cross-Library query, it is perfect, The good news is that access has supported this feature from version 2007 and the query is fast.

Access database cross-Library queries can use "in words", as follows, see Microsoft Help manual: https://support.office.com/zh-cn/article/IN-%E5%AD%90%E5%8F% A5-3f9369a8-2032-4637-81af-699db411fbfa

Grammar

[SELECT | INSERT] into destination in
{Path | ["Path" "type"] | ["" [type; DATABASE = path]}

Example

There are two databases, the table Ys_user results as follows, you want to sort by time

Id	Ys_name	Ys_date
1	Tom	2018/1/1
2	John doe	2018/1/3

D:\test1.mdb

Id	Ys_name	Ys_date
1	Harry	2018/1/1
2	Ma Liu	2018/1/2

D:\test2.mdb

Query statement:

Select Id,ys_name,ys_date from Ys_user in ' D:\test1.mdb ', select Id,ys_name,ys_date from Ys_user in ' D:\test2.mdb ' order b Y ys_date

Query Result:

Id	Ys_name	Ys_date
1	Tom	2018/1/1
1	Harry	2018/1/1
2	Ma Liu	2018/1/2
2	John doe	2018/1/3

problem

The recordset is detected, but how do you know which library the record is when you modify the recordset? We can make the article in the query statement, can add a column

Query statements

Select Id,ys_name,ys_date, ' D:\test1.mdb ' as DBPath from Ys_user in ' D:\test1.mdb ', select Id,ys_name,ys_date, ' D:\test2. MDB ' as DBPath from Ys_user on ' D:\test2.mdb ' ORDER by ys_date

Query Result:

Id	Ys_name	Ys_date	DBPath
1	Tom	2018/1/1	D:\test1.mdb
1	Harry	2018/1/1	D:\test2.mdb
2	Ma Liu	2018/1/2	D:\test2.mdb
2	John doe	2018/1/3	D:\test1.mdb

Access database cross-Library queries and recordset differentiation