Creating Access 2013 Reports using a SQL Server backend

I have been developing in Access for many years but I'm fairly new to doing so with a SQL server back end.  My other backends have been Access mdb or accdb's.

I'm starting to work on reports in the project and wonder what kind of techniques Access developers use when the backend is SQL Server.

In prior projects using an Access back end I would sometimes set up a standard query combined with input parameters to create a temporary table in the Access front end and bind the report to the temp table.  Other times I would bind the report directly to a query.

We are using SQL server because the data size of about 250GB is far beyond the 2GB access limit.  Since the data is so voluminous the 'create a temporary access table to drive the report' approach  is not feasible.

So far in my SQL learning curve I have bound a form to a recordset generated from a stored procedure.  I can see this also being done to bind a report to a recordset returned from a stored procedure.  The selection criterion would be passed as parameters to the stored procedure.  The SP would then return the filtered (based on the past parameters) recordset which would bound to the report.

I just wanted to check with the EE experts before heading down the wrong path.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I start with linked tables and Access queries.  Criteria generally comes from a form or the where argument of the OpenReport method. If that's not fast enough, I try a query against a view.  My last choice is a query against a sp.  If you are going to use Access as the FE, you should use "Access" methods as much as possible.  Most of the time forms and reports work fine against linked tables even with millions of rows.  Index them appropriately.  Views can speed up joins if you end up with more than two or three tables in the query.

The only time I would even consider making temp tables is if the query is complex, summarizes the data, and multiple reports will be made from the resultset.  You can use a sp to make the temp table on the server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<In prior projects using an Access back end I would sometimes set up a standard query combined with input parameters to create a temporary table in the Access front end and bind the report to the temp table. >>

  That's a common technique because with linked tables against SQL, one thing not obvious is that the query will always execute locally if:

1. You join to local tables.

2. You use VBA expressions in the query

3. You use JET/ACE specific SQL.

  This always forces a query to execute locally (JET/ACE does try and pass queries to the back end for execution if it can).

  But since your data sets are large, stored procedures are the way to go mainly, but don't forget about using views as well.   Using them can still leave the brunt of the work on the server, and yet still give you the simplicity of a linked "table" in Access.

mlcktmguyAuthor Commented:
Thank you very much.  Both of you have given me excellent advice on many matters in the past.  I really appreciate your input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.