Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from Integrating Excel and Access

Chapter 7. Leveraging SQL Server Data with Microsoft Office

While the primary focus of this book is on using Access and Excel together to enable automation of reporting and analysis, it is difficult to ignore enterprise databases since in many cases they are the source data in a corporate setting. You don't need to know all about SQL Server to use it with Excel and Access. While you can read a SQL Server book if you need detail, this chapter introduces you to accessing data with tables, views, and stored procedures , accessing data through ADO, using temporary tables, and pushing and pulling data from SQL Server. As an additional topic, if you are familiar with Access, you know that crosstab queries are a very popular way to summarize data, so you may be surprised to find out that in SQL Server you cannot use them. I show how to build a stored procedure to get around this limitation, potentially simplifying your analysis of the data in Excel or Access substantially. Finally, I also cover using DTS to automate report production on a schedule.

Tip

Stored procedures are similar to procedures that you create in any programming language. They may take input parameters, execute code, return values, or call other procedures. You gain a performance advantage when using a stored procedure versus running a query with parameters. In addition, using a stored procedure reduces network traffic, since a single statement can call a stored procedure instead of sending multiple (sometimes hundreds ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page