Free ebook]Integrating Excel and Access - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Saturday, November 30, 2019

Free ebook]Integrating Excel and Access

[Free ebook]Integrating Excel and Access

1st Edition
ISBN-13: 978-0596009731, ISBN-10: 0596009739

DOWNLOAD

Description

Product description

In a corporate setting, the Microsoft Office Suite is an invaluable set of applications. One of Offices' biggest advantages is that its applications can work together to share information, produce reports, and so on. The problem is, there isn't much documentation on their cross-usage. Until now.
Introducing Integrating Excel and Access, the unique reference that shows you how to combine the strengths of Microsoft Excel with those of Microsoft Access. In particular, the book explains how the powerful analysis tools of Excel can work in concert with the structured storage and more powerful querying of Access. The results that these two applications can produce together are virtually impossible to achieve with one program separately.
But the book isn't just limited to Excel and Access. There's also a chapter on SQL Server, as well as one dedicated to integrating with other Microsoft Office applications. In no time, you'll discover how to:
  • Utilize the built in features of Access and Excel to access data
  • Use VBA within Access or Excel to access data
  • Build connection strings using ADO and DAO
  • Automate Excel reports including formatting, functions, and page setup
  • Write complex functions and queries with VBA
  • Write simple and advanced queries with the Access GUI
  • Produce pivot tables and charts with your data
With Integrating Excel and Access, you can crunch and visualize data like never before. It's the ideal guide for anyone who uses Microsoft Office to handle data.

Synopsis

In a corporate setting, the Microsoft Office Suite is an invaluable set of applications. One of Offices' biggest advantages is that its applications can work together to share information, produce reports, and so on. The problem is, there isn't much documentation on their cross-usage. Until now. Introducing "Integrating Excel and Access", the unique reference, shows you how to combine the strengths of Microsoft Excel with those of Microsoft Access. In particular, the book explains how the powerful analysis tools of Excel can work in concert with the structured storage and more powerful querying of Access. The results that these two applications can produce together are virtually impossible to achieve with one program separately. But the book isn't just limited to Excel and Access. There's also a chapter on SQL Server, as well as one dedicated to integrating with other Microsoft Office applications.
In no time, you'll discover how to: utilize the built in features of Access and Excel to access data; use VBA within Access or Excel to access data; build connection strings using ADO and DAO; automate Excel reports including formatting, functions, and page setup; write complex functions and queries with VBA; write simple and advanced queries with the Access GUI; and produce pivot tables and charts with your data With "Integrating Excel and Access", you can crunch and visualize data like never before. It's the ideal guide for anyone, who uses Microsoft Office to handle data.

From the Publisher

This unique reference shows you how to combine the powerful analysis tools of Excel with the structured storage and more powerful querying of Access. It covers everything you need to know, including interfaces, object models, and code. Learn how to crunch and visualize data like never before. Perfect for all Microsoft Office users

About the Author

Michael Schmalz works in financial services and performs business and technology consulting in a variety of industries. He has done technical editing for O'Reilly on several Microsoft Office books and authored "Integrating Excel and Access" and "C# Database Basics". Michael has a degree in Finance from Penn State. He lives with his wife and children in Pennsylvania.

Excerpt. © Reprinted by permission. All rights reserved.

CHAPTER 3 Data Access from Excel VBA
Up to this point, all interaction with the data source has been through the Excel user interface. I hope that you have seen how effective it can be in certain situations.You have also used VBA to enhance data access from the user interface.VBA gives you the opportunity to take more control of the data;it performs queries that are not possible from the user interface, and you can now automate many operations.
There are numerous situations when VBA is not only the preferred way to obtain data, but the only way.For example,you may run into situations in which you want to control access to the data by the time of day or another variable. Giving the user the ability to pull the data straight from the user interface limits your ability to restrict access to that data.You do not want people running queries that join a table with several hundred thousand customer records and a couple million financial transactions during the middle of the day.At the same time, though, you also cannot shut down the database to block the users ’queries when the database needs to be available to perform official transactions. When a developer runs into a situation like this, an easy solution is to write code to pull data from the database while ensuring that the moment the query runs doesn ’t interfere with performance. The developer can then protect the code so that this restriction cannot be bypassed.
Another situation when it is preferable to use VBA to pull the data is when you need to perform actions based on information at the row level.While you could certainly pull the data in through the user interface and then write code to perform the actions within Excel on another worksheet,that method would result in duplication of data. When I am writing reports where I need to perform calculations at several subtotals, calculate ratios,and/or weighted averages,I control all of that through VBA.The other issue that will become more relevant as you move through this book is the choice between using Excel to pull the data from the data source and using the data source (Access)to automate Excel.This choice makes a large impact on application design.
Another consideration when using Excel to pull the data is macro security.At some organizations where Excel is not used heavily,or at least VBA is not used heavily, Excel ’s security will not enable macros that are not signed (high)or that are not installed in trusted locations (very high).This is a great precaution to avoid getting infested with macro viruses,but it makes it difficult to create a home-grown application for use in Excel.You can find out how your security is configured by going to Tools -Macros -Security from the Excel user interface (Figure 3-1).If your security is set at low,I suggest moving it to medium.If it is set to high or very high,I would change it to medium or speak with your system administrator about moving it to medium.At medium,you can choose whether or not to run macros each time you open up an Excel workbook.If you are unable to persuade your system administrator to allow you to change the setting,you will have to perform the automation from
Access.
While this book focuses on integrating Excel and Access,most of the content in this chapter can be applied to other enterprise data sources such as SQL Server,DB2,and Oracle.For the purposes of this book,I will stick with Access and occasionally SQL Server.
Writing a Reusable Module for Data Access
While some people argue that writing a reusable module creates some additional overhead, this is a good way to get introduced to pulling data using VBA.I suggest building a module that returns a database connection when you pass it certain parameters. A module holds procedures and functions and can be saved as a text file, which makes it very easy to use in other applications.While it is certainly easy enough to write the code to access a data source each time you need it,having a module written that you know works can reduce the variables when building an application. Even if you do not use it in production,you can always copy the code from the module to use in future applications.I use this same concept of writing a generic procedure to perform actions like dropping tables,running action queries, and other database tasks that will be done the same way on multiple objects. This way,I only have to write the code once.
The key to using this method is passing the parameters by reference (with ByRef ). When you pass parameters to a procedure,you can either pass the value of the parameter or pass a reference to the variable.When you pass the value,the procedure gets the value and cannot change the original value for the calling procedure (provided that it is not a global variable).When you pass the variable ByRef ,any action done on that variable in the called procedure is done on that variable in the calling procedure.
For example,let ’s assume that you have an integer variable x ,and you want to pass it to a procedure to perform some math function on it.Then you want to use the result,and you do not need to know the original value later.You could write a function called DoMath and pass it your variable.You would create a variable in your original procedure to hold the value of the function that is returned.The other method is to create a procedure called DoMath but pass the variable x as ByRef .Then when you change the value of x in that procedure,the value of x is also updated in the original procedure.While that is a simplistic example,I hope it explains how this can be useful. In the project in the final chapter,you will see an example of how both of these concepts work in a sample application.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment