T-SQL via pass-through

[CedarTree]

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)...

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:

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.

 

[theDBguy]

Hi. Just a guess since I am no expert at this, but maybe for something like that, you may have to use ADO command text.

 

[CedarTree]

That was the trick! So, usually, when I work with sql-server sources in Access, I use a pass-through query (using DAO recordsets) to bring in the data, do calculations, and use P-T queries to update data back on the server. I always thought of ADODB as an old approach but I never toyed with it. Is using ADO a fast way to manipulate data right on the server?

 

[CedarTree]

So to be clearer, I created an ADODB connection, and executed the procedure that way. However, now I have to learn stored procedure programming in more detail, whereas I'm very comfy in VBA. But I went down the stored procedure path b/c if I need to do calcs on 500,000 records, I figured that would take a LONG time connecting and manipulating from Access. But maybe ADO is fast?

 

[pbaldy]

I'm curious, why the loop (cursor)? Wouldn't this work?

UPDATE MyTable SET Test = 1 WHERE PID < 1800

What you've got there is RBAR.

 

[CedarTree]

Yeah it was just for testing purposes. I totally agree with what you have of course.

 

[CedarTree]

Is anyone here an ADODB expert? I can open a recordset via ADO and just wondering how I can edit it.

 

[Galaxiom]

Is anyone here an ADODB expert? I can open a recordset via ADO and just wondering how I can edit it.

Same commands as any ADO recordset. You don't need the Edit method like you do with DAO. Just change the value in the field and do the Update method.

As pointed out on your other thread, doing this in a loop isn't necessary. It should be done with a query if done at all.

BTW The real value with ADO recordsets comes with their ability to be disconnected. They can be bound to Access forms too.

 

[sonic8]

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?

Get rid of the GO and it should run as a Pass-Through-Query. - ADO should not understand the GO as well, but maybe it still continues with the batch.

...while we are at it: Wrap the main block of your batch in BEGIN/END.

 

[CedarTree]

Great - thanks!