高效办公-VBA數據庫應用

    本文介绍一写VBA 操作数据库一些应用案例与最优应用架构，应用在强大的所见即所得数据处理工具Excel基础上, 无疑vba 将数据处理能力发挥的尽显其能, 这个信息化的时代VBA应用总是少不了它的身影。任何问题欢迎留言讨论。----Janus

 

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

        Dim cn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        Dim conn_string As String   
    conn_string = "provider = SQLOLEDB.1;Password=Sa123456;Persist Security Info=True;User ID=sa;Initial Catalog=HESDB;Data Source=192.168.32.159"
    cn.ConnectionString = conn_string
    cn.Open
    rst.Open "SELECT * FROM HSE_base ", cn, adOpenStatic, adLockBatchOptimistic
    Dim i As Integer
    i = 2
  Cells(1, 1) = "UID"
    Cells(1, 2) = "ITEM"
    Cells(1, 3) = "text1"
    Do While Not rst.EOF
    Cells(i, 1) = rst(0)
    Cells(i, 2) = rst(1)
    Cells(i, 3) = rst(2)
    i = i + 1
    rst.MoveNext
    Loop              
MsgBox "Finish!!", vbInformation, ""

 

 

     Dim cn As New ADODB.Connection  ‘定義數據庫連接對象
     Dim rst As New ADODB.Recordset   ‘定義數據集合對象
     Dim conn_string As String                 ‘定義連接字符串
     conn_string = “provider = SQLOLEDB.1;Password=Sa123456;Persist Security Info=True;User ID=sa;Initial Catalog=HESDB;Data Source=192.168.32.159“                          ‘聲明連接字符串信息
     cn.ConnectionString = conn_string    ‘連接字符串賦予數據庫連接對象
     cn.Open    ‘連接數據庫對象

     rst.Open “SELECT * FROM HSE_base ”, cn, adOpenStatic, adLockBatchOptimistic                       ‘ 通過rst 取得數據庫查詢結果集
    rst.Close ‘關閉记录集
    cn.Close‘關閉資料庫連結,釋放资源
    Set  rst= Nothing‘清空對象
    Set cn = Nothing '清空對象

 

 

 

 

 

 

Private Sub QuitWithoutSave()
   SQLSERVER.Show
End Sub
 Private Sub Auto_Open()
Dim cbar  As CommandBarButton
'Set cbar = CommandBars("Standard").Controls("ShippingPlan")
Set cbar = CommandBars("Standard").FindControl(Type:=msoControlButton, Tag:="SP")
'CommandBars("Standard").Controls("ShippingPlan").Delete
If Not cbar Is Nothing Then
    Exit Sub
End If

    With CommandBars("Standard")
        .Protection = msoBarNoProtection
        
           ' Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, ID:=2950, Before:=4
        With .Controls.Add(msoControlButton, ID:=2950, before:=3)
            .DescriptionText = "QuitWithoutSave"
            .Caption = "Sqlserver"
            .TooltipText = "Sqlserver"
            .Style = msoButtonIconAndCaption
            .OnAction = "QuitWithoutSave"
            .Tag = "SP"
        End With
    
    End With
End Sub