Data Access from Excel VBA

Up to this point, all interaction with the data source has been through the Excel user interface. I hope that you have seen how effective it can be in certain situations. You have also used VBA to enhance data access from the user interface. VBA gives you the opportunity to take more control of the data; it performs queries that are not possible from the user interface, and you can now automate many operations.

There are numerous situations when VBA is not only the preferred way to obtain data, but the only way. For example, you may run into situations in which you want to control access to the data by the time of day or another variable. Giving the user the ability to pull the data straight from the user interface limits your ability to restrict access to that data. You do not want people running queries that join a table with several hundred thousand customer records and a couple million financial transactions during the middle of the day. At the same time, though, you also cannot shut down the database to block the users’ queries when the database needs to be available to perform official transactions. When a developer runs into a situation like this, an easy solution is to write code to pull data from the database while ensuring that the moment the query runs doesn’t interfere with performance. The developer can then protect the code so that this restriction cannot be bypassed.

Another situation when it is preferable to use VBA to pull the data is when you need to perform actions based on information at the row level. While you could certainly pull the data in through the user interface and then write code to perform the actions within Excel on another worksheet, that method would result in duplication of data. When I am writing reports where I need to perform calculations at several subtotals, calculate ratios, and/or weighted averages, I control all of that through VBA. The other issue that will become more relevant as you move through this book is the choice between using Excel to pull the data from the data source and using the data source (Access) to automate Excel. This choice makes a large impact on application design.

Another consideration when using Excel to pull the data is macro security. At some organizations where Excel is not used heavily, or at least VBA is not used heavily, Excel’s security will not enable macros that are not signed (high) or that are not installed in trusted locations (very high). This is a great precaution to avoid getting infested with macro viruses, but it makes it difficult to create a home-grown application for use in Excel. You can find out how your security is configured by going to Tools -> Macros -> Security from the Excel user interface (Figure 3-1). If your security is set at low, I suggest moving it to medium. If it is set to high or very high, I would change it to medium or speak with your system administrator about moving it to medium. At medium, you can choose whether or not to run macros each time you open up an Excel workbook. If you are unable to persuade your system administrator to allow you to change the setting, you will have to perform the automation from Access.

Figure 3-1.  The Excel macro security dialog box, which tells Excel how to handle workbooks with macros

While this book focuses on integrating Excel and Access, most of the content in this chapter can be applied to other enterprise data sources such as SQL Server, DB2, and Oracle. For the purposes of this book, I will stick with Access and occasionally SQL Server.

While some people argue that writing a reusable module creates some additional overhead, this is a good way to get introduced to pulling data using VBA. I suggest building a module that returns a database connection when you pass it certain parameters. A module holds procedures and functions and can be saved as a text file, which makes it very easy to use in other applications. While it is certainly easy enough to write the code to access a data source each time you need it, having a module written that you know works can reduce the variables when building an application. Even if you do not use it in production, you can always copy the code from the module to use in future applications. I use this same concept of writing a generic procedure to perform actions like dropping tables, running action queries, and other database tasks that will be done the same way on multiple objects. This way, I only have to write the code once.

The key to using this method is passing the parameters by reference (with ByRef). When you pass parameters to a procedure, you can either pass the value of the parameter or pass a reference to the variable. When you pass the value, the procedure gets the value and cannot change the original value for the calling procedure (provided that it is not a global variable). When you pass the variableByRef, any action done on that variable in the called procedure is done on that variable in the calling procedure.

For example, let’s assume that you have an integer variablex, and you want to pass it to a procedure to perform some math function on it. Then you want to use the result, and you do not need to know the original value later. You could write a function calledDoMathand pass it your variable. You would create a variable in your original procedure to hold the value of the function that is returned. The other method is to create a procedure calledDoMathbut pass the variablexasByRef. Then when you change the value ofxin that procedure, the value ofxis also updated in the original procedure. While that is a simplistic example, I hope it explains how this can be useful. In the project in the final chapter, you will see an example of how both of these concepts work in a sample application.

Example 3-1 uses ActiveX Data Objects (ADO), but you could also write it using Data Access Objects (DAO). When you decide which one you want to use, you must set a reference to either ADO, ADOX, or DAO by going into the Visual Basic Editor and pressing Alt + F11 from the Excel User Interface. Then you go to Tools -> References and select the appropriate reference.

The procedure in Example 3-1, calledGetCn, creates a new data connection with ADO and opens a recordset with the specified SQL. Once this procedure finishes running, the calling procedure can reuse theConnectionandRecordsetobjects because the connection and recordset variables are passedByRef.

Example 3-1. ADO connection

Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _
   sqlstr As String, dbfile As String, usernm As String, pword As String)

  Set dbcon = New ADODB.Connection
  dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
              usernm, pword
  Set dbrs = New ADODB.Recordset
  dbrs.Open sqlstr, dbcon

End Sub

In any procedure where you need to create a connection to an Access database and return a recordset, you can call this procedure. In order to use it, declare variables for the ADO connection and recordset to be passed to theGetCnprocedure. You can either use variables for the SQL string and the filename or simply pass them as strings. The same thing applies to the username and password. If your Access Database is not password-protected, you can pass an empty string (" ") for the username and password.

Example 3-2 shows a procedure that returns all of the records from a table called Table1 in a database called sampledb.mdb. This example also introduces a method of the ExcelRange calledCopyFromRecordset. This method places all of the records from the recordset in the Excel worksheet.

Example 3-2. Introducing CopyFromRecordset

Public Sub getrs()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

sql = "Select * from Table1"
filenm = "C:\Data\sampledb.mdb"

Call GetCn(adoconn, adors, sql, filenm, "", "")

Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1")
xlsht.Range("A1").CopyFromRecordset adors

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = nothing
End Sub

Keep in mind that anytime before you close the ADO connection, you can use it to open additional recordsets or perform any other functions. Again, this procedure is really only for demonstration purposes, but you could certainly use it if you had multiple features that needed to obtain recordsets.

In Chapter 1, I gave a brief description of ADO (including ADOX) and DAO. In this chapter, the biggest differences between the two methods of accessing data come with the use of queries. When you develop an Access Database, you will often have many different types of queries. For example, you might have one query that simply retrieves data (Select Query) or one that appends data from one table into another (Append Query).

When you are dealing with DAO, each type of query is a QueryDef object. Once you have a DAO database connection, you can simply cycle through theQueryDefscollection with aFor Each...Nextloop and get information about each query; you can then make changes to the queries, if necessary. TheQueryDefscollection contains every query in the Access database. When using DAO, you do not need to know what type of query you are dealing with to take actions on it.

This is not as easy a task when using ADO and ADOX. ADOX categorizes queries similarly to the way SQL Server does. Specifically, ADOX considers action queries (Append Queries, Make Table Queries, etc.) to be procedures and select queries to be views. To loop through all of the queries in an Access Database using ADOX, you would cycle through theProceduresandViewscollections. The other major difference between ADOX and DAO is that in ADOX, the Views are also part of theTables collection, while in DAO they are not. The collection of tables in DAO is called theTableDefs collection. A single table is aTableDef.

The difference between accessing a select query as a view and as a table is that if you access a query in theViewscollection, you have access to the Command and CommandText. This is what gives you the SQL of the View. When you access a View as a Table, you do not have access to the Command or CommandText.

I have included two samples below that show you how to loop through the queries and tables in an Access Database. Example 3-3 uses ADO, while Example 3-4 uses DAO. When I need to make changes to queries, I tend to use DAO when working with Access, and ADOX when working with SQL Server. The reason why I tend to use DAO when working with Access queries is that on many occasions when I am working with queries and I am writing SQL on the fly, the query may change from a select query to an action query. I find it is easier to just use theQueryDefs collection, as opposed to determining whether the query is now in theViewsorProcedurescollection. But, as you will see below, you are not limited to doing that. Also note that in the ADO example I am using theGetCn procedure from above. I am just ignoring the recordset that is returned.

Example 3-3. Using ADO and ADOX to loop through queries and tables

Public Sub getinfo()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

Dim adocat As adox.Catalog
Dim adovw As adox.View
Dim adozz As adox.Procedure
Dim adotbl As adox.Table

Dim x As Integer
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1")

sql = "Select * from Table1"
filenm = "C:\Data\sampledb.mdb"

Call GetCn(adoconn, adors, sql, filenm, "", "")

Set adocat = New adox.Catalog adocat.ActiveConnection = adoconn
x = 2
xlsht.Cells(1, 1).Value = "Views"
For Each adovw In adocat.Views
  xlsht.Cells(x, 1).Value = adovw.Name
  x = x + 1
Next
x = 2
xlsht.Cells(1, 2).Value = "Procedures"
For Each adozz In adocat.Procedures
  xlsht.Cells(x, 2).Value = adozz.Name
  x = x + 1
Next
x = 2
xlsht.Cells(1, 3).Value = "Tables"
For Each adotbl In adocat.Tables
  xlsht.Cells(x, 3).Value = adotbl.Name
  x = x + 1
Next

Set adocat = Nothing
Set adozz = Nothing
Set adovw = Nothing
Set adotbl = Nothing
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

Example 3-4. Using DAO to loop through queries and tables

Public Sub getDAOinfo()
Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim tbl As DAO.TableDef
Dim x As Integer
Dim xlsht As Excel.Worksheet

Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "")

Set db = wrk.OpenDatabase("C:\Data\sampledb.mdb")

Set xlsht = Sheets("Sheet2")

xlsht.Cells(1, 1).Value = "Queries" xlsht.Cells(1, 2).Value = "Query Type"
x = 2
For Each qry In db.QueryDefs

  xlsht.Cells(x, 1).Value = qry.Name
  xlsht.Cells(x, 2).Value = qry.Type
  x = x + 1
Next
xlsht.Cells(1, 3).Value = "Tables"
x = 2
For Each tbl In db.TableDefs
  xlsht.Cells(x, 3).Value = tbl.Name
  x = x + 1
Next
Set tbl = Nothing
Set qry = Nothing
db.Close
Set db = Nothing
wrk.Close
Set wrk = Nothing
Set xlsht = Nothing

End Sub

In the DAO example, I place this information into Sheet2. This lets us compare the results of the DAO and ADO/ADOX methods. I also added the query type to the DAO method. This comes across as an integer. To find out what the values are, go to the Immediate Window on the Visual Basic Editor and type?<constant  to determine the values, or go to the object browser and look at theQueryDefTypeEnum collection; the object browser will show you the value if you click on a member of the collection. I have included a table of the values in Table 3-1.

The type of query is useful if you expect a query to return records or make a table. For records, check the query type to ensure that it returns records. Likewise, to make a table, make sure that the query type is 80.

While it is unlikely that you would use either of the examples above for anything other than potentially documenting a database that you are unfamiliar with, I think that these examples are a good introduction to ADO and DAO and how you can connect to databases using each one. In addition to connecting to Access databases, you can connect to enterprise databases, such as SQL Server or Oracle, using these methods. Since DAO was designed specifically for Jet databases, Chapter 7 will only cover connecting to SQL Server with ADO. However, if you feel more comfortable with DAO, you can connect to an SQL Server database using DAO. When dealing with remote data sources like SQL Server, DAO uses more resources than ADO and therefore runs slower. For this reason, it’s frequently better to use ADO with SQL Server.

There are plenty of examples in the help file that comes with DAO to show you how to connect to data sources other than Access. One other thing to keep in mind is that you can use both ADO and DAO in the same application. You will notice when you read over the code above that when I referred to a recordset, I called it anADODB.RECORDSET. If you only had a reference to ADO, you could simply refer to it as aRECORDSET. However, if you decided later that you needed the functionality in DAO for a specific item, you would have to go to all of your recordset references and change them toADODB.RECORDSET. So, my recommendation is to always declare your variables withADODB.ObjectType  orDAO.ObjectType. I even do that with Excel items that I’m unlikely to use in any other applications (worksheets, workbooks, etc. as Excel.Worksheet, Excel.Workbook, etc.).

When pulling data from a data source into Excel, you need to decide how you want to process the recordset. For example, if you need all of the fields and records, you have already been introduced to a simple method that does this: the CopyFromRecordset method places the results of a recordset for Excel Range objects in the range in an Excel worksheet. Sometimes, however, you bring in the results of a query but want only a few of the fields. You can accomplish this by looping through the records.

When dealing with ADO and DAO recordsets, there are several methods of record navigation. In most examples, I go to the first record in the recordset and move through the recordset until there are no additional records. You accomplish this by using theMOVEFIRSTmethod of a recordset to go to the first record, and then theMOVENEXTmethod to move through the recordset. There are several strategies to figure out when you have cycled through all of the records. When you move past the last record, the recordset’sEOFflag is set toTRUE. I suggest using aWhile...Wendloop. Assuming your recordset is a variable namedrs, you write theWhile...Wendloop like this:

  rs.movefirst
  While Not rs.eof
    <Code to work with your Recordset>
    rs.movenext
  Wend

Since you have already been introduced toCopyFromRecordSet, let’s take a look at an example of where you would want to loop through a recordset. Consider a table of employee information that has 15 fields. Assuming that you need only three of the fields (Last Name, First Name, and Salary), you have two choices for how to accomplish this.

The first method is to simply write your query to pull in only the fields that you want using the criteria that you want. This sounds easy, but in some cases it might be difficult if you are not familiar with how to write SQL and if you do not have the appropriate access rights to make changes to the database. In that case, you would need to find out the field names, loop through the records, and test for criteria, if necessary. You could also pull the records by position.

For this example, assume that the fields that you want are called LName, FName, and EmpSalary and that you already have a variable,rs, which is the recordset object; a variable,x, which is an integer; and a variable,xlws, which is an Excel worksheet. You want those fields for people who have a salary of more than $60,000. This example is the same for both ADO and DAO.

  x =1
  rs.movefirst
  While Not rs.eof
    If rs.fields("EmpSalary").value > 60000 then
    xlws.cells(x,1).value = rs.fields("LName").value
    xlws.cells(x,2).value = rs.fields("FName").value
    xlws.cells(x,3).value = rs.fields("EmpSalary").value
    x = x + 1
    End if
    rs.movenext
  Wend

The most important part of this Example is that you have thex = x + 1inside yourIfstatement. If not, you bring in only the records that you want, but you have blank spaces between the records. If you place thex = x + 1outside theIfstatement, each time a record is evaluated,xis incremented by one. In this procedure, the variablexdetermines the row to place the data. After looking at this example, you probably wonder how you get the titles of the fields on the Excel worksheet. When you pull the values by field name, this is very easy to do, either by simply writing the title that you want directly or by accessing the.NAMEproperty of the field object. However, when you perform aCopyFromRecordset, you may not know all of the fields.

Fortunately, there is a very easy method to cycle through the fields. Again, assume that you have a recordset (rs), you also have a variable calledfldthat is defined as a Field (same for ADO and DAO—ADODB.FieldorDAO.Field), and finally you have your Excel worksheet defined asxlws. Let’s put the titles in the first row and paste the recordset starting in row 2. Here is the code:

  x = 1
  For each fld in rs.Fields
    xlws.cells(1,x).value = fld.name
    x = x + 1
  Next
  xlws.range("A2").CopyFromRecordset rs

In general,CopyFromRecordset is quicker than cycling through the records; however, you do give up some control. If you do not see any benefits to moving through the records one at a time, I suggest writing your query to pull the records and fields that you need and then usingCopyFromRecordset. You may also have a situation when you want to use the same recordset for multiple purposes, and moving through the records is your best bet. In the previous example, for instance, we wanted to pull only the records where the salary was greater than $60,000. If you wanted to, you could use that same recordset to look at salaries less than $60,000 and place them in another location.

Please check back next week for the conclusion of this article.