Sorry to post this (again) but not sure anyone is seeing it in the sql-server forum. I can run basic queries (updates, inserts, etc.) using a pass-through. But I want to run something more like this (silly test that sets a flag for certain records)...
When I run this in SSMS, it runs fine. When I run it as a pass-through from Access, nothing changes. So is that Access cannot pass-through something like this? Or my pass through coding is wrong? Here's my P-T coding, which works for basic queries at least:
P.S. If I take the sql statement from Access and copy/paste into SSMS, it runs perfectly. So the sql syntax seems to be fine.
Code:
USE TestDB;
GO
DECLARE @PID as INT;
DECLARE @Row as CURSOR;
SET @Row = CURSOR FOR
SELECT PID FROM MyTable
OPEN @Row;
FETCH NEXT FROM @Row INTO @PID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PID < 1800
UPDATE MyTable SET Test = 1 WHERE PID = @PID;
FETCH NEXT FROM @Row INTO @PID;
END
CLOSE @Row;
DEALLOCATE @Row;
When I run this in SSMS, it runs fine. When I run it as a pass-through from Access, nothing changes. So is that Access cannot pass-through something like this? Or my pass through coding is wrong? Here's my P-T coding, which works for basic queries at least:
Code:
Sub subExecuteSQL(pSQL As String)
On Error Resume Next
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = gsConnection
qdf.sql = pSQL
qdf.ReturnsRecords = False
qdf.Execute
qdf.Close
Set qdf = Nothing
End Sub
P.S. If I take the sql statement from Access and copy/paste into SSMS, it runs perfectly. So the sql syntax seems to be fine.
Last edited: