分享

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

 L罗乐 2017-07-06

 

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

 

搭建环境

在你的电脑中,除安装了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。

  • Command对象用于执行操作查询,对数据源执行插入、更新、删除等操作。


太长了!明天继续......




转载本文请联系我或者注明出处。 

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 全屏 打印 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多