vba连接数据 代码 （excel/access/sqlserver)

                  Sub excel连接数据库()

    Dim Con As New ADODB.Connection
    Dim strCon As String
    Dim rs As ADODB.Recordset  '设置记录集
    Dim i, t
    t = Timer

    strCon = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source= D:\平台\报表\账户流水.xlsx" & _
                ";Extended Properties=""Excel 12.0;HDR=True"";"

    strSQL = "select * from [账户流水$]"

    Con.Open strCon

    Set rs = Con.Execute(strSQL)

    For i = 0 To rs.Fields.Count - 1 '逐个字段

        Cells(3, i + 3) = rs.Fields(i).Name '取字段名  字头放置在cell(3,3)

    Next i

    Range("c4").CopyFromRecordset rs

    rs.Close

    Con.Close

    Set rs = Nothing

    Set Con = Nothing

    MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"

End Sub



Sub access连接并查询()


    Dim Con As New ADODB.Connection
    Dim strConn As String
    Dim rs As ADODB.Recordset  '设置记录集
    Dim i, t
    t = Timer

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=C:\Users\Administrator\Desktop\test.accdb"

    strSQL = "select * from 账户流水"

    Con.Open strConn

    Set rs = Con.Execute(strSQL)

    For i = 0 To rs.Fields.Count - 1 '逐个字段

        Cells(3, i + 3) = rs.Fields(i).Name '取字段名  字头放置在cell(3,3)

    Next i

    Range("c4").CopyFromRecordset rs

    rs.Close

    Con.Close

    Set rs = Nothing

    Set Con = Nothing

    MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"

End Sub




Sub sqlserver连接并查询()

    Dim Con As New ADODB.Connection
    Dim strCon As String
    Dim rs As ADODB.Recordset  '设置记录集
    Dim i, t
    t = Timer

    '连接远程数据库

    strCon = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _

              "User ID=sa;Pwd=123456;" & _

              "Initial Catalog= gydataH5 ;Data Source= 192.168.1.5 ;"

    '连接本地数据库

    ' strCon = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
    '           "User ID=sa;Pwd=;" & _
    '           "Initial Catalog= AdventureWorks2008;Integrated Security=SSPI"


    strSQL = "Select * from smscodelog"

    'strSQL = "Select * from person.person"

    Con.Open strCon

    Set rs = Con.Execute(strSQL)

    For i = 0 To rs.Fields.Count - 1 '逐个字段

        Cells(3, i + 3) = rs.Fields(i).Name '取字段名  字头放置在cell(3,3)

    Next i

    Range("c4").CopyFromRecordset rs

    rs.Close

    Con.Close

    Set rs = Nothing

    Set Con = Nothing

    MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"


End Sub