VBA连接SQL Server

在VBA中直接与SQL服务器建立连接

Set Conn = CreateObject("adodb.connection")

'定义连接的SERVER名，数据库名，用户名和密码

Conn.Open "Driver=SQL Server;SERVER=DB001;Database=DBtest;uid=userID;pwd=password"

'判断连接状态

If Conn.State = 1 Then

         '定义SQL文

        sqll = "SELECT * FROM table1"

        '将结果显示到a2单元格

        [a2].CopyFromRecordset Conn.Execute(sqll)

         '下面语句是添入标题，视需要而添加

         [a1] = "序号": [b1] = "标题": [c1] = "..."

         '有数据行的统计

        newnumber = ActiveSheet.UsedRange.Rows.Count

        If newnumber > 1 Then

        MsgBox "有数据返回"

        End If

End If

Conn.Close '关闭连接

Set Conn = Nothing '释放连接