Data Access from Excel VBA - Writing a Reusable Module for Data Access (Page 2 of 4 )
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.
Next: Choosing Between ADO and DAO >>
More Database Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
