Integrating Excel and Access

Creating a Connection Using VBA

Now that you understand a little about using the Access GUI to access data on SQL Server, I will show you how to do the same thing with VBA. In some cases it is easier to create a pass-through query with the Access GUI and then open the query with VBA. If you need to change the query, you can just change the query's SQL string with VBA. Also, in most cases, I recommend using ADO, rather than DAO, to access SQL Server. And finally, when you do development work with SQL Server, it is always best to use a test database before trying things on a production database.

There are some very good reasons to have a test database for your SQL Server production database. With Access, you can make a copy of a database to test something and scrap the copy if your code doesn't work or causes problems. When you are dealing with SQL Server, however, the process is not as simple, and you will most likely be contending with multiple users. Running your code on a test database reduces potential issues. It is even useful if you are just running queries, as you don't want to run a query that takes a long time to run during the middle of the day with 50 users on the database. Often, you won't know how long something takes to run until you test it.

Let's say that you have data in SQL Server that you need to put into an Excel report. Three viable alternatives are using:

• Access and VBA to pull the data and automate Excel

• Excel VBA to pull the data directly into Excel

• ActiveX script ...