I have been trying to speed up a query that takes a long time to run on my users who use a VPN connection with Hotspots.
I have an Access front end linked to a SQL Server backend. I have a table called Review which contains a bunch of information as well as the Team Lead column. There is a related table called Team Members which has the additional people who go on the review. These tables are linked by review id. My dilemna is on my Calendar form. Depending on a hidden form which contains User Name, a query looks at if you are either in the team lead table OR in the Team Member table as member. The second query takes the result of the first query and queries back against the Team Member table to get the other folks who are the review with you (if you are not the team lead). The first query is the one that takes forever to run. Since it is looking for a parameter in 2 different tables, it is very slow. I thought I might try and make a view in SQL and link that to the Access front end, but I can't code in an access form parameter there. I thought about pass through queries, but don't think that will work either. I know I probably did not do a great job of explaining, but does anyone have any ideas on how I might optimize this? Thanks in advance for any help you might provide.
I have an Access front end linked to a SQL Server backend. I have a table called Review which contains a bunch of information as well as the Team Lead column. There is a related table called Team Members which has the additional people who go on the review. These tables are linked by review id. My dilemna is on my Calendar form. Depending on a hidden form which contains User Name, a query looks at if you are either in the team lead table OR in the Team Member table as member. The second query takes the result of the first query and queries back against the Team Member table to get the other folks who are the review with you (if you are not the team lead). The first query is the one that takes forever to run. Since it is looking for a parameter in 2 different tables, it is very slow. I thought I might try and make a view in SQL and link that to the Access front end, but I can't code in an access form parameter there. I thought about pass through queries, but don't think that will work either. I know I probably did not do a great job of explaining, but does anyone have any ideas on how I might optimize this? Thanks in advance for any help you might provide.