Update Data Using Pass Through Query With Value Coming From Access Function

Question

I create a querydef to update all records on a SQL Server table using pass through query.

The SQL statement is as follows

strSQL = "UPDATE tblEmp SET tblEmp.EmpName1 = Encrypt([tblEmp].[EmpName])"

Encrypt is a function which I created in Access MDB.

Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = sConnectionString
qdf.SQL = strSQL
qdf.ReturnsRecords = False
qdf.Execute

Is there any trick how to update data with the value coming from Access function ? I do not want to update the record one by one.

Thanks.

Answer 1

As soon as you use pass-through query, you should create the text of the query in Access before passing it to the server using Access function. If I understood you correctly, it should be done like this:

strSQL = "UPDATE tblEmp SET tblEmp.EmpName1 = '" & Encrypt([tblEmp].[EmpName]) & "'"

I assume that the column EmpName1 has text data type. You can add WHERE clause if needed.

Answer 2

Pass-through queries run completely on the server and the server doesn't know anything about your VBA function. One way to accomplish your goal of avoiding row-by-row updates on the server would be to

1. pull the EmpName values into a local temporary table,
2. update the EmpName1 values in the local temporary table,
3. push the local temporary table to a remote temporary table on the server, and then
4. use a pass-through query to update the main table from the remote temporary table

Assuming that you have an ODBC linked table named dbo_tblEmp in Access then you could do something like this:

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 = Nothing

Answer 3

As pointed out you can use a PT query, but such a query can't use a function from Access if the value is coming from each row, and you need the function for each row.

However, it not clear why a PT query is required?

The other solution would be to re-write the VBA function as a T-SQL function. So called "scaler" functions work just like VBA, but are server side, and written in T-SQL. Given that T-SQL has looping and just about every feature that VBA has, then once you re-write the code as T-SQL then your PT QUERY becomes this:

UPDATE tblEmp SET tblEmp.EmpName1 = dbo.Encrypt([tblEmp].[EmpName])

In the past, I had a global GST function written in VBA that would return GST rate (based on current date, so:

SELECT id, CompanyName, invoiceNumber, InvoiceDate, GST(InvoiceDate) as GSTRate
FROM tblInvoices

So, I simply re-write the GST() function (VBA) as a T-SQL function. This allowed easy conversion of Access SQL to T-SQL. So views, or even PT quires now could be sent to the server. The so called T-SQL scaler functions work just like when you write a global VBA function. Once written then such functions can be used in ANY SQL expression and any t-sql sql or even t-sql code you write.

So, my suggestion would be to re-write your encrypt VBA function as t-sql. Note that I looked high and dry for a means to NOT have to prefix such scaler functions with dbo.FunctionName (so you do have to use dbo.FunctionName ).

However, for complex quires, or even creating server side views,it is STILL rather nice to take Access SQL and convert it to t-sql - and the GST function or whatever function (such as your encrypt() function becomes dbo.Encrypt().

The beauty here is then you get the same freedom to use that function in ANY sql you write - and that now includes server side SQL.

So, during Access to SQL (back end) conversion projects, to save lots of time, and to save having to re-write SQL, by adopting the user of "scalier" t-sql custom functions, then you can enjoy the same development approach in t-sql as Access VBA developers (that approach being writing custom functions that you are then freely able to use in any SQL you write).

In fact, I had a ton of sql (not mine) that used:

SELECT id, CDBL(StockPrice) as StockP from tblPrices

The previous designer has some values stored as text columns. So, I created a scaler t-sql function like this:

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

After writing the above, then:

SELECT id, dbo.CDBL(StockPrice) as StockP from tblPrices

So, I would suggest you re-write the VBA code in t-sql. T-sql has just about every feature that VBA has, so I never come across VBA code that I could not re-create server side in the t-sql programming language. So t-sql functions work just like VBA ones in that once written you can freely use such custom functions in any other code, and even in sql queries that you write.