SQL代码处理加工器V3.5

dcw0402

在和数据库打交道时,我们常常需要据软件自带的工具(如SQL SERVER2000的企业管理器 或ACCESS的查询等等)生成的代码 转化成在VBA中能用的代码,举例如下:
用友T3的数据库为例,在SQL SERVER的企业管理器中表查询里很容易的生成这样的代码
SELECT SO_SODetails.cSOCode AS 销售订单号, SO_SODetails.dPreDate AS 预发日期,
Customer.cCusName AS 客户名称, Person.cPersonName AS 业务员,
Customer.cTrade AS 所属行业, InventoryClass.cInvCName AS 产品类别,
Inventory.cInvName AS 存货名称, Inventory.cInvStd AS 规格型号,
SO_SODetails.iQuantity AS 数量, SO_SODetails.iNatUnitPrice AS 单价无税,
SO_SODetails.iNatMoney AS 金额无税, SO_SODetails.iNatTax AS 税额,
SO_SODetails.iNatSum AS 价税合计, SO_SODetails.iFHQuantity AS 发货数量,
SO_SODetails.iFHMoney AS 发货金额
FROM SO_SODetails INNER JOIN
Inventory ON SO_SODetails.cInvCode = Inventory.cInvCode INNER JOIN
InventoryClass ON Inventory.cInvCCode = InventoryClass.cInvCCode INNER JOIN
SO_SOMain ON SO_SODetails.cSOCode = SO_SOMain.cSOCode INNER JOIN
Customer ON SO_SOMain.cCusCode = Customer.cCusCode INNER JOIN
Person ON SO_SOMain.cPersonCode = Person.cPersonCode
WHERE (MONTH(SO_SODetails.dPreDate) >= 1) AND (MONTH(SO_SODetails.dPreDate)
<= 1) AND (Person.cPersonName = '白小白') AND (Customer.cCusName = '大连船舶重工集团有限公司')

上面的这些代码如何能在VBA中引用呢?通常我是将它们转化成如下的代码
ssql = ""
ssql = ssql & "SELECT SO_SODetails.cSOCode AS 销售订单号, SO_SODetails.dPreDate AS 预发日期," & chr(13)
ssql = ssql & "Customer.cCusName AS 客户名称, Person.cPersonName AS 业务员," & chr(13)
ssql = ssql & "Customer.cTrade AS 所属行业, InventoryClass.cInvCName AS 产品类别," & chr(13)
ssql = ssql & "Inventory.cInvName AS 存货名称, Inventory.cInvStd AS 规格型号," & chr(13)
ssql = ssql & "SO_SODetails.iQuantity AS 数量, SO_SODetails.iNatUnitPrice AS 单价无税," & chr(13)
ssql = ssql & "SO_SODetails.iNatMoney AS 金额无税, SO_SODetails.iNatTax AS 税额," & chr(13)
ssql = ssql & "SO_SODetails.iNatSum AS 价税合计, SO_SODetails.iFHQuantity AS 发货数量," & chr(13)
ssql = ssql & "SO_SODetails.iFHMoney AS 发货金额" & chr(13)
ssql = ssql & "FROM SO_SODetails INNER JOIN" & chr(13)
ssql = ssql & "Inventory ON SO_SODetails.cInvCode = Inventory.cInvCode INNER JOIN" & chr(13)
ssql = ssql & "InventoryClass ON Inventory.cInvCCode = InventoryClass.cInvCCode INNER JOIN" & chr(13)
ssql = ssql & "SO_SOMain ON SO_SODetails.cSOCode = SO_SOMain.cSOCode INNER JOIN" & chr(13)
ssql = ssql & "Customer ON SO_SOMain.cCusCode = Customer.cCusCode INNER JOIN" & chr(13)
ssql = ssql & "Person ON SO_SOMain.cPersonCode = Person.cPersonCode" & chr(13)
ssql = ssql & "WHERE (MONTH(SO_SODetails.dPreDate) >= 1) AND (MONTH(SO_SODetails.dPreDate)" & chr(13)
ssql = ssql & "<= 1) AND (Person.cPersonName = '白小白') AND (Customer.cCusName = '大连船舶重工集团有限公司')" & chr(13)

恩,你发现了,仅挨着上面的这段代码就可以很容易的贴进VBE中稍做变量修改进行引用,如将 白小白 改为 →" & target.offset(0,-2) & "← .于是乎我就做了这样一个工具,方便加工代码,她好我也好..
----------光滑的分割线--------------
SQL代码加工合成工具V1.0.用法:打开文件后点击按钮,CTRL+V将代码直接贴进光标处的文本框→点击确定按钮→提示成功读入内存,同时打记事本→在记事本中按CTRL+V粘贴加工后的代码 加工后的代码可直接贴进VBE,请测试.

[ 本帖最后由 dcw0402 于 2010-3-21 22:11 编辑 ]

安贤

收藏一下,正在学SQL呢,谢谢楼主,

bibisin

谢谢LZ分享

dcw0402

一楼附件加入更新  将代码转化回来,我的实践中认为这是很有必要的,即将
ssql = ""
ssql = ssql & "SELECT * from a" & chr(13)
ssql = ssql & "wher a.field1 like '6001%'"
转化成
select * from a
where a.field like '6001%'

dcw0402

VB版出品  主要是针对VBS的学习
欢迎在一楼下载试用

[ 本帖最后由 dcw0402 于 2010-3-23 17:54 编辑 ]

aidixiao

好工具 ，谢谢分享。

丁壬易龙

多谢楼主分享!!

zez

多谢楼主分享!!

飞云楼主

不错收藏学习。

linmeici

谢谢楼主分享,很好