none
Access 2016 and SQL RRS feed

  • Question

  • Thank you for taking the time to read my question.

    I want to start to learn how BEST to use SQL with MS Access. I can link the tables but I want SQL to do the "crunching" not MS Access.

    Can someone point me to some good sites that will tell me how to do this?

    Best way to set a form/report record set to a SQL view/stored proc
    Best way to open a record set for use in VBA (with or without variables - stored procedure or view)
    Best way to run a stored procedure
    ...other items I have not thought of yet?

    I'm quite green when it comes to SQL so I'm learning it all at once.

    Thanks again for your time I really appreciate it.

    Thursday, September 12, 2019 7:17 PM

Answers

  • This can be a big topic, but I will outline a few tips, and things that can help you a lot.

    >>Best way to set a form/report record set to a SQL view/stored proc

    As a general rule, using a store procedure for a report (or form) is rarely used, and in most cases it don’t help much. Keep in mind that using a store procedure as a report/form source results in a read only result.

    And keep in mind that any filtering of a store procedure has to occur by you passing parameters (the access client can’t filter a store procedure). In fact the access client can’t filter a pass-through query either.

    So, say using a store procedure for say a bound combo box tends to work poorly.

    But, using views? Yes, this is the “go to” solution for most cases.

    There are several “huge” advantages of using views.

    Remember, if you base a report on a linked table say of 1 million rows, and do this:

    Docmd.OpenReport "Invoice",,,"InvoiceNum = 123

    If you are using:

    Access back end, share on a network:

    Access will ONLY pull the one invoice record down the network pipe.

    If you are using:

    SQL server back end.

    Standard linked table to SQL server.

    Once again, in this case, Access will ONLY pull the one invoice record down the network pipe.

    If you base the report say on a linked view (as opposed to a linked table), then again, Access will correctly filter the report, and only pull records that meet your criteria.

    What this means:

    If the report is based on a single table, then using a standard linked table (directly to the base table), or using a view will thus filter correctly for you.

    (So, all 3 cases will run the same speed). In fact, a pass-through query, or a store procedure will NOT run faster either!

    A pass-through query, or a store procedure (which you use a PT query to consume in most cases) cannot filter the data client side (so you have to pass the filter at query create time, or store procedure call time).

    You can achieve substantial performance gains if the query the report is to be based on is complex (multiple tables, multiple joins, and say even some group by (aggurate sums).

    In this case, then you take the complex query (access client side), convert to a view, and link the report to that view.

    The above is VERY significant, since now if you have some complex “report criteria” prompt form, then you  can continue to use that prompt form, and use that “where” clause.

    In other words, you don’t’ have to change any VBA etc. to get great performance and ensure that the server is doing the work here.

    And the above means that the SQL server is doing the work to filter the data, not the client side.

    The same goes for when you load a form. Forms can remain bound to the base linked table (sql back end).

    The trick here is to near always ASK the end user the criteria before you launch the form.

    Access often gets a bad rap for pulling too much data, but in most cases, it is the fault of the developer.

    So, if we have an invoicing system, (form, sub form details) of say an invoicing system?

    Well, simple “ask” the user what invoice to work on BEFORE you launch the form. No views, store process or any special coding is required here.

    The simple trick for great performance is to never load up a form bound to a large table without using the standard “where” clause of the open form.

    So you simply want to “prompt” the user the BEFORE you load up these large data bound forms.

    So, you want to build some kind of prompt form, and ask/get the criteria before launching the form.

    This advice applies to any access application, not just ones with SQL back end.

    Take a read of this article of mine in regards to searching:

    http://www.kallal.ca/Search/index.html

    The above short article will result in MORE performance increase in your application then reading 10 books on SQL server.

    So, read the above, and you will have un-locked the best kept secret on how to improve performance in Access. And really nice, is the above simple idea in that article ALSO works with standard access (non sql server) based applications.

    So, forms etc. can remain as bound to the linked tables, even with SQL server. The issue not using SQL server, but adopting deigns that “limit” the data you pull into forms in the first place.

    All too often, we access folks simply launch a form bound to some huge table, and THEN expect the user to use ctrl-F to find the record. But that is already too late, since you now searching every record you downloaded into that form.

    So, after an access to SQL migration fully 99% of the forms and existing code will run and work as before.

    The vast majority of speed up tricks actually applies to non sql based applications also!

    In other words, using SQL server does not really speed things up (in fact, you find some things run slower!!!).

    The key concept here is to limit data you pull. But the approaches to limit that data being pulled by Access in near all cases apply just as well to non sql server based back ends.

    However, tops in the tip list?

    Use views for complex queries, and then link to that view. (Remember, all of the previous advice was in regards to a linked table). But if the query is “complex” and involves multiple tables, then use a view (this forces the sql work to run server side).

    So with a view, then forms (or report) filtering can continue to be done as before (use the “where” clause of the open form/report).

    So, it is kind of a myth that access always pull the whole table. Access does not, and this is even true when using Access file based back ends.

    >>opening recordsets

    Same approach as you use now. The only change is you will use this:

    old:

       Dim rst     As DAO.Recordset

       Dim strSQL  As String

       strSQL = "select * from dbo_tblHotels where HotelName = 'DAYS INN'"

       Set rst = CurrentDb.OpenRecordset(strSQL)

    The above with sql server will become:

       Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    So, as a general rule you change the openRecordSet to include the above, but other  then that, really the same as before.

    Now, once again, if the “query” you pulling into the reocrdset is a complex query (more than one table), then create a view server side, and link that view (access will see this as a linked table). Now, just use that view in your reocrdset, and you off to the races.

    The above is a “basic” approach and survival guide in regards to using SQL server. There is very little beyond the view trick that you need to get great performance.

    >> Best way to run a stored procedure

    Well, noted, I “rare” use store procs. If you have a “large” update routine that updates “many” records, then sure, moving that complex update routine to a store procedure can really help.  However, a Pass-through query is also just fine, and they are less hassle then a store procedure.

    And as a “general” solution to pull records, then views are not only a great choice, but tend to be the least amount of work, and better is views allow existing approaches VBA filters that worked well in the past (when not using sql server).

    However, here is an example how I call a store procedure from Access:

       With CurrentDb.QueryDefs("PTInvoiceUpdate")
    
          .SQL = "exec spInvoice  12345"
          .Execute
    
       End With
    


    So, the above is only 3 lines of code, and is an easy way to use a store procedure as a source for say a report. Of course the invoice number in above is hard coded, but if it was same VBA variable, then above becomes:

          .SQL = "exec spInvoice  " & strInvoiceNumber

    So, the above query is a pass-though query. You create a PT query, and then use that PT query to call store procedures. 

    So, the above is really a summary of the concepts you need to adopt for great performance with Access and SQL server.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Edited by Albert D. Kallal Sunday, September 15, 2019 5:41 PM
    • Marked as answer by mbrad Monday, September 16, 2019 4:10 PM
    Sunday, September 15, 2019 5:40 PM

All replies

  • https://support.microsoft.com/en-us/help/303968/how-to-create-an-sql-pass-through-query-in-access

    https://www.youtube.com/watch?v=TjmadpWeA_M


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Thursday, September 12, 2019 7:29 PM
  • The problem is that you want to learn how to BEST use Access with SQL or you want to LEARN how to BEST use Access with SQL

    For the  1st case its easy...just google around and you will find plenty

    If you go for box No2 then you need

    To Learn Access very well...grab some good books and start

    To Learn SQL pretty well ...again some books

    To learn Access+SQL ...well complete step 1 and 2 and we will talk some more.


    Thursday, September 12, 2019 7:35 PM
  • Views are great for display of data that will not be edited such as reports or static forms.

    Pass-through queries are faster than using Access compiled queries

    You will find SQL Server stored procedures are much better for routine maintenance such as batch updates/deletes.

    There really is a lot more to it. There are several good books on using Access with SQL Server back ends. While it is a bit dated "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman is an excellent choice. So is "Microsoft Access VBA: Programmer's Reference" by Teresa Hennig et al.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Friday, September 13, 2019 3:29 PM
  • Thank you all for your replies. I will continue to search on line and will look into the books as well.

    all the best

    Friday, September 13, 2019 3:40 PM
  • This can be a big topic, but I will outline a few tips, and things that can help you a lot.

    >>Best way to set a form/report record set to a SQL view/stored proc

    As a general rule, using a store procedure for a report (or form) is rarely used, and in most cases it don’t help much. Keep in mind that using a store procedure as a report/form source results in a read only result.

    And keep in mind that any filtering of a store procedure has to occur by you passing parameters (the access client can’t filter a store procedure). In fact the access client can’t filter a pass-through query either.

    So, say using a store procedure for say a bound combo box tends to work poorly.

    But, using views? Yes, this is the “go to” solution for most cases.

    There are several “huge” advantages of using views.

    Remember, if you base a report on a linked table say of 1 million rows, and do this:

    Docmd.OpenReport "Invoice",,,"InvoiceNum = 123

    If you are using:

    Access back end, share on a network:

    Access will ONLY pull the one invoice record down the network pipe.

    If you are using:

    SQL server back end.

    Standard linked table to SQL server.

    Once again, in this case, Access will ONLY pull the one invoice record down the network pipe.

    If you base the report say on a linked view (as opposed to a linked table), then again, Access will correctly filter the report, and only pull records that meet your criteria.

    What this means:

    If the report is based on a single table, then using a standard linked table (directly to the base table), or using a view will thus filter correctly for you.

    (So, all 3 cases will run the same speed). In fact, a pass-through query, or a store procedure will NOT run faster either!

    A pass-through query, or a store procedure (which you use a PT query to consume in most cases) cannot filter the data client side (so you have to pass the filter at query create time, or store procedure call time).

    You can achieve substantial performance gains if the query the report is to be based on is complex (multiple tables, multiple joins, and say even some group by (aggurate sums).

    In this case, then you take the complex query (access client side), convert to a view, and link the report to that view.

    The above is VERY significant, since now if you have some complex “report criteria” prompt form, then you  can continue to use that prompt form, and use that “where” clause.

    In other words, you don’t’ have to change any VBA etc. to get great performance and ensure that the server is doing the work here.

    And the above means that the SQL server is doing the work to filter the data, not the client side.

    The same goes for when you load a form. Forms can remain bound to the base linked table (sql back end).

    The trick here is to near always ASK the end user the criteria before you launch the form.

    Access often gets a bad rap for pulling too much data, but in most cases, it is the fault of the developer.

    So, if we have an invoicing system, (form, sub form details) of say an invoicing system?

    Well, simple “ask” the user what invoice to work on BEFORE you launch the form. No views, store process or any special coding is required here.

    The simple trick for great performance is to never load up a form bound to a large table without using the standard “where” clause of the open form.

    So you simply want to “prompt” the user the BEFORE you load up these large data bound forms.

    So, you want to build some kind of prompt form, and ask/get the criteria before launching the form.

    This advice applies to any access application, not just ones with SQL back end.

    Take a read of this article of mine in regards to searching:

    http://www.kallal.ca/Search/index.html

    The above short article will result in MORE performance increase in your application then reading 10 books on SQL server.

    So, read the above, and you will have un-locked the best kept secret on how to improve performance in Access. And really nice, is the above simple idea in that article ALSO works with standard access (non sql server) based applications.

    So, forms etc. can remain as bound to the linked tables, even with SQL server. The issue not using SQL server, but adopting deigns that “limit” the data you pull into forms in the first place.

    All too often, we access folks simply launch a form bound to some huge table, and THEN expect the user to use ctrl-F to find the record. But that is already too late, since you now searching every record you downloaded into that form.

    So, after an access to SQL migration fully 99% of the forms and existing code will run and work as before.

    The vast majority of speed up tricks actually applies to non sql based applications also!

    In other words, using SQL server does not really speed things up (in fact, you find some things run slower!!!).

    The key concept here is to limit data you pull. But the approaches to limit that data being pulled by Access in near all cases apply just as well to non sql server based back ends.

    However, tops in the tip list?

    Use views for complex queries, and then link to that view. (Remember, all of the previous advice was in regards to a linked table). But if the query is “complex” and involves multiple tables, then use a view (this forces the sql work to run server side).

    So with a view, then forms (or report) filtering can continue to be done as before (use the “where” clause of the open form/report).

    So, it is kind of a myth that access always pull the whole table. Access does not, and this is even true when using Access file based back ends.

    >>opening recordsets

    Same approach as you use now. The only change is you will use this:

    old:

       Dim rst     As DAO.Recordset

       Dim strSQL  As String

       strSQL = "select * from dbo_tblHotels where HotelName = 'DAYS INN'"

       Set rst = CurrentDb.OpenRecordset(strSQL)

    The above with sql server will become:

       Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    So, as a general rule you change the openRecordSet to include the above, but other  then that, really the same as before.

    Now, once again, if the “query” you pulling into the reocrdset is a complex query (more than one table), then create a view server side, and link that view (access will see this as a linked table). Now, just use that view in your reocrdset, and you off to the races.

    The above is a “basic” approach and survival guide in regards to using SQL server. There is very little beyond the view trick that you need to get great performance.

    >> Best way to run a stored procedure

    Well, noted, I “rare” use store procs. If you have a “large” update routine that updates “many” records, then sure, moving that complex update routine to a store procedure can really help.  However, a Pass-through query is also just fine, and they are less hassle then a store procedure.

    And as a “general” solution to pull records, then views are not only a great choice, but tend to be the least amount of work, and better is views allow existing approaches VBA filters that worked well in the past (when not using sql server).

    However, here is an example how I call a store procedure from Access:

       With CurrentDb.QueryDefs("PTInvoiceUpdate")
    
          .SQL = "exec spInvoice  12345"
          .Execute
    
       End With
    


    So, the above is only 3 lines of code, and is an easy way to use a store procedure as a source for say a report. Of course the invoice number in above is hard coded, but if it was same VBA variable, then above becomes:

          .SQL = "exec spInvoice  " & strInvoiceNumber

    So, the above query is a pass-though query. You create a PT query, and then use that PT query to call store procedures. 

    So, the above is really a summary of the concepts you need to adopt for great performance with Access and SQL server.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Edited by Albert D. Kallal Sunday, September 15, 2019 5:41 PM
    • Marked as answer by mbrad Monday, September 16, 2019 4:10 PM
    Sunday, September 15, 2019 5:40 PM
  • WOW!! Thank you so VERY much Albert for a great reply to my question. I truly appreciate the time/effort put into this. Have a great day eh!

    Monday, September 16, 2019 4:10 PM