VBA Access/Excel：SQL语句实战与数据库操作详解

需积分: 44 94 浏览量更新于2024-07-27 收藏 316KB PDF 举报

本文档深入解析了VBA（Visual Basic for Applications）在Microsoft Access和Excel中的应用，特别是与SQL（Structured Query Language）相关的操作实例。以下是主要内容的详细概述： 1. **SQL语言教程-序**：这部分介绍了SQL的基本概念和在VBA环境中的重要性，它用于管理和操作关系型数据库，如Access中的 Jet Database Engine。 2. **Workbooks对象的Open方法帮助说明**：讲解如何通过VBA打开Access或Excel工作簿，这涉及到连接到数据库操作的基础步骤。 3. **Workbooks对象与Sheets/Worksheets对象的Add方法**：展示了如何动态地在工作簿中添加新的工作表，这对于数据管理和扩展表格非常实用。 4. **Worksheet对象的Delete方法**：介绍如何使用VBA删除工作表，这对于清理数据或重构工作流程时很有用。 5. **利用ADO（ActiveX Data Objects）的Connection对象连接数据库**：详细阐述了如何通过ADO建立与数据库的连接，以便执行SQL语句。 6. **CREATETABLE-创建数据表的语句**：讲解如何使用VBA编写SQL语句来创建新的数据库表结构。 7. **DROPTABLE-删除数据表的语句**：介绍如何通过VBA删除不再需要的数据库表，以保持数据库的整洁。 8. **AlterTable-修改数据表的语句**：演示如何使用SQL更新现有表的结构，例如更改字段、添加或删除列等。 9. **INSERTINTO-向数据库中添加数据**：展示了如何使用VBA执行INSERT语句，将数据插入数据库表中。 10. **UPDATE-修改数据库中已有的数据**：讲解如何通过UPDATE语句更新数据库中的特定记录。 11. **DELETEFROM-删除数据库的数据**：解释了如何使用VBA执行DELETE语句，以移除不需的数据。 12. **CopyFromRecordset方法的使用说明**：重点介绍如何将数据库查询的结果复制到工作表，实现数据的批量导入和处理。 13. **Select-从数据库中检索数据**：这是SQL的核心部分，讲解如何编写SELECT语句来获取所需的数据。 14. **Where-筛选与限制检索的数据**：介绍如何使用WHERE子句来指定检索条件，精确筛选数据。 15. **SQL函数-预处理检索值的命令**：涵盖了如何使用SQL内置函数进行数据计算和转换，提高查询的灵活性。 16. **GroupBy-分类汇总检索的数据**：讲解GROUP BY语句的使用，实现数据的分组统计和分析。 17. **Having-筛选汇总后的数据**：介绍HAVING子句，用于过滤GROUP BY结果，进一步细化数据的筛选条件。以上内容覆盖了VBA在Excel和Access中通过SQL进行数据管理的关键操作，无论你是初学者还是进阶用户，都能在这里找到相应的实例和指导。通过实践这些教程，可以大大提高在处理大量数据时的效率和数据处理能力。

［

SQL

语句教程］

Sub

连接进销存表

()

'*******************************************

作者：

bengdeng

功能：连接同一目录下的进销存表文件

注意：要在工具

引用中引用

microsoft activex date objects x.x

其中

x.x

为版本号，可能会因为你安装的

ofﬁce

的版本不同而不同，本例引用了

2.5

版

'*******************************************

Dim conn As ADODB.Connection

Dim WN As String

Dim sSql As String

WN = "

进销存表

.xls"

Set conn = New ADODB.Connection

conn.Open "Provider=Microsoft.Jet.Oledb.4.0;" & _

"Extended Properties=Excel 8.0;" & _

"Data Source=" & ThisWorkbook.Path & "\" & WN

If conn.State = adStateOpen Then

MsgBox "

连接成功！

conn.Close

End If

Set conn = Nothing

End Sub

上面的与第一段程序对比，需要多设定一个参数——“

Extended Properties

”，这是指定

Excel

文件的

版本，现在已经有的版本为

5.0

、

7.0

、

8.0

等，分别对应的是

版，

版与

2000~2003

版的

XLS

文件格式，

我用的是

2003

版，所以设定为

=Excel 8.0

。

最后需要说明的是，第二段程序是不能像第一段程序中，用

Jet OLEDB:Database Password="

密码

来

打开与连接带有密码的

Excel

文件的，如果需要处理这样的文件，就要用

Workbooks

对象的

Open

方法先打开

这个

Excel

文件后再处理。下面的这个程序就是这样的例子：

Sub

连接带密码进销存表

()

'*******************************************

作者：

bengdeng

功能：连接同一目录下的打开文件密码为“

123

”的进销存表文件

注意：要在工具

引用中引用

microsoft activex date objects x.x

其中

x.x

为版本号，可能会因为你安装的

ofﬁce

的版本不同而不同，本例引用了

2.5

版

'*******************************************

Dim conn As ADODB.Connection

Dim WN As String

Dim sSql As String

WN = "

进销存表

.xls"

Workbooks.Open ThisWorkbook.Path & "\" & WN, Password:="123"

et conn = New ADODB.Connection

conn.Open "Provider=Microsoft.Jet.Oledb.4.0;" & _

"Extended Properties=Excel 8.0;" & _

"Data Source=" & WN

If conn.State = adStateOpen Then

MsgBox "

连接成功！

conn.Close

End If

Workbooks(WN).Close False

Set conn = Nothing

End Sub

这篇文章到这就完了，有点长，有三个程序，大家可以复制到

VBA

编辑器中，运行一下代码，好好的对比

与理解一下，相信，很快你就会踏这第一步了，而后面的世界还很多精彩，看到这的你，应该可以看出一点点

SQL

与

ADO

与

Excel

的联系了，那就让我们一起继续向行吧！！

［

SQL

语句教程］

CREATE TABLE -

创建数据表的语句

数据表是数据库的基本架构，就像

Excel

文件中的工作表一样，在

Excel

中我们可以用

ADD

方法来创建新

的工作表，而

SQL

语言里，

CREATE TABLE

语句就是用来创建数据表的。

在说明

CREATE TABLE

语句的语法之前，我们再来了解一些相关的知识。

Excel

工作表中对应有列与

行，而在数据库中，对应称为

Column

与

Row

，对于这两个单词，应该用过

VBA

的人都不会陌生，这也是

Excel

中

VBA

里列与行的写法。不同的是下面，在数据库中多少列是在创建表时就有设定的，虽然以后还有可

能增加，而且在设定时还要规定整列的数据类型，同时也意味者整列的数据类型都是一样的；这个在

Excel

中

是没有这样的规则，而且

Excel

的最大行与列是由

Excel

本身决定的，这也是数据库与电子表格对数据约束最

大的不同。

而数据库具体有哪些数据类型呢？以

ACCESS

为例，有存贮日期类型的

DATETIME

；有存贮数值类型的

FLOAT

，

SMALLINT

，

INTEGER

等，有字符串型的

CHAR

等等。不同的数据库可能支持不同的数据类型，

因此在使用时应该参考一下数据库在这方面的说明。

了解了上面的信息，下面开始说下

CREATE TABLE

语句的语法：

CREATE TABLE

表格名

(

列名

的数据类型

列名

的数据类型

,... )

注意在列名与数据类型中间有一个空格，在

VBA

中，我们可以利用

ADO

的

Execute

方法，来运行

SQL

语句，下面我们就用

CREATE TABLE

来创建一个进销存表数据库的三个数据表——明细表，进仓表与出仓

表；其中明细表有

列，分别为物品名称（字符串型），结余日期（日期型），结余数量（双精度型），进仓

数量（双精度型），出仓数量（双精度型）；进仓表有

列，分别为进仓日期（日期型），物品名称（字符串

型），进仓数量（双精度型）；出仓表有

列，分别为出仓日期（日期型），物品名称（字符串型），出仓数

量（双精度型）。代码如下：

Sub

创建进销存表数据库

()

'*******************************************

作者：

bengdeng

功能：在程序文件同一目录创建进销表一个进销存表数据库

注意：要在工具

引用中引用

microsoft activex date objects x.x

其中

x.x

为版本号，可能会因为你安装的

ofﬁce

的版本不同而不同，本例引用了

2.5

版

由于要创建一个新的数据库，还要引用

microsoft ado ext.2.x for ddl and security

利用

ADOX

的

Create

方法来创建，其中

2.X

为版本号，本例引用了

2.8

版

'*******************************************

Dim MyCat As ADOX.Catalog

im conn As ADODB.Connection

Dim WN As String

Dim sSql As String

WN = "

进销存表

.mdb"

Set MyCat = New ADOX.Catalog

MyCat.Create "Provider=Microsoft.Jet.Oledb.4.0; _

Data Source=" & ThisWorkbook.Path & "\" & WN

Set conn = New ADODB.Connection

conn.ConnectionString = MyCat.ActiveConnection

conn.Open

If conn.State = adStateOpen Then

sSql = "CREATE TABLE

明细表

(

物品名称

Char(255)," & _

结余日期

Date,

结余数量

Float," & "

进仓数量

Float,

出仓数量

Float)"

conn.Execute sSql

sSql = "CREATE TABLE

进仓表

(

进仓日期

Date,

物品名称

Char(255),

进仓数量

Float)"

conn.Execute sSql

sSql = "CREATE TABLE

出仓表

(

出仓日期

Date,

物品名称

Char(255),

出仓数量

Float)"

conn.Execute sSql

MsgBox "

创建数据库成功！

" & vbCrLf & "

数据库文件名为：

" & WN & vbCrLf & _

保存位置：

" & ThisWorkbook.Path

conn.Close

End If

Set conn = Nothing

End Sub

剩余42页未读，继续阅读

llg3183

粉丝: 0
资源: 2

VBA Access/Excel：SQL语句实战与数据库操作详解

Excel VBA SQL 语句 范例

Excel VBA 调用 SQL的实例（学生档案）

非常好的Excel_VBA_SQL语句

VBA代码编程方法详解实例.rar

Visual C++精彩实例详解

Excel_VBA教程

ACCESS－VBA编程.

access应用详解

excel-vba操作四大方法

ACESS数据库实例

最新资源

Excel VBA SQL 语句范例