7.2. 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 in Data Transformation Services (DTS) on SQL Server to build the report

I show the VBA options in this section and the DTS option in the next section. I do not get into the actual building of the report, simply the movement of data. Once you have the data in Excel, you can use the same techniques used throughout the rest of the book to automate Excel.