3.1. Writing a Reusable Module for Data Access

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 variable ByRef, 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 variable x, 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 called DoMath and 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 called DoMath but pass the variable x as ByRef. Then when you change the value of x in that procedure, the value of x is 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, called GetCn , 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 the Connection and Recordset objects because the connection and recordset variables are passed ByRef.

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 the GetCn procedure. 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 called CopyFromRecordset . 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.