经典代码解析4.1：使用ADO整合Excel与Access

L罗乐 2017-07-06

展开全文

Excel擅长数据分析，而Access擅长整理和存储大量繁杂的数据。将Excel与Access整合起来协同运用，发挥各自的优势，从而满足更复杂的需求。

搭建环境

在你的电脑中，除安装了Excel外，还需要安装有Access 2007及以上版本的软件。更重要的是，要在VBE中添加对ADO的引用：

在VBE中，单击菜单栏中“工具”下的“引用”，在“引用”框中，选取“Microsoft ActiveX Data Objects ×.× Library”和“Microsoft ADO Ext. ×.× for DLL And Security”前的复选框，单击“确定”。

Excel工作表如下图所示：

代码功能

下面的几段代码实现下列功能：

使用VBA创建Access数据库
使用Excel中的数据填充数据库
通过Excel添加/删除字段，并填充/删除相应的数据
在Excel中获取Access中的数据
Excel中的数据修改后，Access数据库实现更新

程序代码及解析

创建并填充Access数据库

下面的代码首先创建一个Access数据库及表，然后使用Excel工作表中的数据填充该数据库表。

解析：

上述代码创建一个新的Catalog对象，用于使用ADOX来引用数据库。创建一个名为变量strTableName代表的值的表，并且为新创建的表添加字段，本例中添加了5个字段，你可以根据实际修改来增加或减少字段以及改变字段的名称；将新表添加到数据库的Tables集合中。
接下来，代码为新表创建主键。连接到已有的数据库，删除数据库表中已有的主键，并创建新的主键。
随后，基于数据表定义记录集，每次遍历工作表中的一行并将数据存放到Access数据库表。其中，在循环中，rst.AddNew创建一条新记录，然后遍历每一列，将列中的值赋给记录集中的每个字段，rst.Update保存记录。
最后，代码关闭Recordset对象和Connection对象并将对象变量设置为Nothing以清除内存。

运行代码后，Access数据库表如下图所示：

添加新字段

如果在Excel工作表中增加了新标题，例如“班级”（如下图所示），那么在数据库表中也要添加“班级”字段。

在Access数据库表中添加字段的代码如下：

解析：

代码首先创建新的ADO连接，然后创建ADOX Catalog对象（数据库）并让其使用ADO连接，接下来设置对想要修改的表的引用，并添加列。最后，清除引用对象。

运行代码后的数据库表如下：

也可以使用SQL语句来添加字段，代码如下：

解析：

代码首先创建新的ADO连接，然后创建Command对象并让其使用ADO连接，接着执行SQL语句来添加新字段，最后清除对象引用。

为新字段添加数据

下面的代码为刚才添加的字段填充数据：

解析：

需要遍历工作表中的行，每次修改一条记录中的字段内容。
代码创建ADO连接，根据主键值逐条选取记录并打开，然后修改记录，保存并关闭记录集，最后关闭连接并清除对象引用。

结果如下图所示：

删除字段

下面的代码删除上文中添加的“班级”字段：

解析：

与前面的代码一样，首先创建新的ADO连接并创建ADOX对象使之使用刚创建的ADO连接，然后设置对想要修改的数据库表的引用，接着通过从表的Columns集合中删除指定的列，最后清除对象引用并关闭连接。

也可以使用SQL查询语句来实现，代码如下：

解析：

代码创建新的ADO连接，随后创建一个Command对象并使之使用新的ADO连接，然后执行带有DROP子句的SQL语句来删除指定字段，最后清除对象并关闭连接。

关于数据库的简单说明

术语“记录”和“字段”通常用于描述数据库中的数据。一条记录即表中的一行，一个字段即表中的一列。
SQL即结构化查询语言，语法简单但功能强大，可以对数据库实现各种操作。
Connection对象用于在应用程序和所要访问的数据源之间提供传递途径。上文中的示例在使用数据库前，先要建立应用程序和数据库之间的连接。
Recordset对象的ActiveConnection属性将现有的Connection对象赋给Recordset对象或者代表记录集的一个连接字符串，以在数据库中使用。若将一个连接字符串赋值给该属性，则此记录集将为自已创建一个Connection对象，用于引用该记录集所使用的Connection对象。

其CursorLocation属性指定是用服务器端游标引擎还是用客户端游标引擎来操作记录集中的记录。

在使用Open方法打开数据库时，如果其Source设置为从表中读取记录集（表名必须放置在双引号中），则其Options参数设置为adCmdTable；如果设置为从SQL查询语句读取记录集，则其Options参数设置为adCmdText。其CursorType参数在获取记录数据时使用adDynamic或adForwardOnly，且adForwardOnly在获取大型数据集时性能更好；在写入记录时，使用adOpenKeyset。