7.3. 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 information can be filled in for the connection string:

• Provider

• Data source

• Initial catalog

• Integrated security

You can also enter a username and password, but I do not recommend it unless you use some type of public username for very limited reporting. I suggest having a dialog box pop up asking for the username and password. For this example, we will pass username and password as parameters to the VBA procedure. Also note that if you use integrated security, you do not need to pass a username and password. I show Integrated Security in the XML file, but you should omit that line if you want to use a username and password.

Now, open your table and enter sqloledb for Provider, the name of your SQL Server or the IP address of your SQL Server for Data Source, the database name for Initial Catalog, and SSPI for Integrated Security. If you are building this on a network where everyone can use the server name for Data Source, then using the server name would be fine. However, in most cases, I recommend using the IP address, particularly if you are building this application for use in multiple locations. Once this is entered, the procedure in Example 7-1 will save it as an XML file.

Example 7-1. Sample procedure to save a recordset as XML

 Public Sub MakeXML(  ) Dim adors As ADODB.Recordset Set adors = New ADODB.Recordset adors.Open "tbl_SQLConnection", CurrentProject.Connection adors.Save "C:\Documents and Settings\All Users\" & _            "Documents\SQLConn.XML", adPersistXML adors.Close Set adors = Nothing End Sub

This code puts the file in a folder that should exist on most Windows XP systems. You might notice that the folder shows as "Shared Documents" on the screen, but if you go into the properties of a file in the folder, you see the folder needs to be referred to as "Documents" in a path; this is similar to the "My Documents" folder showing up as "Michael Schmalz's Documents," which you can't refer to in a path. While the code uses a location that should exist on most Windows XP systems, you can select whatever location you would like. Find the XML file and open it up with Notepad, and you see the following if you use ADO 2.8. You may see something a little different if you use prior versions of ADO. However, as long as you use the same version as your other users, it will work on their machines.

     <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'         xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'         xmlns:rs='urn:schemas-microsoft-com:rowset'         xmlns:z='#RowsetSchema'>     <s:Schema id='RowsetSchema'>         <s:ElementType name='row' content='eltOnly'>             <s:AttributeType name='ADO_Argument' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>                 <s:datatype dt:type='string' dt:maxLength='20'/>             </s:AttributeType>             <s:AttributeType name='Argument_Text' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>                 <s:datatype dt:type='string' dt:maxLength='50'/>             </s:AttributeType>             <s:extends type='rs:rowbase'/>         </s:ElementType>     </s:Schema>     <rs:data>         <z:row ADO_Argument='Provider' Argument_Text='sqloledb'/>         <z:row ADO_Argument='Data Source' Argument_Text='MJS_Home'/>         <z:row ADO_Argument='Initial Catalog' Argument_Text='Pubs'/>         <z:row ADO_Argument='Integrated Security' Argument_Text='SSPI'/>     </rs:data>     </xml>

As you can see, this is a much easier way to write the XML file than trying to do it yourself, though it is certainly possible to write it by hand. In many cases, when I use this method with a VB file, I put in many more pieces of information. I use this technique instead of using an .INI file because I find it is much more flexible. It is very easy to edit this file to add more records; copy a row that begins with <z:row and ends with />, paste it on the line below, and change the data inside the single quotes. Again, this method fills in four pieces of information. If you are building an application to run on your computer with no other users, it is probably overkill. However, you never know when you will have additional users, and you can use this same XML file over and over, even among multiple Access and Excel applications that need to access the same SQL Server.

To open the XML file as an ADO recordset, use the Open method of the ADO recordset object and use the path and filename. Example 7-2 shows a procedure that opens up an XML file and puts the information from the file into the Immediate Window.

Example 7-2. Procedure to read an XML file as a recordset

 Public Sub ReadXML(  ) Dim adors As ADODB.Recordset Set adors = New ADODB.Recordset adors.Open "C:\Documents and Settings\All Users\" & _            "Documents\SQLConn.XML" adors.MoveFirst While Not adors.EOF   Debug.Print adors.Fields(0).Value & " - " & _               adors.Fields(1).Value   adors.MoveNext Wend adors.Close End Sub

Now that you understand how to open the XML file, Example 7-3 shows a procedure to open the recordset by pulling the data from SQL Server and pushing that data into a new Excel workbook. To use this, you need a reference to the same version of ADO that you used to build the XML file, as well as a reference to Microsoft Excel. You do this by going to Tools References in the Design Mode of the module. Note that in this procedure, Excel stays open; you could also set it up to save the Workbook using the SaveAs method of the Workbook object. If you do that, also use the Quit method of the Excel Application object to close Excel.

Example 7-3. Push data from SQL Server to Excel

 Public Sub OpenSQLWriteExcel(UserName As String, Password As String) Dim adocn As ADODB.Connection Dim adoconnrs As ADODB.Recordset Dim adors As ADODB.Recordset Dim adofld As ADODB.Field Dim ConnString As String Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim x As Integer Set adoconnrs = New ADODB.Recordset adoconnrs.Open "C:\Documents and Settings\All Users\" & _                "Documents\SQLConn.XML" adoconnrs.MoveFirst While Not adoconnrs.EOF   ConnString = ConnString & _       adoconnrs.Fields(0).Value & " = '" & adoconnrs.Fields(1).Value & "';"   adoconnrs.MoveNext Wend adoconnrs.Close ConnString = ConnString & "User ID = '" & UserName & "';" ConnString = ConnString & "Password = '" & Password & "';" Set adocn = New ADODB.Connection adocn.ConnectionString = ConnString adocn.Open Set adors = New ADODB.Recordset adors.Open "pubs.dbo.Authors", adocn adors.MoveFirst Set xlapp = New Excel.Application xlapp.Visible = True Set xlwb = xlapp.Workbooks.Add Set xlws = xlwb.ActiveSheet x = 1 For Each adofld In adors.Fields   xlws.Cells(1, x).Value = adofld.Name   x = x + 1 Next adofld Set xlrng = xlws.Range("A2") xlrng.CopyFromRecordset adors xlws.Columns.AutoFit adors.Close adocn.Close Set xlrng = Nothing Set xlws = Nothing Set xlwb = Nothing Set xlapp = Nothing Set adoconnrs = Nothing Set adocn = Nothing Set adors = Nothing End Sub

This is a lot of code to open up a recordset that is doing very little. The code is cut by quite a bit if you open the authors table as a linked table, but the time you lose in writing this procedure will be saved many times over if you have to make changes to the connection information or if you use this application on multiple machines.

Once you have the data in Excel, automate Excel to put in subtotals or make a pivot table out of the data. The authors table of the Pubs database doesn't really lend itself to that type of analysis, but you can see how to get the information. Also note that Access is not involved at all in this code other than being the automation engine. It makes sense to do things like this in Access because you can build a nice frontend that looks very similar to a VB application. An Access frontend that builds many reports in Excel will be much easier for your end users than navigating multiple Excel documents to find their file. Also, in many cases you will do some additional automation and possibly even link to other data in the Access database. For this very simplistic example, the code will be virtually the same in Excel.