Excel VBA利用ADODB访问数据库使用小结

最新推荐文章于 2024-08-19 13:18:27 发布

haha_71

最新推荐文章于 2024-08-19 13:18:27 发布

阅读量8.6k

点赞数 1

分类专栏： VBA

VBA 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

连接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)

这儿的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)；

6、记录数：rst.RecordCount是反馈记录数的，但在VBA中却总是反馈-1，不过可以根据rst.eof判断有无查询结果，如果rst.eof= true就表示查询结果为空。

▲调用存储过程

1、连接数据库，如前；

2、定义存储过程

Set cmd = CreateObject("ADODB.Command")

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

3、参数赋值

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

cmd.Execute

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

链接样例：

 
        Sub InputData() 
       
        Dim cn As New ADODB.Connection 
       
        Dim rs As New ADODB.Record 
       
        Dim cnStr As  
        String 
        , sqlStr As  
        String 
       
        Dim datarg 
       
        cnStr =  
        "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=[LoginName];Password=[password]@;Initial Catalog=[DBName];Data Source=[IP];Connect Timeout=720;" 
       
        sqlStr = ThisWorkbook.Worksheets( 
        "Parameters" 
        ).[B2]      ' 
        //存放SQL语句 
       
        cn.Open cnStr 
       
        ThisWorkbook.Worksheets( 
        2 
        ).[a2].CopyFromRecordset ConnectRst(cn, sqlStr) 
          
        cn.Close 
       
        End Sub 
       
        Function ConnectRst(cnn As ADODB.Connection, Sql As  
        String 
        ) As ADODB.Recordset 
       
        Dim Rst As New ADODB.Recordset 
       
        Rst.Open Sql, cnn, adOpenStatic 
       
        Set ConnectRst = Rst 
       
        End