T-SQL via pass-through (1 Viewer)

CedarTree

Registered User.
Local time
Today, 03:35
Joined
Mar 2, 2018
Messages
404
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)...

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:

theDBguy

I’m here to help
Staff member
Local time
Today, 00:35
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 03:35
Joined
Mar 2, 2018
Messages
404
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

Registered User.
Local time
Today, 03:35
Joined
Mar 2, 2018
Messages
404
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

Wino Moderator
Staff member
Local time
Today, 00:35
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 03:35
Joined
Mar 2, 2018
Messages
404
Yeah it was just for testing purposes. I totally agree with what you have of course.
 

CedarTree

Registered User.
Local time
Today, 03:35
Joined
Mar 2, 2018
Messages
404
Is anyone here an ADODB expert? I can open a recordset via ADO and just wondering how I can edit it.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:35
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 08:35
Joined
Oct 27, 2015
Messages
998
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.
 

Users who are viewing this thread

Top Bottom