Excel VBa 连接Oracle数据库

最新推荐文章于 2021-11-17 08:00:29 发布

原创最新推荐文章于 2021-11-17 08:00:29 发布 · 4.4k 阅读

38 ·

CC 4.0 BY-SA版权

文章标签：

#excel #vba #oracle

其他专栏收录该内容

13 篇文章

订阅专栏

本文介绍了如何使用Excel创建一个简单的数据库连接工具，包括连接数据库、查询表、更新数据等功能。通过VBA代码实现了ADODB连接Oracle数据库，展示查询结果，并提供了插入/修改数据的集成方法。用户界面包含连接、查询和更新按钮，操作直观易用。

需求：使用Excel做一个简单的数据库连接工具(类似于java中的AWT和SWING)。

可以查询数据库的表
查询各个表内的数据
插入/修改各个表的数据

步骤：

先建立一个UserForm作为主界面，并放置按钮，如下图所示。

然后双击中间的“连接数据库”按钮，进行代码编辑。

Private Sub CommandButton1_Click()

    Dim strConn As String  
    Dim dbConn As Object
    Dim resSet As Object
    
    Dim db_sid, db_user, db_pass As String
    
    db_sid = "edu-server.yds.yd:1521/orcl.yds.yd"
    db_user = "root"
    db_pass = "123456"
    
    Set dbConn = CreateObject("ADODB.Connection")
    Set resSet = CreateObject("ADODB.Recordset")
    
    strConn = "Provider=OraOLEDB.Oracle.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"
    
    //清空sheet1   
    Sheets("Sheet1").Range("1:65536").ClearContents
 
    dbConn.Open strConn
    
    //查询当前数据库所有表名
    Set resSet = dbConn.Execute("select table_name from user_tables")
    
    //将表名放入sheet1中
    For j = 0 To resSet.Fields.Count - 1
      Cells(1, j + 1) = resSet.Fields(j).Name
    Next
    
    //将Sheet1中的A2:A20(A20写成了固定的，也可以写成变量)放到主界面中，下面是主界面
    UserForm2.ListBox1.RowSource = "Sheet1!A2:A20"
    
    Range("A2").CopyFromRecordset resSet
    
	//跳转到主界面
    UserForm2.Show 0
    //隐藏当前界面
    UserForm1.Hide
    
    dbConn.Close
    
End Sub

使用UserForm创建一个主界面，并放入一些控件，如下图。

左侧空白部分是一个ListBox，点击步骤1界面的“连接数据库”按钮后，跳转到当前步骤的界面，并将表明显示到左侧的ListBox中，效果图如下。

双击“查询”按钮，进入到查询功能编写。

Private Sub CommandButton1_Click()

    Dim strConn As String
    Dim strSql As String
    Dim iCount As Integer
    Dim whm As Worksheet
    
    Dim dbConn As Object
    Dim resSet As Object
    
    Dim db_sid, db_user, db_pass As String
    
    db_sid = "edu-server.yds.yd:1521/orcl.yds.yd"
    db_user = "root"
    db_pass = "123456"
    
    Set dbConn = CreateObject("ADODB.Connection")
    Set resSet = CreateObject("ADODB.Recordset")
    
    strConn = "Provider=OraOLEDB.Oracle.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"
	
	//根据鼠标选中的表名拼接sql
    strSql = "select * from " & ListBox1.Value
    
      //判断sheet2是否存在
      If ActiveSheet.Name = "Sheet2" Then
      	 //存在就清空sheet2
         Sheets("Sheet2").Range("1:65536").ClearContents
      Else
         //不存在就创建sheet2
         Worksheets.Add.Name = "Sheet2"
      End If
      
    dbConn.Open strConn
    
    //执行sql
    Set resSet = dbConn.Execute(strSql)
    
    //将查询到的数据放入sheet2中
    For j = 0 To resSet.Fields.Count - 1
      Cells(1, j + 1) = resSet.Fields(j).Name
    Next
    
    Range("A2").CopyFromRecordset resSet
    
    dbConn.Close

End Sub

效果图如下。
在这里插入图片描述

3.双击“更新”按钮，进入更新功能代码编写。
这里我的想法是将插入和修改放到了一起,点击“更新”按钮会后，先清空当前表
,再将sheet2中的数据全部插入到表中,这样就将插入和修改整合到了一起，但是效率可能会低.

Private Sub CommandButton2_Click()

    Dim strConn1 As String
    Dim truncateSql As String
    Dim insertSql As String
    
    Dim dbConn1 As Object
    Dim resSet1 As Object
    
    Dim maxRow As Integer
    Dim maxColumn As Integer
    
    Dim db_sid1, db_user1, db_pass1 As String
    
    db_sid1 = "edu-server.yds.yd:1521/orcl.yds.yd"
    db_user1 = "root"
    db_pass1 = "123456"
    
    Set dbConn1 = CreateObject("ADODB.Connection")
    Set resSet1 = CreateObject("ADODB.Recordset")
    
    //获取最大行数
    maxRow = ActiveSheet.[A65536].End(xlUp).Row
    //获取最大列数
    maxColumn = ActiveSheet.[IV1].End(xlToLeft).Column
    
    strConn1 = "Provider=OraOLEDB.Oracle.1; user id=" & db_user1 & "; password=" & db_pass1 & "; data source = " & db_sid1 & "; Persist Security Info=True"

	//拼接清空表sql
    truncateSql = "truncate table " & ListBox1.Value
    //拼接插入sql
    insertSql = "insert into " & ListBox1.Value & " values( "
       
    dbConn1.Open strConn1
    
    Set resSet1 = dbConn1.Execute(truncateSql)
    
    //循环，第一行是表字段名，从第二行开始，j=2
    For j = 2 To maxRow
    
    	//取每一行所有有效列的值，拼到插入语句的sql中
        For i = 1 To maxColumn
            If i = maxColumn Then
                insertSql = insertSql & "'" & Cells(j, i) & "')"
            Else
                insertSql = insertSql & "'" & Cells(j, i) & "',"
            End If
        Next
        
        //拼完插入语句的sql，就执行
        Set resSet1 = dbConn1.Execute(insertSql)

		//将sql重置成最初状态，以便下一行的拼接
        insertSql = "insert into " & ListBox1.Value & " values( "
    Next
    
    //增删改需要提交事务，这里我不清楚需不需要，写上肯定没错
    Set resSet1 = dbConn1.Execute("commit")
    
    //弹出提示框，提示更新成功！
    MsgBox prompt:="     更新成功!     "
    
    dbConn1.Close

End Sub