Integrating Excel and Access

Building the Connection in Access

When using Access, decide whether you will use DAO or ADO, and whether you will build the connection string with VBA or create a DSN. There is no one right answer. I prefer to create an XML file that can be opened as an ADO recordset to hold the connection string information and have Access open that file to build the connection. As long as the end user has access to the XML file on their machine, she can use the application. If you use a DSN, you will have to make changes on everyone's PC to use the application. A second convenience is that you can have the XML file on a network drive. This comes in handy if you need to change server information in the future. It also allows you to test the application on the test server and the production server by just changing one line in the XML file.

The easiest way to create the XML file is to create a table in Access with the required information, open up that table with ADO, and save the recordset as XML. Here are the steps to do that—I should also mention that I use multiple rows of data for each part of the connection, rather than multiple columns.

First, go into the Access GUI and create a new table in Design View with two columns. Call the first column ADO_Argument (Text—20 characters) and the second column Argument_Text (Text—50 characters). Next, save the table as tbl_SQLConnection and select No when asked whether you want to create a primary key. Now you are ready to fill in the information. The following ...