【VBA研究】VBA如何生成SQL语句最快捷

iamlaosong文

Excel通过VBA访问数据库时需要根据表中的值生成SQL语句，由于字段类型不同，构成的方法也有差别，比如字符型要加单引号，日期型需要转换。如果分别处理，代码量就会比较大。

其实有个非常简单的办法，就是把语句写好，只是需要修改的值用一个字符代替，比如用“?”，最后用循环的方法替换这些问号就行了。例如：

1、查询语句，替换条件中的参数

'原始语句
sqls = "select a.jyrq, a.ywlsh, a.khzh, a.srje, b.dwmc, b.bmmc, b.khmc, a.mark"
sqls = sqls & "  from EMSAPP_ZFB_JYMX a, EMSAPP_ZFB_ZHMX b"
sqls = sqls & "  where a.jyrq between to_date('?', 'yyyy-mm-dd') and  to_date('?', 'yyyy-mm-dd')"
sqls = sqls & "  and a.khzh = b.khzh(+)  and nvl(b.dwmc,'未清分') like '?%'  order by dwmc, bmmc, khzh"
'参数替换
For j = 1 To 3
    sqls = Replace(sqls, "?", Cells(row1, j + 2), 1, 1)
Next j
'Debug.Print sqls

2、插入语句，比较简单，替换插入值

'初始设置
tbName = "EMSAPP_ZFB_JYMX"
KeyNum = 1                  '关键字列号
KeyField = "ywlsh"
AllFields = "(ywlsh,khzh,srje,mark,jyrq)"
FieldNo = 5
'原始语句
sqls = "insert into " & tbName & AllFields & " values ('?','?',?,'?',to_date('?','yyyy-mm-dd'))"

'参数替换
For j = 1 To FieldNo
    sqls = Replace(sqls, "?", Cells(row1, j), 1, 1)
Next j
'Debug.Print sqls

3、更新语句，复杂一点，方法一样

'初始设置
tbName = "EMSAPP_ZFB_JYMX"
KeyNum = 1                  '关键字列号
KeyField = "ywlsh"
AllFields = "(ywlsh,khzh,srje,mark,jyrq)"
FieldNo = 5
'原始语句
sqls = "update " & tbName & " set " & AllFields & " = (select '?','?',?,'?',to_date('?','yyyy-mm-dd') from dual)"
sqls = sqls & " where " & KeyField & " = '?'"

'参数替换
For j = 1 To FieldNo
    sqls = Replace(sqls, "?", Cells(row1, j), 1, 1)
Next j
sqls = Replace(sqls, "?", Cells(row1, KeyNum), 1, 1)
'Debug.Print sqls

对于不能用查询结果赋值更新的数据库，方法也是一样的，只是原始语句改为：

update EMSAPP_ZFB_JYMX set ywlsh='?', khzh='?', srje=?, mark='?', jyrq=to_date('?','yyyy-mm-dd') 

 where ywlsh= '?'