Excel通过VB查询Access数据库

Sub 按钮5_Click()

Dim uADO, rsADO As Object

Dim strPath, strSql As String

Dim i As Integer, ri As Integer

 Dim D1 As Date

    '定义日期变量

    Dim D2 As Date

    '定义表示Ecxel行数的变量

Set uADO = CreateObject("ADODB.Connection") '新建数据库连接对象

Set rsADO = CreateObject("ADODB.RecordSet") '新建记录集对象

strPath = ThisWorkbook.Path & "\人力资源管理系统.mdb " '设置数据库地址

uADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath '打开数据库

 D1 = InputBox("请输入需要提数的日期，例如：2011-9-4", "提数日期")

     '将D2赋值为D1+1

    D2 = D1 + 1

 

 strSql = "SELECT 订单编号,品名,型号1, 型号2,型号3, 重量, 件数 FROM 个人简历 WHERE 离开日期<#" & D2 & "# AND 离开日期>=#" & D1 & "#" '设置查询语句

rsADO.Open strSql, uADO, 1, 3 '执行查询

Dim R As Range

Set R = ActiveSheet.Range("B2")

ActiveSheet.UsedRange.Clear

For i = 0 To rsADO.Fields.Count - 1

R.Offset(0, i).Value = rsADO.Fields(i).Name '输出字段名

Next i

Set R = R.Offset(1, 0)

Do Until rsADO.EOF '循环查询到数据 直到记录最后一条

For i = 0 To rsADO.Fields.Count - 1

R.Offset(ri, i).Value = rsADO.Fields(i).Value '输出数据表内容

Next i

ri = ri + 1

rsADO.MoveNext '指向下一条记录

DoEvents

Loop

rsADO.Close '关闭记录集

Set uADO = Nothing

Set rsADO = Nothing

End Sub