|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
有一个3W多的Excel表格,需要在Access数据库中的三个表中查询是否符合条件的用户身份(比如贫困户)。Access数据里有三个表,每个表中的数据不超过50W。我使用以下代码,全部查询完需要30多分钟。简单测试的1000条数据,需要2分20秒。因为数据涉及安全,不方便上传附件,见谅!
- Public Sub SearchDB()
- Application.ScreenUpdating = False '关闭屏幕更新
- '连接数据库
- Dim cnADO As Object
- Dim rsADO As Object
- Dim dataPath, querySql As String
- Dim resultArr(), stuArr()
- On Error GoTo ErrMsg
- dataPath = ThisWorkbook.Path & "\DataSource.accdb"
- Set cnADO = CreateObject("ADODB.Connection")
- On Error GoTo ErrMsg
- With cnADO
- .Provider = "Microsoft.ACE.OLEDB.12.0"
- .ConnectionTimeout = 100
- .Open dataPath
- End With
-
-
- '查询数据
- Dim countRow As Double
- countRow = Sheets(1).Range("E4").End(xlDown).Row
- 'countRow = 1000
- ReDim resultArr(1 To countRow - 3, 1 To 3)
- ReDim stuArr(countRow - 3)
- stuArr = Application.Transpose(Sheets(1).Range(Cells(4, 6), Cells(countRow, 6)).Value)
- For i = LBound(stuArr) To UBound(stuArr)
- '查询经开区建档立卡户
- querySql = "SELECT COUNT(*) FROM jkqjdlk WHERE 身份证号码='" & stuArr(i) & "'"
- Set rsADO = cnADO.Execute(querySql)
- If rsADO.Fields(0).Value > 0 Then
- resultArr(i, 1) = "是"
- Else
- resultArr(i, 1) = "否"
- End If
-
- '查询赣州市建档立卡户
- querySql = "SELECT COUNT(*) FROM gzsjdlk WHERE 身份证号码='" & stuArr(i) & "'"
- Set rsADO = cnADO.Execute(querySql)
- If rsADO.Fields(0).Value > 0 Then
- resultArr(i, 2) = "是"
- Else
- resultArr(i, 2) = "否"
- End If
-
- '查询赣州市城镇贫困人口
- querySql = "SELECT COUNT(*) FROM gzsczpk WHERE 身份证号码='" & stuArr(i) & "'"
- Set rsADO = cnADO.Execute(querySql)
- If rsADO.Fields(0).Value > 0 Then
- resultArr(i, 3) = "是"
- Else
- resultArr(i, 3) = "否"
- End If
- Next
- Sheets(1).Range(Cells(4, 11), Cells(countRow, 13)) = resultArr
- cnADO.Close
- Application.ScreenUpdating = True '打开屏幕更新
- MsgBox ("匹配结束!")
- Exit Sub
- ErrMsg:
- MsgBox Err.Description, , "错误报告"
- End Sub
复制代码 为了加快执行速度,我还将需要查询的数据身份证号码读取到数组中循环到数据库中去查询。
|
|