VBA+ADO+SQL系列学习教程（1）

楼主 wise	VBA结合SQL来操作Access、SQL Server在工作中经常用到，本人在近一个多月里面，常常用到VBA+SQL来进行编程。以下将慢慢地讲述一系列操作。 1、前提条件：在当前路径下创建一个名为“数据库”的Access文件。 Q：如何使用VBA及SQL在Access文件中创建一个名称为“人员”，里面的字段为姓名、年龄的表。 A：ALT+F11→插入模块，在模块中插入以下代码： Sub CreateTable() '定义变量 Dim Adoconn As Object Dim Strconn As String Dim StrSQL As String StartTime = Timer '后绑定数据库 Set Adoconn = CreateObject("ADODB.Connection") '设置连接字符串 Strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" '创建表的SQL语句 StrSQL = " CREATE TABLE 人员 (姓名 Char, 年龄 Integer)" '打开数据库连接 Adoconn.Open Strconn '执行SQL语句 Adoconn.Execute StrSQL '关闭数据库连接 Adoconn.Close MsgBox Timer - StartTime End Sub 在本例中，为了适应各电脑中，采用了后绑定调用数据库： Set Adoconn = CreateObject("ADODB.Connection") 而因为需要知道该数据库的路径，必须要通过Provider告知ADO使用哪个OLE DB提供者。本例中因为使用了Access 2010 ，故其为： Provider=Microsoft.ACE.OLEDB.12.0; 而该文件的路径及数据库名称则通过设置连接字符串中其他字符实现。值得注意创建数据库的SQL格式为： Create Table 名称 (字段1 字段1字段类型,字段2 字段2字段类型) 而执行SQL语句的话，必须首先打开数据库文件，导致产生了该操作： Adoconn.Open Strconn 注意里面的open操作打开数据库后，则需要执行数据库，此时的执行SQL代码是： Adoconn.Execute StrSQL 注意Execute操作，其为执行该SQL代码。执行完毕后，需要关闭数据库，此时代码为： Adoconn.Close 从上面代码分析可以知道VBA+ADO+SQL操作步骤是： 1、后绑定数据库 2、确定数据库的来源于哪个OLE DB，并且其路径及数据库 3、编写SQL代码 4、打开数据库 5、执行SQL语句 6、关闭数据库
2楼 wise	在上一个例子中，是创建数据库的，下面第二个例子 Q：如何使用VBA+ADO+SQL删除当前路径下“数据库.accdb"中的”人员“表？ A:代码如下： Sub DeleteTable() Dim Adoconn As Object Dim Strconn As String Dim StrSQL As String Dim StarTime As Variant StartTime = Timer '后绑定数据库 Set Adoconn = CreateObject("ADODB.Connection") '设置连接字符串 Strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" '删除表的SQL语句 StrSQL = "DROP Table 人员" '打开数据库连接 Adoconn.Open Strconn '执行SQL语句 Adoconn.Execute StrSQL '关闭数据库连接 Adoconn.Close MsgBox Timer - StarTime End Sub 注意，删除数据库的SQL代码格式是： Drop Table 表名称表示删除指定表。
3楼 wise	Q：如何使用VBA+ADO+SQL来创建新的字段？ A：ALT+F11→插入模块，在模块输入代码： Sub InsertNewColumn() Dim Adoconn As Object Dim Strconn As String Dim StrSQL As String Dim StarTime As Variant StarTime = Timer '后绑定数据库 Set Adoconn = CreateObject("ADODB.Connection") '设置连接字符串 Strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" '添加新字段的SQL语句 StrSQL = "ALTER TABLE 学生 ADD COLUMN 专业 Char" '打开数据库连接 Adoconn.Open Strconn '执行创建表的SQL语句 Adoconn.Execute " Create Table 学生 (姓名 char,年龄 integer)" '执行SQL语句 Adoconn.Execute StrSQL '关闭数据库连接 Adoconn.Close MsgBox Timer - StarTime End Sub 注意SQL语句： ALTER TABLE 学生 ADD COLUMN 专业 Char 在表中插入新字段的格式为： ALTER TABLE 表名 ADD COLUMN 字段名字段类型但在插入该字段之前，必须确保表名已经创建好的。在本程序中，通过该代码确保表已经创建完毕： Adoconn.Execute " Create Table 学生 (姓名 char,年龄 integer)"
4楼 wise	Q：如何使用VBA+ADO+SQL删除数据库里面表的字段？ A：ALT+F11→插入模块，在模块中输入以下代码： Sub DeleteColumn() Dim Adoconn As Object Dim StrConn As String Dim StrSQL As String Dim StarTime As Variant StarTime = Timer '后绑定数据库 Set Adoconn = CreateObject("ADODB.Connection") '设置连接字符串 StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" '删除字段的SQL语句 StrSQL = "ALTER TABLE 学生 DROP COLUMN 专业" '打开数据库连接 Adoconn.Open StrConn '执行SQL语句 Adoconn.Execute StrSQL '关闭数据库连接 Adoconn.Close MsgBox Timer - StarTime End Sub 删除字段的SQL代码如下： ALTER TABLE 表名 DROP COLUMN 字段名
5楼 wise	Q：如何利用VBA+ADO+SQL修改数据库里面的字段类型呢？例如修改当前路径下的”数据库.accdb"中“学生”表中的“年龄”字段为char(5),如何操作呢？ A：ALT+F11→插入模块，在模块中输入以下代码： Sub ChangeType() Dim Adoconn As Object Dim Strconn As String Dim StrSQL As String Dim StarTime As Variant StarTime = Timer '后绑定数据库 Set Adoconn = CreateObject("ADODB.Connection") '设置连接字符串 Strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" '修改字段类型的SQL语句 StrSQL = " ALTER TABLE 学生 ALTER COLUMN 年龄 Char(5)" '打开数据库连接 Adoconn.Open Strconn '执行SQL语句 Adoconn.Execute StrSQL '关闭数据库连接 Adoconn.Close MsgBox Timer - StarTime End Sub 在本例中，利用ALTER TABLE语句修改字段类型的格式为： ALTER TABLE 表名 ALTER COLUMN 字段名修改后的字段类型
6楼 wise	Q:如何利用VBA+ADO+SQL把数据库某表复制到另外一个数据库中？ A：ALT+F11→插入模块，在模块中输入代码： Sub InToNew() Dim AdoConn As Object Dim StrConn As String Dim StrSQL As String Dim StarTime As Variant StarTime = Timer '后绑定数据库 Set AdoConn = CreateObject("ADODB.Connection") '设置连接字符串 StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" '复制基础数据到另外一个数据库 StrSQL = " SELECT * INTO [" & ThisWorkbook.Path & Application.PathSeparator & "新数据库.accdb].新基础数据 FROM 基础数据" '打开数据库连接 AdoConn.Open StrConn '执行SQL语句 AdoConn.Execute StrSQL '关闭数据库连接 AdoConn.Close MsgBox Timer - StarTime End Sub 在此处值得关注的是： SELECT * INTO [database].Newtable FROM SOURCE 其中参数database代表目标数据库，而newtable 为目标数据表，Source代表源数据表在本例中具体体现为： StrSQL = " SELECT * INTO [" & ThisWorkbook.Path & Application.PathSeparator & "新数据库.accdb].新基础数据 FROM 基础数据" 本例子中的：ThisWorkbook.Path & Application.PathSeparator & "新数据库.accdb 代表本目录的“新数据库.accdb”文件。值得注意的是路径两边的[]不能省略
7楼 wise	Q：如何使用VBA把表的数据添加到Access表中？ A：ALT+F11→插入模块，输入以下代码： Sub INSERT() Dim AdoConn As Object Dim StrConn As String Dim StrSQL As String Dim i As Long On Error Resume Next Set AdoConn = CreateObject("ADODB.Connection") StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & ThisWorkbook.Path & Application.PathSeparator & "数据库.accdb;" StrSQL = " Create table 流量 (地市 char, 普通用户 double,零M double,小于10M double )" '打开数据库连接 AdoConn.Open StrConn '执行SQL语句 AdoConn.Execute StrSQL '关闭数据库连接 AdoConn.Close '打开数据库连接 AdoConn.Open StrConn For i = 2 To Range("A" & Cells.Rows.Count).End(xlUp).Row StrSQL = "Insert into 流量 ( 地市,普通用户,零M,[小于10M]) values ( '" & Cells(i, 1) & "'," & Cells(i, 2) & "," & Cells(i, 3) & "," & Cells(i, 4) & ")" '执行SQL语句 AdoConn.Execute StrSQL Next i '关闭数据库连接 AdoConn.Close End Sub 首先创建一个“流量” 表，然后使用Insert into往表中添加数据。 Insert into 语法为： Insert into 表 (字段1，字段2，字段3) values (值1,值2,值3) 注意，如果值是字符类型，则在字符两边添加单引号(')；如果是日期形式，则两边添加"#";数值类型则不需添加。 tu.png
8楼 wise	待定占位4
9楼 wise	待定占位5
10楼 wise	待定占位6
11楼 DJ_Soo	喔,77又开始玩VBA了
12楼 wise	工作干活用到了，不是玩啊
13楼 DJ_Soo	工作用到也可以是玩呀,嘿嘿,要不写代码就没意思了
14楼 wise	很讨厌写代码啊，白天写，晚上头痛，还是以前写PPT比较好
15楼 DJ_Soo	囧哦,我见到ppt头晕
16楼朽木	这个在数据挖掘中很有用。
17楼 qinhuan66	好好学习天天向上
18楼 335081548	谢谢分享