怎么用ADO查询目标数据在数据库中是否存在？

cjq253

opiona 发表于 2019-10-9 11:38

大神，还有个疑问，这种方法，如果不同对象在同一个数据中有相同内容，会出现误判吗？
例子中的EEEE的数据1是内容5，如果BBBB的数据1也是内容5呢？
left join 会不会出现只能匹配到一个的现象？

语虚何以言知

后台表1我增加了2列如下：
运行结果如下：


附件： test.rar (23.34 KB, 下载次数: 3)

2019-10-11 17:23 上传

点击文件名下载附件

代码：

1. Sub test()
   
2. Dim CNN As Object
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set CNN = CreateObject("adodb.connection")
   
5. CNN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='excel 12.0;hdr=yes;imex=1';Data Source=" & ThisWorkbook.FullName
   
6. 
   
7. s1 = "select * from [操作表$a:a]  where 对象 is not null "
   
8. s2 = "select * from [后台表1$]  where 对象 is not null "
   
9. SQL = "select * from (" & s1 & ")a left join (" & s2 & ")b on a.对象=b.对象"
   
10. With Sheets("操作表")
    
11.     .Range("d1:x10000").Clear
    
12.     .[d2].CopyFromRecordset CNN.Execute(SQL)
    
13.     .Columns(5).Delete
    
14.    
    
15.     xrr = Sheets("后台表1").UsedRange.Rows(1)
    
16.     .[d1].Resize(1, UBound(xrr, 2)) = xrr
    
17.     .[d1].Offset(0, UBound(xrr, 2)) = "状况"
    
18. End With
    
19. 
    
20. s3 = "select * from [后台表2$]  where 数据 is not null "
    
21. s4 = "select * from [后台表3$]  where 数据 is not null "
    
22. SQL = s3 & " union " & s4
    
23. Set rst = CNN.Execute(SQL)
    
24. arr = WorksheetFunction.Index(rst.GetRows, 0, 0)
    
25. st = Join(arr, ",")
    
26. 
    
27. CNN.Close: Set CNN = Nothing
    
28. 
    
29. 
    
30. With Sheets("操作表")
    
31. 
    
32.     brr = .[d1].CurrentRegion
    
33.     For i = 2 To UBound(brr)
    
34.         For ii = 2 To UBound(brr, 2) - 1
    
35.             If InStr(st, brr(i, ii)) = 0 Or brr(i, ii) = "" Then d(brr(1, ii) & "无") = ""
    
36.         Next
    
37.          brr(i, UBound(brr, 2)) = IIf(d.Count = 0, "ok", Join(d.Keys, ","))
    
38.           d.RemoveAll
    
39.     Next
    
40.    
    
41.     crr = WorksheetFunction.Index(brr, 0, UBound(brr, 2))
    
42.     .[d1].Offset(0, UBound(xrr, 2)).Resize(UBound(brr), 1) = crr
    
43. 
    
44. End With
    
45. 
    
46. End Sub
    

复制代码