Excel编程连接数据库

《Excel编程连接数据库》由会员分享，可在线阅读，更多相关《Excel编程连接数据库（3页珍藏版）》请在装配图网上搜索。

1、Excel一个重要的数据分析平台，自然就少不了与数据库连接。在Excel上就有直接打开数据源的选项，如下图所示：尽管这种方式用起来很方便，这种连接数据源的方式和其他功能一样，不是很灵活。查到的数据会被放到一个工作表里，并做自动筛选。如果我们有特别的要求，比如希望将数据表的左左上角定位在B10单元格，这种方式就不能满足要求。另外，鉴于SQL的强大，这些“好用”的功能到了高级用户手里就会一无是处。最后，这种方式不能把Excel的数据Insert到数据库中。所以，利用VBA连接并操作数据库就显得很必要了。要相操纵数据库，首先添加对Microsoft ActiveX Data Objects X.X

2、Library的引用。其中的X.X是版本号。对于本教程使用的功能来讲，版本号是无所谓的根本用不到那些新功能。这个库是一个把数据库操作对象化的库，使用起来和其他的库很类似。然后就可以写一个Sub来连接数据库了。首先，需要新建一个连接对象。Dim con As New ADODB.Connection注意其中的New关键字用于创建一个新的对象。这也可以写成Dim con As ADODB.ConnectionSet con = New ADODB.Connection然后，就要设置连接字符串了。在这个例子中，我建了一个Access 2007的数据库mydb来做实验。这样，连接字符串就是：con.C

3、onnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:MyDocDocumentsmydb.accdb当然，如果你连接其他的数据库，连接字符串是不一样的，比如连接SQL Server就可能是Provider=SQLOLEDB;Data Source=db source;DATABASE=db name;UID=user; pwd=password每种数据库都有自己的连接字符串格式，你需要查阅文档才能知道到底该怎么写这个连接字符串。不过连接字符串的一般格式是一致的，即一组被分号;分开的key=value的项目。Prov

4、ider是指数据库的驱动类型；Data Source是指数据库文件；Database是指数据库名；UID是用户名；pwd是密码；有的时候，需要用Windows集成帐户校验，这时还要加上Integrated Security=sspi。其外还有很多其他的项目，例如通讯方式，永续化方式，字符编码等。这些项目大多可选，如果不写也可以连上数据库，只有在希望对连接方式进行精细调整的情况下才需要写它们。设置好了连接字符串后，就可以用以下代码打开数据库con.Open最常见的数据库使用方式就是查询了。为了存储查询结果，必须首先新建一个记录集对象。Dim rs As New ADODB.RecordSet然后

5、可以定义一条SQL查询语句queryText = select * from Person你可以书写任意复杂的SQL语句，并将其拼成一个字符串。然后，就可以执行查询了。rs.Open queryText, con可以看到RecordSet的Open方法在这里接收两个参数，第一个是查询语句字符串，第二个是打开的数据库连接。当执行完这句后，查询结果就已经存储在rs之中了。当然，如果数据量巨大，就会花一些时间。接下来的工作就是将查询结果从rs中读出，并显示在Excel工作表里。这里首先书写列头。ActiveSheet.Cells(1, i).Value = rs(i - 1).NameNextrs内

6、部会有一个游标（Cursor）。刚查询完后，游标指向查询结果的第一条记录。每条记录有若干列，每一列被称为一个域（Field）。rs(index)就是指当前指向记录的第index个域。上面这段代码遍历每一个域，并将其Name显示出来，放到工作表第一行的对应列中。需要注意，Office的库中的索引一般从1开始计数，而数据库的索引都是从0开始计数的（因为它们都是用C/C+开发的）。所以rs的索引要用i-1。执行效果如下：接下来就是遍历每条记录，将所有的数据显示出来。r = 2While (Not rs.EOF)ActiveSheet.Cells(r, i).Value = rs(i - 1).Val

7、ueNextrs.MoveNextr = r + 1Wend遍历以rs.EOF=false为条件。当EOF=true时，就说明游标已经到了所有查询结果的末尾。对于每一条记录，可以用rs(index).Value获取它的值。处理完一条记录后，用rs.MoveNext来将游标定位在下一条记录。执行后，可以看到效果：当然，如果你想直接把rs的所有数据显示在单元格里有更简单的做法。比如希望显示位置的左上角在A2单元格，直接写一句Range.copyFromRecordSet就可以了。Range(A2).CopyFromRecordset rs这个和上面显示数据的效果完全相同。不过它不会帮你把列头显示出

8、来。好了，说完了读取数据库，再说说修改数据库。修改数据库有Insert，Update和Delete三种。可以书写SQL语言直接进行这种操作。这里以Insert为例。在数据库连接打开后，直接用Connection.Execute来执行一个修改数据库的SQL命令，如下所示。con.Execute Insert Into Person(PName, Gender) Values(Linda, F)就能将数据插入到数据表中。因为PID在这个例子中是自动生成的，所以可以不用为它赋值。执行这条指令后，就会在数据库中看到下面的效果然而RecordSet为我们提供了更方便的方式。在查询完成之后，我们可以直接对

9、RecordSet进行修改，比如:rs(PName).Value = New Name modify the current record.rs.AddNew add a new recordrs(PName).Value = Ivyrs(Gender).Value = M.rs.Delete delete the current record等等。最后执行rs.UpdateRecordSet就会根据我们的修改，自动将插入，删除，和更新应用到数据库里。所以如果希望将工作表数据导入到数据库，就可以直接将数据放到一个空白的数据集中，然后再Update即可。当然SQL Server的T-SQL有更加

10、方便的语句Open Source可以直接导入Excel数据。不过因为这是VBA教程，就不介绍它了。不过为了使得这种修改可以执行，必须在rs被打开时指定可进行读写操作。就是说必须书写Record.Open的第四个参数。下面具体说一下其第三个和第四个参数的含义。rs.Open queryText, connection，cursorMode, lockMode其中cursorMode可以是：0 = adOpenForwardOnly 打开仅向前类型游标。1 = adOpenKeyset 打开键集类型游标。2 = adOpenDynamic 打开动态类型游标。3 = adOpenStatic 打开静

11、态类型游标。而lockMode可以是：1 = adLockReadOnly （默认值）只读 不能改变数据。2 = adLockPessimistic 保守式锁定（逐个） 在编辑时立即锁定数据源的记录。3 = adLockOptimistic 开放式锁定（逐个） 只在调用 Update 方法时才锁定记录。4 = adLockBatchOptimistic 开放式批更新 用于批更新模式（与立即更新模式相对）。所以lockMode的默认值不允许rs.Update，需要手动设置一个可以修改的lockMode。程序的最后请务必记住要关闭数据库连接。conn.Close另外，最好将两个对象的引用取消掉，使其可以释放空间：conn=Nothingrs=Nothing这样做可以避免资源的浪费。