以前在软件公司中工作,从来没有这样诡异地使用过ACCESS跟SQLSERVER.但现在发现在企业中,类似的看上去让人不爽的应用还是很多的.没有思路,于是到CSDN上询问.
CSDN上面ACCESS版网友 wangtiecheng给出下面的代码:
'
更改传递查询的SQL语句
Public Sub ChangeSQL(strPassThroughQueryName As String , strPassthroughSQL As String )
' 1、初始化
Dim cat As New ADOX.Catalog
Dim Pro As ADOX.Procedure
Dim Cmm As New ADODB.Command
' 2、判断输入参数的有效性
If strPassthroughSQL = "" Or Len (strPassthroughSQL) = 0 Then Exit Sub
Set cat.ActiveConnection = CurrentProject.Connection ' 使用本地连接 CurrentProject.Connection
Set Pro = cat.Procedures(strPassThroughQueryName)
Set Cmm = Pro.Command
Cmm.CommandText = strPassthroughSQL
' Cmm.CommandTimeout = 300
Set Pro.Command = Cmm
' 3、更改 PassThrough 串成功
Set cat = Nothing
Set Pro = Nothing
Set Cmm = Nothing
End Sub
在ACCESS中创建一个新的模块(模块名不能与sub名相同),拷入代码.
Public Sub ChangeSQL(strPassThroughQueryName As String , strPassthroughSQL As String )
' 1、初始化
Dim cat As New ADOX.Catalog
Dim Pro As ADOX.Procedure
Dim Cmm As New ADODB.Command
' 2、判断输入参数的有效性
If strPassthroughSQL = "" Or Len (strPassthroughSQL) = 0 Then Exit Sub
Set cat.ActiveConnection = CurrentProject.Connection ' 使用本地连接 CurrentProject.Connection
Set Pro = cat.Procedures(strPassThroughQueryName)
Set Cmm = Pro.Command
Cmm.CommandText = strPassthroughSQL
' Cmm.CommandTimeout = 300
Set Pro.Command = Cmm
' 3、更改 PassThrough 串成功
Set cat = Nothing
Set Pro = Nothing
Set Cmm = Nothing
End Sub
VBA编辑器窗口,工具-引用,Microsoft ADO Ext. 2.8 for DLL and Security打勾.
然后在执行存储过程前的代码中调用 ChangeSQL子函数即可修改传递查询的SQL.