The change of course has been moving from the crosstab query / query / form to a dynamically created pass-through query. It may be something else but unsure why it is not refreshing the data in the form. 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. If I look at the query and execute this by itself, then it refreshes with the correct data. However the form/crosstab table after the use of the dynamic pass through queries, do not seem to refresh within the form. The form/crosstab table referring to a normal access query, prior to the dynamically created pass through queries, would work fine. The Pass Through Queries are created successfully with the hard coded date value instead of
Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName) Set mydatabase = DBEngine.Workspaces(0).Databases(0) '- 'ĭim mydatabase As DAO.Database, myquerydef As DAO.QueryDef ' ConnectString: the ODBC connect s tring, this must be at ' SPTQueryName: the name of the query to create ' Create s an SQL pass-through query using the supplied arguments: Public Sub CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String) SQLString = "SELECT Employees.* FROM Employees UNION SELECT Management.* FROM Management " ID "Ĭall CreateSPT(SPTQueryName, SQLString, ConnectString) SQLString = SQLString & "ORDER BY Calls. SQLString = SQLString & "WHERE (((Calls.)='" & Format(strDate, "YYYY-MM-DD") & "')) "
SQLString = SQLString & "ON Category.ID = Calls.Category) ON Customers.ID = Calls.Caller " ID = Calls.) ON Employees.ID = Calls.) ON. SQLString = SQLString & "FROM Customers RIGHT JOIN (Category RIGHT JOIN ( RIGHT JOIN (Employees RIGHT JOIN ( JOIN Calls " SQLString = SQLString & ".) AS Dashboard " SQLString = SQLString & "Customers., Customers.,Customers.,Customers.," SQLString = SQLString & "Calls.ID, Calls., Calls., Calls., Calls., " SQLString = "SELECT CONVERT(varchar, ,103) AS. SPTQueryName = "Dashboard View Start UPDATE"ĬonnectString = "ODBC Description=SQL Management Server DRIVER=SQL Server SERVER=ABAPLUMBINGPC\SQLEXPRESS Trusted_Connection=Yes DATABASE=ABA PLUMBING Call Tracker V7" StrDate = Forms(frmName).Controls.Item("txtDate") Public Sub PreparePassThroughQueries(frmName) Public Sub runPreparePassThroughQueries(frmName) Now that we have created Pass through queries, we have dynamically created the pass through query via Public Sub DashboardDateQuery(qryDefinition As String, frmDefinition As String)Ĭall PreparePassThroughQueries(frmDefinition) Private Sub cmdRunDashboardDateQuery_Click()Ĭall DashboardDateQuery("Dashboard View Start UPDATE_FIRST_LAST JOIN_Crosstab", "Dashboard List")įorms!.txtTimeDescription.SetFocus All queries were native queries, not Pass-Through Queries I previously have a function vba code, executing a query for an Access FE / Access BE.
0 kommentar(er)
