Interfacing an Access Front-end to a SQL Server Back-end RRS feed

  • General discussion

  • I have found the combination of Access/SQL to be the (almost) perfect development environment for creating database apps. Even if the FE is migrated to a web app, Ill use Access as the FE for admin and maintenance stuff.

    My question:  What's your preferred method for manipulating and rendering SQL data via Access, and which results in the best performance...stored procs? ADO? Something else?

    Look forward to hearing your responses

    Darrell H Burns
    Friday, November 19, 2010 6:16 PM

All replies

  • As Tony advised I'm jumping in with my question:

    What is better: create a table-valued function via SSMS and use the result with pass-through query in Access or create a saved query in Access (or even hard-coded SQL string) which will return the same data recordset?

    Andrey V Artemyev | Saint-Petersburg, Russia
    Friday, November 19, 2010 6:31 PM
  • "DarrellDoesData" wrote in message

    I have found the combination of Access/SQL to be the (almost) perfect development environment for creating database apps. Even if the FE is migrated to a web app, Ill use Access as the FE for admin and maintenance stuff.

    My question:  What's your preferred method for manipulating and rendering SQL data via Access, and which results in the best performance...stored procs? ADO? Something else?

    Look forward to hearing your responses

    Darrell H Burns
    The actual preferred approach for me is to do the least amount of work possible! Now that might sound a little bit funny, but I'm actually being truthful and honest here.
    So surprisingly, the least amount of work and a lot of cases is for example to take a query, converted it to a view in SQL server, and setup a link to that view. This tends to be less work than say developing a store procedure or some other type of server based coding approach. Sometimes additional work such as stored procedures might yield you better performance increases, but they're not worth the extra effort and time. In other words one is looking for balance between how much performance more do you get, for the given effort and time. We don't have unlimited budgets and time here. If you have to spend too much time performance tweaking, then the cost for those efforts will increase the cost of your application too much. So you only do as much as time and efforts will allow.
    In the case where I have a bunch of linked tables to SQL server and we're building quarries on those tables, I continue to use the standard access query builder. For the most part you're not going to gain any significant performance and advantages by trying to use pass through queries or whatnot WHEN talking about a single table query.
    As I've noted several times here, I have a number of applications that I deploy that work over standard Internet connections and even Wi-Fi. I thus have the rich access client installed on the laptop, and it's hitting the sql server that resides over the Internet. In these cases, then often some little performance details as you noted become significantly important.
    For the most part, if a query is hitting a SINGLE table, or even if a form is bound to a linked table and you open it with a where clause, you'll only find the one record gets pull down the pipe. In this case linked table performance for this type of scenario is totally adequate. You really don't have to tweak more performance here.
    Where you get really large gains is any type of query that joins in more then one table and query has any type of Aggregate summations or totals.  In these cases you have a tremendous amount of choices from pass through queries, stored proc and what not. However again the most most cost effective approach I found and least amount of effort for greatest performance gains can be had by simply moving that query to the server side (cut + paste), and then turning that query it into a view. You then setup a link to that view. (It will show up as a linked table). This also works well for relinking routines and thus a few additional maintenance issues that make this view approach a little bit simpler for deployment as opposed to store procs etc. So in place of parameters and stored procedures, I'd tend to create additional views when I have a performance bottleneck, and this process goes quite fast.
    Since in most instances I'm taking an existing application, and ones that might have been around for significant amount of time, I continue to use DAO, And again there's not a whole lot advantage to converting an existing application with DAO code that someone's paid good money for to run and debug for years into some ADO code. Furthermore in most if not all of my server based applications, I simply create one or 2 pass through queries in the query builder. These allow me to execute any SQL Command as pass though from DAO anyway with great ease:
        Dim qdfPass       As DAO.QueryDef
        Set qdfPass = CurrentDb.QueryDefs("MyPass")
        qdfPass.SQL = "exec sp_myProc"
    You can see in the above that is not very much code, and the above sql string can be any legitimate SQL server command or stored procedure on the server. And, you can add params to the above string. ADO going to gain you nothing in terms of performance here at all.
    In fact I often create two pass through queries for the whole application, one like above that has the return records = no, and another set with the above = yes for those Procedures and commands that do in fact return data.
    So, in most cases because you have a bunch of existing designs in SQL within an existing Access application, when you come across a query or something that runs slow, it tends to be a pretty rapid to cut and paste that sql into the server side and create a view.  You then link and you done performance wise. So, that what I mean by little work for big performance gains.
    There's a few more other issues I could point out such as basing combo boxes directly on a linked view to SQL server as opposed to have ANY sql in the combo box source. They run a lot more snappy when you do that (no sql in the source but JUST view name).
    So for the most part I avoid using stored procedures, I avoid passing parameters to store procedures, and for the most part will create a view. Note that reports based on a view responed VERY well to where clauses in existing code also. So, reports filter correctly even when you launch a report with a where clause, and the report is based on that view. (again, less work, less code changes).
    You can always of course push things even farther by using things like record sets and pulling data into them from the server, but then you start to spend too much valuable development time on building things that you should not have to touch in the first place.
    For example I have a full screen calendar that runs amazingly well over the Internet, the query the calendars based was slow as a dog since it took data from several tables. I simply converted that query to a view, and the rest the VBA code that pulled in the recordset was unchanged, and the results were great increase in performance. The calendar now works as well when I had this running in a typical office split LAN setup.
    Of course the basic other areas is Avoid opening a form attached to a large table unless you use a where clause, but that was good advice even for non server based applications with of access.
    So, bound forms and where Clauses work fine with server based systems, and I lean quite heavy on views to solve Performance bottlenecks.
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Saturday, November 20, 2010 3:54 AM
  • I used to like ADP's a lot, but the writing is on the wall...

    So I switched to ODBC-attached tables, doing everything with DAO. It is rare for me to need ADO for anything.

    Passthrough queries are very powerful. I will use stored procedures if the complexity warrants it, but typically not for trivial CRUD queries.

    -Tom. Microsoft Access MVP
    Saturday, November 20, 2010 4:24 AM
  • Albert makes a great point about ROI.

    The biggest gains I've seen were from converting complex access queries to pass throughs.

    Cutting around 95% off the time to execute. Such an easy thing to do and such a wow factor for the client who you've just convinced to upsize.

    Big improvement without having to invest much into learning.

    Sometimes, however, it's easy to lose perspective on the ROI issue and start doing stuff for your own education.

    Views can be good too, they cut development time greatly and, if you have clients who want to create their own queries and reports (and I do), it's a great way to present them with denomalised data.

    Saturday, November 20, 2010 7:37 AM
  • Hi Darrell,
    You've got some other good advice in this thread.  If you want more
    info on this, I've written a PowerPoint presentation on techniques for
    using Access as a client-server front-end to SQL Server databases.
    It's called "Best of Both Worlds" at our free J Street Downloads page:  It includes some thoughts on when to use SQL
    Server, performance and security considerations, concurrency
    approaches, and techniques to help everything run smoothly.
    Armen Stein
    Microsoft Access MVP
    Monday, November 22, 2010 4:29 AM
  • Thanx, Armen. I just took a drive thru your Powerpoint and got some good tips.
    Darrell H Burns
    Monday, November 22, 2010 7:38 PM