【VBA研究】Excel VBA利用ADODB访问数据库使用小结

作者:iamlaosong

 

▲连接Oracle数据库

    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=msdaora;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"

    dl580是tnsnames.ora中配置的服务名。

▲创建记录集recordset

    Set rst = CreateObject("ADODB.Recordset")
    sqls = "select * from tb_city"
    Set rst = cnn.Execute(sqls)

    注意:Set rst = cnn.Execute(sqls)如果用rst.Open sqls, cnn, adOpenKeyset, adLockOptimistic代替,效果更好。

    这儿的SQL语句除了查询语句select以外,也可以用insert、update、delete等命令,也可以用truncate table这样的命令。这些SQL语句也可以用command对象完成。command对象还可以进行存储过程的调用并传递参数。

▲记录集结果的引用

Excel VBA 通过ADO取到数据后,有如下引用方式(假定记录集名字为rst):

1、字段数量:rst.fields.count;

2、字段名称:rst.fields(0).name,0表示第一个字段,其它字段分别是1,2,...rst.fields.count-1;

3、字段值:rst(0)或者rst(0).Value或者rst.fields(0).value,0表示第一个字段,也可以用字段名代替,如:rst("city")

4、整个记录集: Range("a2").CopyFromRecordset rst命令将记录集保存到A2单元格开始的工作表中;

5、记录移动:取数后记录定位在第一条,rst.movenext可以移动到下一条,结尾标志:rst.eof = true,Excel VBA只支持向前移动(rst.movenext);例如:

        Do While Not rst.EOF()
            k = k + 1
            sku(k, 1) = rst(0)
            sku(k, 2) = rst(1)
            rst.MoveNext
        Loop

6、记录数:rst.RecordCount是反馈记录数的,是否有值要看SQL语句的执行方式。

执行SQL语句有三种方式,一种是cnn. Execute,这种方式比较适合无返回记录的语句,即DML语句。如果执行有返回记录的SQL语句,也可以取到记录,只是RecordCount总是反馈-1。这种情况下可以根据rst.eof判断有无查询结果,如果rst.eof= true就表示查询结果为空。另一种方式是rst.Open,这个适合有返回记录的语句,即select语句,因为这种方式能够返回记录数RecordCount。当然还有第三种方式,就是用command,这个比较适合执行存储过程,因为这种方式可以传递参数。三种方式command方式功能最强,用起来也最麻烦,connection最弱,用起来也最简单

▲调用存储过程

1、连接数据库,如前;

2、定义存储过程

     Set cmd = CreateObject("ADODB.Command")

    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "zfqf_bag2mail"   '存储过程名称,有两个参数
    cmd.CommandType = adCmdStoredProc

    注意:CommandType的值决定了CommandText内容的类型,其取值定义如下:

    '---- CommandTypeEnum Values ----
    'Const adCmdUnknown = &H8
    'Const adCmdText = &H1
    'Const adCmdTable = &H2
    Const adCmdStoredProc = &H4
    'Const adCmdFile = &H100
    'Const adCmdTableDirect = &H200

3、参数赋值

     cmd.Parameters(0).Value = Cells(row1, pos_acc)
     cmd.Parameters(1).Value = Cells(row1, pos_lab)
4、执行存储过程 

    cmd.Execute

这个存储过程的结果保存在表中,通过查询语句得到结果,没有通过变量返回。


===================================

附:ADODB.RecordSet常用方法

      rs = Server.CreateObject("ADODB.RecordSet") 
  rs.Open(sqlStr,conn,1,A) 
  注:A=1表示读取数据;A=3表示新增、修改或删除数据。 
  在RecordSet组件中,常用的属性和方法有: 
  rs.Fields.Count:RecordSet对象字段数。 
  rs(i).Name:第i个字段的名称,i为0至rs.Fields.Count-1 
  rs(i):第i个字段的数据,i为0至rs.Fields.Count-1 
  rs("字段名"):指定字段的数据。 
  rs.Record.Count:数据记录总数。 
  rs.EOF:是否最后一条记录。 
  rs.MoveFirst:指向第一条记录。 
  rs.MoveLast:指向最后一条记录。 
  rs.MovePrev:指向上一条记录。 
  rs.MoveNext:指向下一条记录。 
  rs.GetRows:将数据放入数组中。 
  rs.Properties.Count:ADO的ResultSet或Connection的属性个数。 
  rs.Properties(item).Name:ADO的ResultSet或Connection的名称。 
  rs.Properties:ADO的ResultSet或Connection的值。 
  rs.close():关闭连接。 


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/iamlaosong/article/details/18043433
文章标签: excel vba oracle
上一篇VBA-"找不到工程或库" 解决方案
下一篇【VBA研究】VBA中如何用求和函数SUM求和
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭