需求：使用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.CloseEnd 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.CloseEnd Sub
- 最后一步，在sheet1的ThisWorkBook中写入显示步调1的毗连界面代码。
至此，简单数据库毗连功能都已完成，有需要的可以添加，我这个每次执行sql都需要毗连数据库，关闭毗连，没有优化，可以自行修改。
第一次使用的话需要将sheet2删除！不删可能会报错。
例子放入在了百度网盘，有需要的自取。
链接: https://pan.baidu.com/s/1tCix95JM17FMttFO4sTGMg 提取码: adxf 复制这段内容后打开百度网盘手机App，操纵更方便哦
感谢阅读！
来源：https://blog.csdn.net/qq_42665745/article/details/112002289
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
