Access 2007 front end, SQL 2008, backend. Small legal office. Windows 2007 Professional work stations, Windows 2008 Server, Domain established, AD implemented, ODBC working fine. Good VBA and data architecture skills.
This is my first experience with using SQL server as a back end. I understand building your query and then selecting the SQL Pass-Through query option and seeing the translation to SQL as it is needed. Some articles say to copy and paste the code to SQL Server as a query in SSMS and/or as Stored Procedure or TSQL to cut down on the data transfer. (not sure I have that exactly correct).
I understand converting Access SQL to SQL Server TSQL and changes in _ and " and '. I understand DoCmd.RunSQL.
Three questions:
1) If I create a SQL Pass-Through, do I save it locally just like a standard Access 2007 query so it shows up in the query list of Access 2007, but when it executes it runs it all on the SQL Server returning only the results,like a pass-through?
2) If I create a SQL Pass-Through, do I have to cut and paste it to a query window on the SQL Server? If I do need to place it on the SQL Server, how do I reference the query from the front end?
I have read a number of articles which discuss the process of creating the query but they are vague or varried on where it must be saved and how it is referenced. Or perhaps I just didn't understand it.
3) I read another response that said Access 2007 is far more discerning on the data it returns and this is not the solution to query execution that everyone says it is.
Thanks Phred