"I have been repeatedly advised not to have SQL Server run Office automation"

We all hear this, probably because Office needs security patches from time to time. I would never suggest letting users have access to an office application running on the server.

But, in most cases, you will loose this argument to the server group.

This is probably more than you ever wanted to know...

Here is what I have in one support environment.

1. Access MDE with a secure MDA application. I use 3rd party tools to provide modern tree views and an Outlook style look and feel. This resides on each users PC.

2. Access uses linked Views - the vast majority of links are to SQL Views built with T-SQL. Access VBA also calls on Stored Procedures. I also generate Pass Throgh Queries and store the T-SQL in Access code using case statements and appends to alter the request.

2. With Excel VBA and Excel Object Model, dynamically create each Excel Workbook. Start with Excel, add a worksheet, and all the gory details. My Workbooks may have a daily download of Wall Street prices for a commodity per location, basis, transport and other current data. The associated worksheets have actual formulas in the cells. Brokers want to conduct what-if situations on futures and look at the associated spreads for example.

This is not something that people who just copyfromrecordset need to do. The Excel workboods can be dynamically created from the options a user checks or enters on the user interface form.

3. A launcher on the users desktop checks for the last date of the Access MDE application. When I update the application, each user's PC checks for latest version and downloads it. Users are in the local office and in many remote sites.

The interface on step 1 uses SQL Server to maintain each users security, views, and favorites.

Basically, I have a development version. Compile it. Post it on the server and all users automatically get the latest version.

Since 99% of the processing takes place on SQL Server, even a large complex 16 worksheet (full of formulas and current data) will take between 1 to 25 seconds to build from scratch. And, most of these offer the user a dozen custom choices such as: Customer name, from-to dates, overview / details, graphs, ...

Just to give you an idea, the application code and forms (no data) is about 130 MB with about 150 user form interfaces and 200 very active users. Once in a while, simple reports use Access Reports. Excel has the advantage of having the business rules and formulas included so everyone can agree on where the numbers came from.

Depending on the complexity and size of the data, make decisions to just copy and filter a SQL Veiw, Stored Procedure, or Pass Through Query using T-SQL.

I add about two or more major features per week to update the application version that gives my traders the edge. This is a one development station and MS office on each users PC solution.

The office manager clicks a button once in the morning, and it creates stand-alone Excel reports that are archived on a shared network. These can be retrieved via FTP or e-mailed. It would be easy to automat that on a timer.

This solution is not the solution for everyone.

I also use .NET and Reporting Services when they are the right tool.

http://www.accessui.com/Home/tabid/36/Default.aspx

Take a look at this web site for example (if just for the free tools).

Access can work extremely efficiently with SQL Server, let SQL provide all the horsepower, and only send the results over the network.

All this said:

I would run a job on Excel (Excel VBA Object Model Code) on a server next to SQL server and have it create the Excel workbooks in a shared folder.

We post a lot of SQL on our Excel users forum along with the Excel Object Model VBA code. (and VB and .Net... its all good)