vba 连接数据库

Private Sub CommandButton1_Click()
'连接ms sql

Dim conn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用
Dim rs As New ADODB.Recordset  '定义记录集对象，保存数据表

Dim sql, strCn As String
Dim i
Dim outstr

sql = "select repid,fid,bm,sname,catid from tabReport order by catid,fid"
'两种链接串写法
strCn = "Provider=sqloledb;Server=127.0.0.1;Database=Report;uid=sa;pwd="
'strCn = "Provider=SQLOLEDB.1;Persist Security Info=false; User ID=sa;Password=;Initial Catalog=Report;Data Source=(local);"

conn.Open strCn
rs.Open sql, conn, adOpenKeyset, adLockOptimistic, adCmdText

'Set rs = conn.Execute(sql)

rs.MoveFirst
For i = 1 To rs.RecordCount
    Cells(i, 1).Value = rs("sname") 'rs.Fields(0).Value
    Cells(i, 2).Value = rs("catid") 'rs.Fields(2).Value
    rs.MoveNext
Next

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub