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

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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