Connecting to a Database with VBA

Document Sample
Connecting to a Database with VBA Powered By Docstoc
					                                                                                                          1


Technical Article

Connecting to a Database
with VBA
 VBA projects will often need to connect to a back-end database. The Developer will have to
 create a database connection, connect to the database, execute statements that return and
 modify data, and process any returned data into a form of output. They may need to create,
 update and delete tables, and add users and/or privileges. The Developer will also need to
 shutdown the database connection and handle any errors caused by the connection. In this
 article, Anupam Banerji explains the database objects provided by VBA.

An Excel™ spreadsheet may need to connect               implemented in your VBA project.
to a database to read, write or analyze large
datasets. Databases are a useful storage                Connecting to a Data Source
system; they offer advantages over other
methods of storing datasets when storing,               The ADO model uses the Connection object
processing and retrieving data. Databases               to link to a data source. The Connection
may be manipulated by coded statements                  object opens a connection string to the
known as Structured Query Language, or                  database.   The code below shows how a
SQL. SQL is a powerful language that allows             Connection object is instanced and connects
the user the tools to create, manage and                to a SQL Server™ 2008 database:
remove database objects, such as schemas,
tables, indices, triggers, users and roles.             Dim conn As ADODB.Connection

                                                        set conn = new ADODB.Connection
Excel™ 2007 VBA uses the ActiveX Data
Objects (ADO) model as the standard object              conn.ConnectionString = "Data" & _
model to access a database and execute SQL                     "Source=<myServerAddress>;" & _
commands. The user can form connection                         "Initial Catalog=<myDataBase>;" & _
strings, create and execute commands                           "User Id=<myUsername>;" & _
                                                               "Password=<myPassword>;"
through recordsets and can even use SQL
statements to create and delete tables, or to           conn.Open
alter them.
                                                        The connection is then used by the other
Referencing the ADO model                               objects in ADO to manipulate records in the
                                                        database. The connection can also execute
To add ADO to the Excel™/VBA project:                   SQL statements if the VBA code doesn’t need
                                                        returned data.
1. Click on Tools → References.
2. Add the “Microsoft ActiveX Data Objects              Use the Close statement              to   close       the
   6.0 Library” reference to your VBA                   database connection.
   project.

The   ADO     object    model     may     now     be

   P.O. Box 715, Rose Bay NSW, Australia • http://www.coactumsolutions.com • inquiries@coactumsolutions.com
                                                                                                         2


Technical Article

Connecting to a Database
with VBA
Manipulating Data                                       Design Issues

The Recordset object is used to manipulate              When using ADO objects, the Developer
data. The Recordset uses the Parameters                 needs to understand that the ADO 6.0 Object
object to add arguments from the VBA                    Model will not roll back transactions.     A
interface to the SQL statement.           The           rollback is similar to an undo operation in
Recordset is then executed and returns a                your Office products.     However, rollback
set of rows and fields. If there are no rows,           operations are a bit more complicated;
then the Recordset is empty.                            rollbacks can only occur if the underlying
                                                        data written by the VBA object has not been
Using the Close statement with the                      processed further by a different object. The
recordset will close the connection object the          Developer should implement data checks and
recordset was created with.                             confirm whether the data should be
                                                        committed before executing any SQL queries
Using Commands                                          or updating any recordsets.

This is a safer way to manipulate data.                 I used to work with DAO before ADO was
Changes to the command will not affect the              accepted by companies as the standard.
underlying Recordset until the command is               Data Access Objects (DAO) is an older
                                                        Microsoft object model which allows the user
executed.    A command holds an SQL
                                                        to create and implement data manipulation
statement and a parameter set.         The
                                                        on a transaction level. The user can then
Execute method returns a Recordset of
                                                        commit and rollback transactions with several
values.
                                                        commands.      ADO 6.0 doesn’t have this
                                                        model. I’m not sure if Microsoft made the
Error Handling
                                                        right choice in introducing the ADO model
                                                        without a formal rollback structure.     This
Database errors are retrieved from the
Connection object. The Errors property will
contain information on the connection error.              Quick Tip:
For a SQL Server™ database, this is a large               ADO and DAO may have the same objects,
negative error number, indicating that the                but they work differently. DAO uses the
error has been thrown at the Server. The                  database connection differently to ADO,
user will have to check the database error                which is more efficient in its design and
numbers for details on what the error is.                 allows the Developer the choice of where
Unfortunately, VBA simply shows a generic                 to perform SQL operations and how to
error message.                                            manipulate data. DAO is more rigid in
                                                          design, but allows commits and rollbacks.

                                                        makes it difficult for users to undo changes

   P.O. Box 715, Rose Bay NSW, Australia • http://www.coactumsolutions.com • inquiries@coactumsolutions.com
                                                                                                         3


Technical Article

Connecting to a Database
with VBA
after they’ve been committed.
                                                             While (Not r.EOF)
                                                                 ' Output data
A Quick Example
                                                                 r.MoveNext
This example forms a command, executes                       Loop
the command to return a recordset, and
outputs recordset data.                                 ADO objects are a simple and efficient way to
                                                        connect to databases from Excel™/VBA. The
We open a connection as discussed. We                   VBA Developer should be aware of the
then  create a   Command   object  with                 inability of the ADO recordset to roll back
parameters:                                             transactions once the recordset has been
                                                        updated. The database objects and security
    Dim conn As ADODB.Connection                        can also be administered through VBA
    Dim cd As ADODB.Command                             through the use of the database specific SQL.
    Dim r As Recordset
    Dim ps As ADODB.Parameters

    Set cd = New ADODB.Command

    cd.ActiveConnection = <Connection>                  Anupam Banerji is the Director of Coactum
                                                        Solutions, and has a wealth of experience
    cd.CommandText = <SQL query>                        writing, evaluating and validating systems
    Set ps = cd.Parameters                              for the cement, pharmaceuticals, energy
                                                        derivatives   and   banking   and   finance
    ' Auto-Refresh the parameter set                    industries.
    ps.Refresh
                                                        Additionally, he’s designed and written
    ' Enter Parameter Values
    ps.Item(0).Value = <Parameter Value>                software solutions in VB/VBA, SQL and
    ps.Item(1).Value = <Parameter Value>                C# .NET, and has worked as a quantitative
                                                        analyst in banking.
Execute the command             and    return    the
resulting recordset:                                    Anupam holds a Masters degree in Process
                                                        Engineering and a Bachelors in Chemical
    r = cd.Execute                                      Engineering.

Move to the first record:

    r.MoveFirst

Loop through the recordset and output data.

   P.O. Box 715, Rose Bay NSW, Australia • http://www.coactumsolutions.com • inquiries@coactumsolutions.com