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。
Command对象用于执行操作查询,对数据源执行插入、更新、删除等操作。
太长了!明天继续......
转载本文请联系我或者注明出处。
联系客服