Looking for info on how/when to use SQL queries, Views and/or stored procedures. An Access FE linked to SQL BE would be nice to tear apart to see how FE forms/report record sources are connected to BE.
Background......
Converted/converting a split Access FE/BE to SQL Server. (right now using SQLSE 2014 on my local computer).
Tables have been migrated to SQLSE 2014, all data copied no problems. Did some structure changes, deleting tables, creating different tables.
Access FE working well - fixed relationships/queries/UDFs and can add data; new records match the old existing records.
I used a backup BE: one table has around 8,500 records and another has over 1.5 million records (adding around 24,000 records/month).
With that many records, printing reports took longer and longer as the number of records grew (Access FE/BE).
I wrote a scalar function and a stored procedure in SQL (SQL 2005 syntax), but can't find any info on how, when or what to use:
when do I/can I use a View?
when do I/can I use a SP?
when do I/can I use a Query?
There is lots of info on tables in SQL, but very little on using SQL queries, Views and/or stored procedures in an Access FE.
I've been searching on line and looking for books, but only found one book "ACCESS 2000 Developer's Handbook Volume 2: Enterprise Edition" that even touches on the Access side of Access FE/SQL BE. (still trying to digest it)
For reports, I think I can use a pass thru query (SQL query like an Access query?) for reports.
If I need to use a UDF (scalar function) do I have to/should I use a SP?
TIA for any nudge in the right direction.....