Results 1 to 8 of 8

Queries, Views, Stored Procedures, Oh my!

  1. #1
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,012

    Queries, Views, Stored Procedures, Oh my!

    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.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,053
    It is generally significantly faster to use stored procedures rather than views or queries in access.

    Within sql server views are used to provide a 'flatfile' recordset view of the data (e.g. to combine say a customer record with an address record) and/or to provide a prefiltered view of the data (e.g. to only show invoices generated in the current financial year) and primarily used to 'prerestrict' users view of the data and not allow direct access to the tables. You cannot apply parameters to views.

    Stored procedures can return recordsets, add, delete or update records and you can use parameters.

    To execute a stored procedure you use a passthrough query (an option when you are in the query window).

    You will need to set the connection string which will normally be the same as the one you would have for a table linked to the relevent server - something like

    Description=mySQLServer;DRIVER=ODBC Driver 13 for SQL Server;SERVER=serverlocation;Trusted_Connection=No ; DATABASE=mySQLDatabase;Encrypt=Yes;

    Open the properties window from the ribbon and put the connection string in the ODBC Connection String property

    And in the query window put

    EXEC "myStoredProcedure"

    or with parameters

    EXEC "myStoredProcedure param1, param2"

    If your stored procedure returns a recordset (i.e. a select query), you will see the recordset when you open the query in datasheet view

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    17,533
    Quote Originally Posted by Ajax View Post
    It is generally significantly faster to use stored procedures rather than views or queries in access.
    I'm not sure I agree. It can be significantly faster, but Access is pretty good at passing SQL back to the server when it can, so simpler queries likely won't see a speed difference if converted to stored procedure. I tend to start with Access queries against linked tables since they're simpler to create and I'm lazy, and convert anything that doesn't perform well to something server based.

    Other tools not mentioned include table-valued functions, which I've used, and CTE's, which I haven't tried yet but hear good things about. A table-valued function and a stored procedure can both take parameters, but the function can be used in a FROM clause. I think you can stuff more programming logic into a stored procedure. An option to pass-through queries is an ADO Command object, though I seem to use pass through queries now. Frankly there are so many tools I'd also be interested in a "here's when to use what" tutorial. I've just learned as I went, so there are gaping holes in my knowledge.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,053
    I did say generally

  5. #5
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    559
    Hi, some fast (very) general rules:
    * if security is an issue, do as much as possible on the SQL server;
    * T-SQL is a lot more powerful and flexible than Access SQL, to use it in Access write procedures;
    * views in SQL server are like the queries you save in Access. Use them to already flatten the table structure on the server and make your work in Access easier. You can also use them to set security and restrain the access to the database to those views in stead of directly into the tables.
    * if you use batch processes execute them on the back-end, if you use record by record processing Access VBA is faster;
    * I prefer to write my reports on SSRS(SQL server reporting services) and publish them on the SQL website where the users can download the results in PDF or Excel format than write them in Access, but that's a personal choice

  6. #6
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,012
    Where I'm at right now....

    Been doing a LOT of reading/experimenting.
    My understanding so far: (with thanks to Ajax and Paul)

    I create a stored procedure that functions (returns records) like a saved query in Access. Then I create a pass through query (lets call it "qrySPTest1") that "calls" the SP. I can set a report/form record source to that saved Access query ("qrySPTest1"), but I need to modify the query "qrySPTest1", changing the parameters each time I change/requery the form...... Close??


    A VIEW is somewhat like an Access query, but if you use a WHERE clause it is not "dynamic" like the Access query.
    If I create a VIEW (in SQLSE), I should link the VIEW to Access like a linked table. Then I can use the VIEW like a table: use the VIEW in an Access query..... but it still brings all the records across the network, although the returned records set could be smaller.




    For reports, the record source should be (for the most part) a SP. I have gotten 2 reports to display data correctly (ie the correct data).. Whoo-hoo!


    For forms, the record source should be (for the most part) a SP? If I need to filter 1.5+ million records down to around 25, the only way I could figure out is to use an SP. Does that mean the saved queries I have in Access will be/should be SPs??


    ------------------
    @NoellaG - thanks for the info..
    You said
    * I prefer to write my reports on SSRS(SQL server reporting services) and publish them on the SQL website where the users can download the results in PDF or Excel format than write them in Access, but that's a personal choice
    So far, I'm only up to my big toe.......How do I get started with SSRS?
    "the SQL website" - is there an URL?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    ArviLaanemets is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Apr 2017
    Posts
    310
    For reports I mostly use SQL Server views linked into front-end. Maybe when the number or records in query will be very big, it will slow down? On other hand, the SQL Server query syntax is more advanced than the query syntax in Access (you can get a result in single query, when you need several saved subqueries in Access), and more optimized, so the time what you win on having data in back-end, you may lose on calculations access needs to get the result.

    P.e When I need various reports about articles purchased over some period or from certain providers, I design a view in SQL Server like vPurchases: PurchaseDate, PurchaseMonth (in format yyyymm), PurchaseYear, ArticleNo, ProviderID, ArticleQty, ArticlePrice, and link it to front-end as vPurhases. I can then use it to design sources for various reports (using GROUP BY to summarize). And to have report for specific month, year, or provider, I use report's Filter property when calling the report.

  8. #8
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    559
    Hi
    or forms, the record source should be (for the most part) a SP? If I need to filter 1.5+ million records down to around 25, the only way I could figure out is to use an SP. Does that mean the saved queries I have in Access will be/should be SPs??
    You need to avoid to pull all those records through the network and filter them as much as possible at the base, so yes I would use stored procedures there to minimize the network traffic.

    SSRS is a service of SQL server that is included in the standard version (even with the light version since the latest versions) , but not set up by default. When you set it up, you define the website (URL) where the reports will be published yourself . There are a lot of tutorials how to set up SSRS on the internet.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2012, 07:32 PM
  2. Set date parameters within Stored Procedures
    By Brian62 in forum SQL Server
    Replies: 0
    Last Post: 02-15-2012, 03:40 PM
  3. stored procedures failure
    By MrGrinch12 in forum Programming
    Replies: 1
    Last Post: 06-23-2010, 11:54 AM
  4. Event procedures
    By GIS_Guy in forum Forms
    Replies: 1
    Last Post: 05-11-2010, 01:34 PM
  5. Alter Views/Queries in MDB from C# application.
    By avadhutkhavare in forum Access
    Replies: 0
    Last Post: 11-03-2009, 03:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums