在VBA使用ADO可以高效的查询数据,但是写SQL查询语句是个有些令人头痛的事情,其实在ADO中用到的SQL语句的语法并不是很复杂,很多时候是由于即要思考SQL的语法,又要用VBA的字符串连接命令,最终搞得一团糟。
其实,这个创建SQL语句的工作可以交给VBA来完成,当然其前提是——你已经想清楚了要如何构建你的SQL。
1. 定义参数
为了和VBA中数组元素编号一致,称为“参数0”到“参数4”,共5个,分别对应SQL语句的不同部分。代码中给出了“聚合SQL”和“非聚合带排序”两个例子。
2. 使用变量
参数数组也可以用变量,比如需要查询当前年份的记录,参数3初始赋值为”[年]=””,然后使用下面代码进行替换
aPara(2) = VBA.Replace(aPara(2), REPLACE_CHAR, Year(Date))
3. SQL语句模板
将SQL模板定义为”SELECT FROM WHERE “,共有5个问号,对应5个参数。
4. 可选SQL关键字
由于GROUP BY和ORDER BY是可选部分,因此如果不需要相关部分的话,请将相应的参数置空,参见“非聚合带排序”。
5. 动态创建SQL字符串
准备工作已经完成,循环替换就可以创建SQL字符串,Replace方法的第5个参数需要指定为1,以实现逐个替换。
sSQL = VBA.Replace(sSQL, REPLACE_CHAR, aPara(i), 1, 1, vbTextCompare)
6. 完整代码
Sub CreateSQL() Dim sSQL, aPara Const REPLACE_CHAR = "" '============================================================== ' aPara = Array("Fields", _ 参数0: 字段 ' "Table", _ 参数1: 数据表 ' "Where", _ 参数2: 条件 ' "Group By", _ 参数3: 分组 ' "Order By") 参数4: 排序 '-------------------------------------------------------------- ' 聚合SQL ' aPara = Array("[班级],[科目],SUM([成绩])", _ ' "[成绩表$A:K]", _ ' "[年]='2017'", _ ' "[班级]", _ ' "[班级],[科目]") '-------------------------------------------------------------- ' 非聚合带排序 aPara = Array("[班级],[科目],[姓名],[成绩]", _ "[成绩表$A:K]", _ "[年]=''", _ "", _ "[班级],[科目]") '-------------------------------------------------------------- ' 使用变量替换参数 aPara(2) = VBA.Replace(aPara(2), REPLACE_CHAR, Year(Date)) '============================================================== sSQL = "SELECT FROM WHERE " For i = 0 To 4 If i > 2 And Len(aPara(i)) > 0 Then aPara(i) = IIf(i = 3, "GROUP BY ", "ORDER BY ") & aPara(i) End If sSQL = VBA.Replace(sSQL, REPLACE_CHAR, aPara(i), 1, 1, vbTextCompare) 'Debug.Print sSQL Next Debug.Print sSQL End Sub
7. SQL查询字符串
' 聚合SQL SELECT [班级],[科目],SUM([成绩]) FROM [成绩表$A:K] WHERE [年]='2017' GROUP BY [班级] ORDER BY [班级],[科目] ' 非聚合带排序 SELECT [班级],[科目],[姓名],[成绩] FROM [成绩表$A:K] WHERE [年]='2018' ORDER BY [班级],[科目]
每次只需要稍加修改参数数组就可以创建出SQL语句,感觉不错吧!大家也可以把这个Sub过程改造成Function函数,更便于在代码中调用。