|
楼主 |
发表于 2013-6-26 17:10
|
显示全部楼层
Private Sub ComboBox1_DropButtonClick()
Dim cn As New ADODB.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量
Dim dt1 As Date, dt2 As Date
Dim mydata As String, mytableH As String, mytableP As String, DataSource As String
dt1 = DTPicker1.Value
dt2 = DTPicker2.Value
'连接数据库
mydata = "recipe_db"
mytableH = "new_report_HeadInfo"
mytableP = "new_report_Param"
DataSource = ThisWorkbook.Sheets("HomePage").Range("DataSource").Value
strCn = "Provider = SQLOLEDB.1;" & "Password = recipe_db_user;" & "User ID = recipe_db_user;" _
& "Data Source =" & DataSource & "; Initial Catalog = " & mydata
On Error Resume Next
cn.Open strCn
On Error GoTo 0
If cn.State = adStateOpen Then
Else
MsgBox "未能成功连接SQL数据库" & mydata & ",请检查!", vbCritical, "数据保存"
Exit Sub
End If
strSQL = "select DISTINCT Batchno from " & mytableH & " where Date>='" & dt1 & "'" & "and Date<='" & dt2 & "'"
rs.Open strSQL, cn, 0, 1
On Error Resume Next
rs.MoveFirst
If rs.RecordCount <> 0 Then
Do While Not rs.EOF
ComboBox1.AddItem rs.Fields("Batchno")
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub |
|