使用带有来自Access函数的值的直通查询更新数据
我创建了一个querydef来使用传递查询更新SQL Server表上的所有记录。
SQL语句如下所示
strSQL = "UPDATE tblEmp SET tblEmp.EmpName1 = Encrypt(tblEmp.EmpName)“
Encrypt是我在Access MDB中创建的函数。
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = sConnectionString
qdf.SQL = strSQL
qdf.ReturnsRecords = False
qdf.Execute有什么技巧可以用来自Access函数的值来更新数据吗？我不想逐一更新记录。
谢谢。
回答 3
Stack Overflow用户
发布于 2019-08-23 21:09:42
一旦使用了传递查询，就应该在Access中创建查询文本，然后再使用Access函数将其传递给服务器。如果我没理解错的话，应该这样做：
strSQL = "UPDATE tblEmp SET tblEmp.EmpName1 = '" & Encrypt([tblEmp].[EmpName]) & "'"我假设列EmpName1具有text数据类型。如果需要，您可以添加WHERE子句。
Stack Overflow用户
发布于 2019-08-23 23:37:32
传递查询完全在服务器上运行，服务器对您的VBA函数一无所知。要实现避免在服务器上逐行更新的目标，一种方法是
- 将EmpName值拉入本地临时表，
- 更新本地临时表中的EmpName1值，
- 将本地临时表推送到服务器上的远程临时表，然后
- 使用直通查询从远程临时表
更新主表
假设您在Access中有一个名为dbo_tblEmp的ODBC链接表，那么您可以这样做：
Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim odbcConnect As String
odbcConnect = cdb.TableDefs("dbo_tblEmp").Connect
cdb.Execute "SELECT EmpName, '' AS EmpName1 INTO localTemp " & _
"FROM dbo_tblEmp WHERE EmpName IS NOT NULL", dbFailOnError
cdb.Execute "UPDATE localTemp SET EmpName1 = Encrypt(EmpName)", dbFailOnError
DoCmd.TransferDatabase acExport, "ODBC Database", odbcConnect, acTable, "localTemp", "#remoteTemp"
DoCmd.DeleteObject acTable, "localTemp"
Dim qdf As QueryDef
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = odbcConnect
qdf.ReturnsRecords = False
qdf.sql = _
"UPDATE tblEmp SET EmpName1 = rt.EmpName1 " & _
"FROM tblEmp te INNER JOIN #remoteTemp rt ON te.EmpName = rt.EmpName"
qdf.Execute dbFailOnError
qdf.sql = "DROP TABLE #remoteTemp"
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = NothingStack Overflow用户
发布于 2019-08-25 11:30:16
如上所述，您可以使用PT查询，但如果值来自每行，则这样的查询不能使用Access中的函数，并且您需要为每行使用该函数。
但是，还不清楚为什么需要PT查询？
另一种解决方案是将VBA函数重写为T-SQL函数。所谓的“缩放器”函数的工作原理与VBA类似，但它们是服务器端的，并且是用T-SQL编写的。假设T-SQL有循环，并且VBA拥有的几乎所有特性，那么一旦您将代码重写为T-SQL，那么您的PT查询就变成了：
UPDATE tblEmp SET tblEmp.EmpName1 = dbo.Encrypt([tblEmp].[EmpName])在过去，我有一个用VBA编写的全局GST函数，它将返回GST比率(基于当前日期，因此：
SELECT id, CompanyName, invoiceNumber, InvoiceDate, GST(InvoiceDate) as GSTRate
FROM tblInvoices因此，我只是将GST()函数(VBA)重写为T-SQL函数。这样就可以轻松地将Access SQL转换为T-SQL。因此，现在可以将视图，甚至是PT查询发送到服务器。所谓的T-SQL定标器函数的工作方式与编写全局VBA函数时的工作方式相同。一旦编写，这些函数就可以用在任何SQL表达式和任何t-sql sql中，甚至可以用在您编写的t-sql代码中。
因此，我的建议是将加密VBA函数重写为t-sql。请注意，我曾千方百计地寻找一种方法，以避免在这样的缩放函数前面加上dbo.FunctionName (所以您必须使用dbo.FunctionName )。
然而，对于复杂的查询，甚至是创建服务器端视图，使用Access SQL并将其转换为t-sql -以及GST函数或任何函数(如您的encrypt()函数变为dbo.Encrypt() )仍然是相当不错的。
这里的美妙之处在于，您可以在编写的任何SQL中获得相同的自由使用该函数-现在包括服务器端sql。
因此，在访问SQL (后端)转换项目期间，为了节省大量时间，并避免重写SQL，通过采用"scalier“t-sql自定义函数的用户，您可以在t-sql中享受与Access VBA开发人员相同的开发方法(该方法是编写自定义函数，然后您可以自由地在您编写的任何SQL中使用)。
事实上，我有一大堆sql (不是我的)用来：
从tblPrices中选择id，CDBL(StockPrice)作为StockP
以前的设计器将一些值存储为文本列。因此，我创建了一个伸缩器t-sql函数，如下所示：
ALTER FUNCTION [dbo].[CDBL]
(
-- Add the parameters for the function here
@P1 sql_variant
)
RETURNS float
AS
BEGIN
-- Declare the return variable here
DECLARE @Result float
-- Add the T-SQL statements to compute the return value here
SET @Result = CAST(@P1 as float)
-- Return the result of the function
RETURN @Result
END在写完上面的内容之后，然后：
SELECT id, dbo.CDBL(StockPrice) as StockP from tblPrices因此，我建议您用t-sql重写VBA代码。T-sql几乎具有VBA所具有的所有特性，因此我从未遇到过不能用t-sql编程语言重新创建服务器端的VBA代码。因此，t-sql函数的工作方式与VBA函数类似，因为一旦编写完成，您就可以在任何其他代码中自由使用这种自定义函数，甚至可以在编写的sql查询中使用这些自定义函数。
https://stackoverflow.com/questions/57622860
复制相似问题
腾讯云开发者
