Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page