VBA中用ADO访问SQL SERVER数据库：数据查询

ph033378

本程序的作用是：使用数据对象ADO访问SQL数据库，从而进行数据查询，并将查询的数据返回到EXCEL中，由于此程序需用到SQL数据库，所以只供大家参考，不便运行，但大家可以看出其精华！
  Private Sub CommandButton1_Click()

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim R, C, F, I As Integer
Dim Sql_text, day1, linenumber, box As String


Const cnnstr = "Provider = SQLOLEDB;" & _
         "Data Source = apsgszml04;" & _
         "Initial Catalog = bhl2ken;User ID =sa;Password =;"

‘连接数据库

day1 = UserForm1.TextBox1.Text
linenumber = UserForm1.ComboBox1.Text
box = UserForm1.ComboBox2.Text


cn.Open cnnstr
Sql_text = Sql_text & "SELECT CONVERT(Char,dbo.TRY123.[day],101) as date1,"
  Sql_text = Sql_text & " dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.lotnumber"
  Sql_text = Sql_text & " FROM dbo.TRY123"
  Sql_text = Sql_text & " WHERE (CONVERT(Char,dbo.TRY123.[day],101)= '" & day1 & "' and dbo.TRY123.linenumber= '" & linenumber & "' and dbo.TRY123.box_no= '" & box & "')"
  Sql_text = Sql_text & " ORDER BY dbo.TRY123.serialnumber "
‘使用SQL数据库查询语言查询

'SELECT CONVERT(Char,dbo.TRY123.[day],101) as date1,dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.lotnumber
'
'FROM   dbo.TRY123
'WHERE   (CONVERT(Char,dbo.TRY123.[day],101)= " & day1 & " and dbo.TRY123.linenumber= " & linenumber & " and dbo.TRY123.box_no= '" & box & "'
'ORDER BY dbo.TRY123.serialnumber


rst.Open Sql_text, cn, adOpenStatic, adLockBatchOptimistic '用adOpenStatic + adLockBatchOptimistic打开较快，且占用资源小

R = 5 'Excel表的行序号
C = 3 'Excel表的列序号
I = 0 'SQL表的字段序号
F = rst.Fields.Count - 1
Worksheets("sheet1").Unprotect
Worksheets("sheet1").Cells.ClearContents
While Not rst.EOF
  For I = 0 To F
  Sheet1.Cells(R, I + 3).Rows.Value = rst.Fields(I).Value
  Next I
  R = R + 1
  rst.MoveNext  ’将数据库的数据返回到EXCEL表中
Wend
Worksheets("sheet1").Protect
UserForm1.Hide
'MsgBox ("读取完毕")
rst.Close '完成后要关闭
cn.Close  '完成后要关闭
'注意：以上为直接连接SQL Server 的方法，不用建ODBC数据源
Worksheets("sheet1").Activate
End Sub