Excel VBA Macro Programming

You can make your code interact with any database as long as the database is ODBC (Open DataBase Connectivity) compatible and the appropriate driver is available. ODBC allows applications to access the database in a standard way from many different languages, including VBA. This can be extremely useful if you want to bring data from a database into your spreadsheet. For example, you may want to import data from an accounting system into your application, such as when the accounting system doesn't necessarily provide functionality in the application, but the database has the data you are interested in. If the database has an ODBC driver available, you can bring the data into your VBA application in any shape or form to use as needed.

Databases such as Microsoft Access, Microsoft SQL Server, and Oracle all support ODBC and, provided you have the relevant permissions to the database, you can read data into your spreadsheet and even write data back if need be. Writing data back should be done with extreme care, however, because you can easily destroy the integrity of a relational database in this way.

ODBC Links

You need to first set up an ODBC link to point to the database that you want to work with. You do this by selecting Control Panel in Windows, then Administrative Tools, and then Data Sources (ODBC). You will see the ODBC Data Source Administrator dialog, as shown in Figure 16-1.

Figure 16-1: Setting up a data source name

If you do not already have a data source name (DSN) set up for the database, click the Add button. This will give you a list of available database drivers, as shown in Figure 16-2. A DSN is exactly what it says: it is a name of an ODBC link to a database that you can use to refer to that ODBC link. There is a wide variety of these, but I am going to concentrate on Microsoft Access because it is an integral part of Microsoft Office, and if you have Excel, you will most likely have Access.

Figure 16-2: Selecting an ODBC driver for the data source

Double-click the Access driver at the top of the list, and you will be taken into the screen in Figure 16-3. Enter the Data Source Name as NWind and the Description as Northwind Database . These two items are merely for identification purposes. This gives a name (DSN) that you can use in your code and a description that states what it is about. If you prefer, the Description field can be left blank because it only identifies what the DSN is about in the DSN screen; it is not used in your code at all.

Figure 16-3: Final steps for setting up a DSN

Click the Select button and select the NorthWind database. This is a sample database (.MDB file) supplied with Microsoft Access and should be among the Access files. If you cannot find it, locate it by using Search and the Files and Folders option in Windows. Click OK, and you have now created an ODBC link (DSN) to the database with the name of NWind. Click OK again and close the Control Panel.

You can set up ODBC links to other databases on servers such as SQL or Oracle in the same way. However, you need to know the name of the server and the name of the database, and you also need an ID and password that give at least Select Access. You may also need other settings depending on which ODBC driver is used.