UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Qdf .SQL Property, Access 2016    
 
   
Timtropolis63
post Jun 13 2019, 09:29 AM
Post#1



Posts: 3
Joined: 13-June 19



Greetings all.

I have bit of code where I am creating a qdf based on an existing Passthru query in order to make it dynamic for what I need.
I'm adjusting the .SQL property with SQL statement defined as a variable.
The problem I have is the execution which is not accepting the new SQL code, and is processing the query as it exists.

My questions are:
1. Do I need to save the qdf/query in order to have it accept the updated SQL?
2. Do I need to include any other properties in the qdf.execute?

Code:
CODE
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim ssql As String
    
    DoCmd.SetWarnings False
    Set dbs = CurrentDb
    Set qdf = CurrentDb.QueryDefs("passthru_PTQ")
    Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset, dbSeeChanges)
    
    If rst.RecordCount > 0 Then
        ssql = "DELETE " & strTableName & ".* FROM " & strTableName & " "
        qdf.SQL = ssql
        
        qdf.Execute

    End If

    qdf.Close


TIA,
TIM
This post has been edited by Timtropolis63: Jun 13 2019, 09:44 AM
Go to the top of the page
 
jleach
post Jun 13 2019, 09:59 AM
Post#2


UtterAccess Editor
Posts: 10,019
Joined: 7-December 09
From: St Augustine, FL


Hi, I believe you do need to save it, yes. IIRC, there's a qdf.Save method that will take care of it.

The only other property you should need to be careful of is the .ReturnsRecords property. I usually save two template passthrough queries: one which returns records, and one that doesn't. That way I don't have to worry about it, only have to grab the right one depending on whether I want a return or not.

hth

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jun 13 2019, 10:29 AM
Post#3


UA Admin
Posts: 35,304
Joined: 20-June 02
From: Newcastle, WA


I'm not 100% sure it has to be saved, but I will have to go look for an example.

I also wonder if it might be more efficient to Truncate these tables, since you are not applying a Where clause.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jun 13 2019, 10:35 AM
Post#4


UA Admin
Posts: 35,304
Joined: 20-June 02
From: Newcastle, WA


Well, okay then.

I forgot that the changed SQL was being implicitly saved in my routine when I closed the QueryDef object and called it from another procedure.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Jun 13 2019, 10:42 AM
Post#5



Posts: 3,163
Joined: 27-February 09



Why do you need to modify the SQL of the query to just delete some records?

Why not just build the SQL statement and then use

DBEngine(0)(0).Execute "DELETE FROM MyTable WHERE...", dbFailOnError

No mucking with modifying querydef strings or any of that.
Go to the top of the page
 
GroverParkGeorge
post Jun 13 2019, 10:48 AM
Post#6


UA Admin
Posts: 35,304
Joined: 20-June 02
From: Newcastle, WA


Pasthrus do execute on the server and can often be more efficient, especially if the number of records is larger. But with no Where clause, I'd still use Truncate in a passthru.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Timtropolis63
post Jun 13 2019, 11:14 AM
Post#7



Posts: 3
Joined: 13-June 19



Thanks everyone for the help.
I'll take these ideas and give it whirl and followup.

Only other question is that when I attempt to do qdf.save, it doesn't give me that option in the context menu and when I try to add it, I get Method not found error. Any other ideas with respect to that?

Thanks again.
This post has been edited by Timtropolis63: Jun 13 2019, 11:15 AM
Go to the top of the page
 
ADezii
post Jun 13 2019, 11:17 AM
Post#8



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Do I need to save the qdf/query in order to have it accept the updated SQL?

No, in addition:
  1. Make sure the ReturnRecords Property is set to No.
  2. You may have to do an explicit
    CODE
    rst.MoveLast:rst.MoveFirst
  3. in order to obtain an accurate Record Count.
  4. Where and how is strTableName Declared?
  5. As an example, then following Code will dynamically modify the SQL of a Pass-Through Query (_qryPassThru) which will DELETE all Records in a Table (Table1) while keeping the other Table Properties in tact.
    CODE
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim ssql As String

    Set dbs = CurrentDb

    '************* USER DEFINED *************
    Const conPASS_THRU = "_qryPassThru"
    Const con_TABLE_NAME = "Table1"
    '****************************************

    Set qdf = dbs.QueryDefs(conPASS_THRU)

    ssql = "DELETE FROM " & con_TABLE_NAME
        
    With qdf
      .SQL = ssql
      .Execute
    End With

    Set dbs = Nothing
    Set qdf = Nothing
  6. Hope this helps.

This post has been edited by ADezii: Jun 13 2019, 11:19 AM
Go to the top of the page
 
projecttoday
post Jun 13 2019, 05:56 PM
Post#9


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


You need to create a pass-through query, replace the code in the pass-through query with the .QDF property, then execute the query with Currentdb.Execute.

--------------------
Robert Crouser
Go to the top of the page
 
Timtropolis63
post Jun 14 2019, 10:05 AM
Post#10



Posts: 3
Joined: 13-June 19



Thank you Adezzi!! That worked!
Go to the top of the page
 
ADezii
post Jun 14 2019, 10:43 AM
Post#11



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif Good Luck with your Project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 04:16 AM