none
Dynamic Pass-Through Query with follow up Refresh of Table/Form RRS feed

  • Question

  • Hi MSDN

    I previously have a function vba code, executing  a query for an Access FE / Access BE.  All queries were native queries, not Pass-Through Queries

    [Code]

    Private Sub cmdRunDashboardDateQuery_Click()

        RefreshDashboardList
    End Sub

    Public Sub RefreshDashboardList()

        Application.Echo False
        Call DashboardDateQuery("Dashboard View Start UPDATE_FIRST_LAST JOIN_Crosstab", "Dashboard List")
        Forms![Dashboard List].txtDate.Requery
        Forms![Dashboard List].txtTimeDescription.SetFocus
        SubcmdJobOrderRight
        SubcmdJobOrderLeft
        
        Application.Echo True
        Forms![Dashboard List].Repaint
        
    End Sub

    Public Sub DashboardDateQuery(qryDefinition As String, frmDefinition As String)
    Dim db  As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter

    Call PreparePassThroughQueries(frmDefinition)

    Set db = CurrentDb
    Set qdf = db.QueryDefs(qryDefinition)
        
        Forms(frmDefinition).Requery

    'Cleanup
    Set qdf = Nothing
    Set db = Nothing
    End Sub

    [/code]

    Now that we have created Pass through queries, we have dynamically created the pass through query via

    [code]

    Public Sub runPreparePassThroughQueries(frmName)
        Call PreparePassThroughQueries(frmName)
    End Sub



    Public Sub PreparePassThroughQueries(frmName)
    Dim SPTQueryName As String
    Dim SQLString As String
    Dim ConnectString As String
    Dim strDate As Date

    On Error GoTo ErrorHandler

    'frmName = "Dashboard List"

    strDate = Forms(frmName).Controls.Item("txtDate")

    SPTQueryName = "Dashboard View Start UPDATE"

    ConnectString = "ODBC;Description=SQL Management Server;DRIVER=SQL Server;SERVER=ABAPLUMBINGPC\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=ABA PLUMBING Call Tracker V7"

    SQLString = "SELECT CONVERT(varchar, [Due Date],103) AS [Due Date], [Job Order].[Job Order], Employees.[Update Name], Category.Category, "
    SQLString = SQLString & "Calls.ID , Calls.[Job Number], Calls.[Call Back], Calls.[Call Before], Calls.[Status], "
    SQLString = SQLString & "Customers.[City], Customers.[Full Name],Customers.[Business Phone],Customers.[Home Phone],"
    SQLString = SQLString & "Customers.[Mobile Phone] , "
    SQLString = SQLString & "([Calls].[Job Number] + Char(13) + Char(10) + "
    SQLString = SQLString & "IIf([Calls].[Call Back] = 'Yes','Call back' + Char(13) + Char(10),'') + "
    SQLString = SQLString & "IIf([Customers].[Full Name] IS NULL, '', [Customers].[Full Name] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Customers].[Business Phone] IS NULL, '', [Customers].[Business Phone] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Customers].[Home Phone] IS NULL, '', [Customers].[Home Phone] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Customers].[Mobile Phone] IS NULL, '', [Customers].[Mobile Phone] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Calls].[Call Before] = 'Yes','Call: ' + [Calls].[Call Before] + Char(13) + Char(10),'') + "
    SQLString = SQLString & "IIf([Customers].[City] IS NULL, '', [Customers].[City] + Char(13) + Char(10)) + "
    SQLString = SQLString & "[Category].[Category] + Char(13) + Char(10) + "
    SQLString = SQLString & "[Calls].[Status]) AS Dashboard "
    SQLString = SQLString & "FROM Customers RIGHT JOIN (Category  RIGHT JOIN ([Job Time]  RIGHT JOIN (Employees RIGHT JOIN ([Job Order]  JOIN Calls "
    SQLString = SQLString & "ON [Job Order].ID = Calls.[Job Order]) ON Employees.ID = Calls.[Assigned To]) ON [Job Time].ID = Calls.[Job Time]) "
    SQLString = SQLString & "ON Category.ID = Calls.Category) ON Customers.ID = Calls.Caller "
    SQLString = SQLString & "WHERE (((Calls.[Due Date])='" & Format(strDate, "YYYY-MM-DD") & "')) "
    SQLString = SQLString & "ORDER BY Calls.[Due Date], [Job Order].ID;"



        Call CreateSPT(SPTQueryName, SQLString, ConnectString)
        
        
        
    SQLString = "SELECT Employees.* FROM Employees UNION SELECT Management.* FROM Management;"
    SPTQueryName = "Staff Extended"
        
            Call CreateSPT(SPTQueryName, SQLString, ConnectString)
            
            'MsgBox "Finished"
            
    ErrorHandler:
        'strDate = Date
                
        
        
        
        
    End Sub




    Public Sub CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String)
    '-----------------------------------------------
    ' SUB: CreateSPT()

    PURPOSE:
    ' UtterAccess Moderators
    ' Create s an SQL pass-through query using the supplied arguments:
    ' SPTQueryName: the name of the query to create
    ' SQLString: the query's SQL string
    ' ConnectString: the ODBC connect s tring, this must be at
    ' least "ODBC;"
    '---------------------- ------------------------- '
    On Error Resume Next

        Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
        Set mydatabase = DBEngine.Workspaces(0).Databases(0)
        mydatabase.QueryDefs.Delete SPTQueryName
        Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
        myquerydef.Connect = ConnectString
        myquerydef.SQL = SQLString
        myquerydef.Close
        
    End Sub

    [/code]

    The Pass Through Queries are created successfully with the hard coded date value instead of 

    strDate = Forms(frmName).Controls.Item("txtDate")

    The form/crosstab table referring to a normal access query, prior to the dynamically created pass through queries, would work fine.

    However the form/crosstab table after the use of the dynamic pass through queries, do not seem to refresh within the form.

    If I look at the query and execute this by itself, then it refreshes with the correct data.

    My thought is there needs to be another snippet of vba such as dbSeeChanges, but i cannot see where to place it in the vba.

    It may be something else but unsure why it is not refreshing the data in the form.  

    The change of course has been moving from the crosstab query / query / form  to a dynamically created pass-through query.

    I guess I will try to create a view directly on the sql server, and have this referred to within the access FE.

    But in the meantime, if anyone has any thoughts.

    Thank you.


    Thank you in Advance

    Thursday, August 2, 2018 10:48 AM

Answers

  • Solution:   

    in my testing, I noticed by reopening the subform, it retrieved the results...

    So of course, Requerying the subform will work.

    Forms![Dashboard List]![Dashboard List Subform].Requery

    Thank you kindly


    Thank you in Advance

    Friday, August 3, 2018 6:12 AM

All replies

  • As a brief update, 

    I did a quick check by putting the form in design mode and in form mode.

    And the data updates.

    So it seems a Repaint or Refresh or Requery issue has come up by employing a dynamic pass-through query.

    I have eliminate one possible cause of issue, 

    I have instead of the form referring to the dynamaic pass through query, ... I have pointed a static query (attached to the form) to the dynamic pass-through query.

    Hence re-focusing my direction, and will post an update.

    Thank you.


    Thank you in Advance

    Friday, August 3, 2018 12:02 AM
  • Hi MSDN, 

    I believe I have found the issue, but trying to work a solution now.

    After the Pass through query is dynamically recreated, according to the following post in utteraccess

    http://www.utteraccess.com/forum/Pass-Query-Clause-t1388283.html

    [code]

            

    Public Sub CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String)
    '-----------------------------------------------
    ' SUB: CreateSPT()

    PURPOSE:
    ' UtterAccess Moderators
    ' Create s an SQL pass-through query using the supplied arguments:
    ' SPTQueryName: the name of the query to create
    ' SQLString: the query's SQL string
    ' ConnectString: the ODBC connect s tring, this must be at
    ' least "ODBC;"
    '---------------------- ------------------------- '
    On Error Resume Next

        Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
        Set mydatabase = DBEngine.Workspaces(0).Databases(0)
        mydatabase.QueryDefs.Delete SPTQueryName
        Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
        myquerydef.Connect = ConnectString
        myquerydef.SQL = SQLString
        myquerydef.Close


    End Sub

    [/code]

    This recreates the pass through query fine, and if I manually open it in design mode, after it has run through the vba which triggers, then it has updated.

    But it requires me to either shut down the form or restart, and then the effect takes place.

    The pass through query is requiring a 'Refresh' similar to when you go to linked manager and click ok to refresh links when the SQL database underlying tables are changed.

    Do you know of any way to force this refresh of pass through query in vba

    I have looked at something but not sure what to write 

    https://www.sqlservercentral.com/Forums/1872220/RE-Dynamic-connection-string-for-pass-through-query

    https://access-programmers.co.uk/forums/showthread.php?t=215596

    These are the control selections for myquerydef

    CacheSize
    Cancel
    Close
    Connect
    CreateProperty
    DateCreated
    Execute
    Fields
    LastUpdated
    MaxRecords
    Name
    ODBCTimeout
    OpernRecordSet
    Parameters
    Prepare
    Properties
    RecordsAffected
    ReturnsRecords
    SQL
    StillExecuting
    Type
    Updatable

    I think maybe this website has the solution

    https://www.office-forums.com/threads/refresh-pass-through-query.670066/

    <article>
    Closing the application before switching the connection is necessary. So
    this good. After you relinked the new server, you need to "relink" the
    passthrough queries. I use this simple piece of code:

    Dim db As DAO.Database

    Set db = CurrentDb

    ' for each passthrough:
    db.QueryDefs.Item("passthoughQuery").Connect
    db.TableDefs.Item("linkedTable").Connect

    mfG
    --> stefan <--
    </article>
    Stefan Hoffmann, Jul 20, 2007

    Thank you in Advance

    Friday, August 3, 2018 2:34 AM
  • Indeed this is the issue.

    I have event tried to use the method on UtterAccess that only modifies the SQL, without the need to recreate the pass-through query.

    Hence the module of code that was working with the query within the form, was for a select query.

    [code]

    Public Sub DashboardDateQuery(qryDefinition As String, frmDefinition As String)
    Dim db  As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter

    Call PreparePassThroughQueries(frmDefinition)

    Set db = CurrentDb
    Set qdf = db.QueryDefs(qryDefinition)
        
        
        Forms(frmDefinition).Requery

    'Cleanup
    Set qdf = Nothing
    Set db = Nothing
    End Sub

    [/code]

    It was sufficient to requery the form with the query as the datasource.  This was working fine with access native queries.

    But now that we are using a pass through query with changing SQL code, then this now must be forced to refresh.  Once I get this... I shall close this discussion... unless any feedback.

    Thank you.


    Thank you in Advance

    Friday, August 3, 2018 4:41 AM
  • It may be something else but unsure why it is not refreshing the data in the form.  

    Hi CQA,

    I never use QueryDefs. All sql-strings are generated on the spot, and assigned to the RecordSource of the form in the Open event.

    After opening a form you can change the WHERE clause and/or the ORDER BY caluse of the used sql-string. Assigning the new sql-string the the RecordSource of the form results in a changed/updated form.

    You can even go a step further. By replacing both the RecordSource of the form, and the ControlSources of the respective controls, you can display quite different things in the same form. I extended on this technique, and use only one and the same form for any sql-string, powered by a metadata table with control definitions.

    Imb.

    Friday, August 3, 2018 5:10 AM
  • Thank you kindly Imb, 

    I think I am following... basically all processing is done at the front end.

    If so, once we moved to Azure, the performance is an issue, to which reason we are employing pass-through queries.

    I do have to admit if you can point me to an example of what you refer to above, as still learning my way.

    But in any event, if it is processing on the front end, it will not have performance to which reason I am employing T-SQL commands via the pass through query.

    I guess the other way will be to have a query or view saved on the server, and a variable is passed, and then the results retrieved.

    Thank you in advance for your help.


    Thank you in Advance

    Friday, August 3, 2018 5:50 AM
  • Solution:   

    in my testing, I noticed by reopening the subform, it retrieved the results...

    So of course, Requerying the subform will work.

    Forms![Dashboard List]![Dashboard List Subform].Requery

    Thank you kindly


    Thank you in Advance

    Friday, August 3, 2018 6:12 AM