网络教育本科统考:VBA连接数据库 - ExcelVBA - 凡心

来源:百度文库 编辑:宝宝故事网 时间:2019/10/17 13:51:18

VBA连接数据库 Dim i As Long, j As Long
    Dim sSql As String, sht As Worksheet, sht2 As Worksheet
    Dim rs As New ADODB.Recordset
    Dim oConn As New ADODB.Connection
    Dim strConn As String '连接串
    Dim id As String
   
    'MsgBox "ddd"

   
    '-连接数据库-
    strConn = "DRIVER=SQL Server;UID=Anonymous;Trusted_Connection=Yes;DATABASE=FLF;WSID=VMW;APP=Microsoft Data Access Components;SERVER=w-shpd-dev;"
    '连接串
    oConn.Open (strConn)
   
    '取得工作sheet
    Set sht = Workbooks(1).Worksheets(1)
    Set sht2 = Workbooks(1).Worksheets(2)
   
   
   
    For j = 0 To 50000
    id = sht.Cells(j + 1, 3)
    sSql = "select * from sheet1$ where [Inquiry Nbr]=" & id & " 'sql语句"
    rs.Open sSql, oConn '打开数据集
   
    i = 1
   
    If rs.EOF Then
    'MsgBox "null"
   
    Else
    Do While Not rs.EOF
      sht2.Cells(i, 1) = rs("Activity Id")
      sht2.Cells(i, 2) = rs("First Name")
      sht2.Cells(i, 3) = rs("Inquiry Nbr")
      rs.MoveNext
      i = i + 1
    Loop
    End If
    Next
    '关闭连接释放数据
    rs.Close
    oConn.Close

 原文地址 http://ken_kang.blog.bitscn.com/archives/2007/16634.html