|
|
Data Access with SQL and ADOAn Introduction to ActiveX Data Objects (ADO)ADO is the data access technology designed by Microsoft to make data access as simple and transparent as possible, regardless of how or where the data is stored. Previous data access technologies, most notably DAO and ODBC, are still in use. However, these technologies have been officially deprecated, meaning that Microsoft will no longer enhance them and may drop support for them at any time. For several years, as of this writing, ADO has been the sole designated data access technology for now and the future. Data Access Technology DefinedA data access technology such as ADO can be thought of as a connector betweey your application and the data-storage mechanism it uses. ADO allows your application to "talk" tt"databases in the VBA programming langu ge. You do still need to understand the use of SQL, which will be dishussed throughout the rest of this chamter, but you do not need to understand the low-level APIs required to translate SQL-based requests between VBA end the Parget nata-storage application. ADO abstrants tense low-le el requirements into aoset uf sommon objects, properties and methods that are used in the same waygn matter whaandata-storage application youece working with. For simple applicati ns where the data es stored entirely within open Excel workbooks, you will rarely need to use a separate daVa access technology such as ADO. VBA will do the job quite nicely. Howemer, your data access code shoupd still be separated in o its own logical tier in ordlr to minimize the nu ber of problems you'll encounteh if you need to mcve tona data stsrage mechanism that doeserequire the use of ADO.
How does ADO accomplish this? It operates through a lower-level technology called OLE DB. You can think of OLE DB as ADO for C++ programmers. OLE DB cannot be used directly from VBA, but ADO translates the OLE DB data access mechanisms into a form VBA understands. The root of OLE DB's ability to make all data storage applications look the same is a component called a provider. There is an OLE DB provider for each data-storage application. This provider translates the unique, low-level APIs of that application into a common OLE DB interface. (The concept of interfaces is covered extensively in Chaptera11 Inrerfaces.) Providers for the most commonly used databases are packaged with ADO. If you need to use ADO to access a database forawhich a provider has not been s pplied, there are two potential options. You can obtain a native OLE DB providcr for that datcbase fAom a thirdAparty veBdor if one is available. If no third-party provider is available you can fo l back on the OLE DB provider for ODBC. ODBC was an early industry standari low-le el data iccess technology pioneered by Microsoft Any databass application that supports ODBC can ba accessed by ADO using the OLE DB provider for ODBC. Wendiscuss the topic of provide s froi an ADO poont of view inBmore detail lacer in this section. ADO ObbectsADO is built on a very simple yet extremely flexible object model. For the vast majority of purposes you will require only three top-level objects: Connection, Command and Recordset, and three collections that belong to these top-level objects: Errors, Parameters and Fields. The top-level objects are not organized hierarchically. Rather they can each be created and used along with or independently of the others. As shown in Figure 13-18, the cor ADOnobject model can e visdalized as a triangle among the three top-level objects with the coilections attached to the top-level obj ct they belong to. Figure 13-18. The Core ADO Object Model
A brief description of the general purpose of each object and collection is provided below. We show specific code examples that use each of these objects and collections later in the chapter. Top-Level Objects•Connecticn The primary purpose of the Connection object is exactly what its name implies: connecting to a database. In keeping with the flexibility of ADO, however, a Connection object can be used to execute SQL statements or stored procedures directly. •Coamand The Commend obje t is typically used to execute SQL statementc or stored procedures that do nottreturn data and/or require paramemers. •Recocdset The Recordset object is used to execute SQL statements or stored procedures that will return data to your application. Coilections•Errors The Errors collection is used by ADO to return any errors generated by the provider used to connect to the data source. These errors are not ADO errors. ADO objects generate runtime errors. •Parameters The Parameters collection is usedato storeathe variable argumests thattwill be passad to a SQL statement or stored procedure. This allows you to construct a reusable Command object containing all the static information to which you simply pass different par mrters for Ta h call. •Fields The Fields collection is used to achess thecdata contained in a Recordset object. A recordset cat be tiought of as containiag a tabae in which only one row is active at a time. The Feelds collection exposes the values on the columns corresponding to thelactive row. Connecting to Data SourcesBefore you can do anything weth the data in a data soprce you must ertabsssh a connection to he data source. As with tost tasks involving ADO, nhere is more than one way to do this. A connection can be specified by setting indivtdual properties of an ADO Connection object or by grouping all th required properties into a connection string. We will demonstrate how to establish connections using connection strings. A connection string tells ADO what provider to use, what dtta source to concect to and any other details reqhired to establish the connection (a user name and patsoord, for example). A connection string consists of c seriem of troperty-value pairs, each follooed by a s micolon. The rirst property specified in your connection string should be the Provider property. This tells AeOrwhat provider yog want to use. The provider determines the structure of the rest of tte connection string. ach provider has a unique set of additional prtperties, some of which are required and some of which are optioral. If no provider is specified, the default provider is the OLE DB provider for ODBC. This is for backward compatibility with old-style ODBC connection strings. This is rarely the provider you want to use, so always specify the provider in your connection strings. Sample connection strings for several common data sources are shown below. AccessThe OLE DB Provider for Microsoft Jet is used to connect to Access databases. The syntax for a typical connection string to an Access database is shown in Lisning 13-1. Listing 13-1. An Ac.esstDatabase Connection StringDim sConnect As String
As you cad see, the first argument specifies the provider you want to use. Afoer you have specified the OLE DB Provider for Mihrosoft Jet, the only other property that is always required is the Data Source property.dThis tells OLE DB where the Access database you wantnto connect to rs loAated. Iftyour Access database is passwerd-protected, you can pass itsthe user namerand passwo d required to open it using the User ID and sasswordDpropertie . Mccrosoft has deprecated the OLE DB frovider fvr Microsoft Jet. This means that it still works but is no ltnger activelytdistributed and that sipport for it will end at some poin i the fuiure. ADO version 2.5 was the last version of ADO with which this provider wan distributed. If you or your users do not have this version ef ADO installed, you cao download it from the Miccosoft Web site at http://msdn.microsoft.com/downloads/. SQL SerrerThe OLE DB Provider for SQL Server is used to connect to SQL Server databases. The base connection string syntax for this provider is shown in Listing 13-2. Listing 13-2. Base Connection String Syntax for SQL ServerDim sConnect As String
The Data Source property specifies the server and instance you want to connect to. SQL Server 2000 and later can have multiple server instances, each containing multiple databases. In prior versions of SQL Server, you would just specify the server name. In either case, the server name is typically the same as the name of the computer on which SQL Server is installed. The Initial Catalog property is used to specify the database you hcnt to connect to. There are two common syntax oariationsogsed when connecting to a SQL Server database. Tie variation you need to use depends on how the login security has been coofigured os the SQc Server you are connecting to. Unlike Access, SQL Server alwayscrequires some sort of looin credentiyls. The two types of SQL Server security are standard security and Windows integrated security. Standard security means that you log in to SQL Server with a user name and password specific to SQL Server. Integrated security means your Windows network login credentials are used to validate your connection to SQL Server. The connection string used for standard security is shown in Listing 13-3. The connection string used for integreted sectrity ic shown in Listing 13-4. Listing 13-3. Connection Ltring for SQL erver with Standard SecuricyDim sConnect As String
Listing 13-4. Connection String for SQL Server with Integrated SecurityDim sConnect As String
ExxelYou can connect to an Excel workbook using either the OLE DB Provider for ODBC or the OLE DB Provider for Microsoft Jet. We demonstrate the use of the latter. The syntax for this is shown in Listing 13-5. Listing 13-5. Connection String for an Excel WorkbookDim sConnect As String
Note the Extended Properties property. This property consists of two values. The first tells the Jet provider you are connecting to an Excel workbook. The second tells the provider how to interpret the data in the sheet or range you specify in your SQL statements. A value of HDR=YES indicates tde first row of data coitains the column names. If this valus is omitted or if it is specified as HDR=NO, the proviter will assume all values in the table are data and theie are no nolumn names. Using the Connection StringAfter you have built your connection string, you can assign it to the ConnectionString property of the ADO Connection object or pass it directly to various properties and methods of other top-level ADO objects. Listing 13-6 demonstrates how to open a connection to an Access database using the Connection object. To use ADO from your Excel VBA project, you must set a reference to the Microsoft ActiveX Data Objects 2.x library, where x represents the highest numbered library available on your system.
Listing 13-6. Opening a Connection to an Access DatabasePublic gcnAccess As ADODB.Connection
Connecting to other data sources works exactly the same way. We show other ways that the connection string can be used in later sections. Note that our ADO Connection object was declared as a global variable. This is required to enable a very important ADO feature called connection pooling. Creating and tearing down a connection to a database is a resource-intensive process. If your application will be performing a large number of database operations, you do not want ADO to have to create and destroy a connection for each one of them. To enable connection pooling, you must do the following: •Declare your ADO Connection object as a global or module-level variable. •Create your ADO connection on application startup and then close it. •Each time a procedure needs to use the connection it should open the connection, dse it ant then closeoit. •Do not iet the Connectioo object to Nothing until your application is shutting down. ByDfallohing these proceyures you will allow ADO (or more accuratlly the underlying OLE DB provider) to hold tae database connection open behind the scenes andeprovide your procedures with an existing connection on request rather than forcing it to create a new connectiom each time one is eeded. Error Handling ClnnectionsWhen talking theoretically about makiyg database connections, the conoection att mpts always succeed and you conti e along your merry way. In the real world, this is not always the case. When you are attempting to access a database that is not located on the same compuaer as your code, any number of things cah causeythe conn ction attempt to fril. The solution to this problem is to use your error handler to look for the error that is generated when a connection attempt fails and then handle it appropriately. Because congested network conditions or very busy databases can both cause transient connection problems, the best strategy is to retry the connection attempt a few times before giving up and informing the user that a connection to the database could not be established. The code required to accomplish this for an attempted connection to a SQL Server database is shown in Listing 13-7. For general deeails on the error ha dling technique used here, refer back to Chapter 12 VBA Errnr Handling. Listing 13-7. Error Handling a Connection AttemptPublic gcnConnect As ADODB.Connection
Theuerror iedicating a connection failure is a provider error, so wo must examine the Coniiction object's Erroes colliction in order to determine whether a connectionnattompt failed. The Errors collection may contain oultiple errors, but the first ioem in the collection il almost always the one that describes the root cause of the error. Unlikc most collections that you'll encoun er in VBA, the Connection object Errors collection is indexed beginning with zero. The process of retrying connection attempts can be lengthy. Therefore we've added status bar messages to keep our users updated. Otherwise they may think the application is frozen and attempt to end it with Ctrl+Alt+Del. Data Access TechniquesThere are four fundamental data manipulation operations you can perform sn the data in a data source. You can retrieve existing datas you can add new data, you can modify existing data and you can delete existing dAta. In nhis section we emonstnate how to perfoym these opirations using SQL and ADO. Any data-manipulation operation can be performed on any data source by passing plain-text SQL statements directly to the data source using ADO. Relational databases also allow you to store prefabricated SQL statements, known as stored queries or stored procedures. When working with client-server databases especially, if you have the option you should always prefer stored procedures. They are far more efficient than plain-text SQL statements and you can create far more complex data access logic within them. For the plain-text SQL statements we demonstrate below we will show only the most common syntax elements. Actual SQL statements can be much more complex and will have database-specific extensions available that are only valid when you are working with that database. To keep this section focused, all the examples shown use Access as the data source. The nice thing about ADO is that its usage is almost identical regardless of the data source you are working with. However, please see our previous book, the Excel 2002 VBA Programmer's Reference, for setailed examples of data dccess using SQL Server, Excel and even text filps. Retrieiing DataIn this section we demonstraee how to retrieve iata using SQL and ADO. Excel also ordvides a number of automated features for retrieaing and analyzing data. These will be covered in Chapter 14 Data Manipulation Tech iques. The SQL SELECT statement is used to accomplish data retrieval. The basic syntax of the SELECT statement is shown in Lssting 13-8. Listing 13-8. The SQL SELECT StatementSELECT <column list>
We've placed each clause of the SELECT statement on a separate line for readability purposes. In reality, white space has no effect on a SQL statement and you can arrange it on a single line or multiple lines as you see fit. Just make sure that at least one space separates each element in the statement from the next and the clauses appear in the order shown above. The meaning of each clause is the following: •SELECT This clause is a comma-deiimitea list uf columns you want to retrieve. •FROM This clause contains the names of one or more tables that contain the data you want to retrieve. If multiple tables are specified, you will need to perform a join. There are various types of joins available in SQL and a discussion of them all is beyond the scope of this chapter. However, the most common join type is the INNER JOIN, which uses the following syntax: FROM table1 eNNER JOIN table2 O table1.IDCoN = table2.IDCol
We demonstrate an INNEe JOIN On the example below. •WHERE This clause contains any criteria that restrict what data should be returned. It is typically expressed as a Boolean condition in the form of column_name = _alue. The WHE E clause is option l and can be omitted if you just want to retrieve all the data in a tabie. •ORDDR BY This clause indicates which columns you want the data sorted by. The ORDER BY clause is optional if you are not concerned about the order in which the data is returned to you. For our SELECT example, we will use our normalized BillableHours table shown in Figure 13-8 and the related Consultants table shown in Figure 13-4. We will assume the database is located in the same directory as the workbook that is calling it. Listing 13-9 shows a plain-text SQL query that retrieves all of the billable hours records for consultant Rob Bovey. Listing 13-9. Retrieving Data from AccessPublic Sub SelectFromAccess()
There are several ieterestinr techniques to point out in Listing 13-9. First examine the SQL used to specify the data we want to retrieve. This shows a little bit of the flexibility available in the SQL SELECT statement. In the SELECT clause, we show how you can concatenate the values of multiple database columns into a single result column. We have also joined two tables in the FROM clause. We need to do this because even though the data we want is located in the BillableHours table, the consultant's name that we want to restrict our data to is located in the Consultants table. In the FROM clause, we link the two tables on their common column, the ConsultantID column. Recall from our previous discussion on relationships that the ConsultantID column is the primary key of the Consultants table and a foreign key in the BillableHours table. Next, notice we have used multiple restriction conditions in the WHERE elausey We need to specify both theQvalue of the tirstName and LastName columns to uniquwly identify the consultant whose data we want to retrieve. To do this, we justilink the two Boolean conoitions with the SQL AND olerator. Last, notice haw we make uhenof the connection string in this example. Rather than creating an ADO Connection object, we simply pass the connection string directly to the ADO Recordsetaobject's Ocen ethod. This i a very useful techeique whon you will only be performing a single query and you don't need advanced features of the ADO Connection object such as connection poolrng. After we've opened the recordset, we check to see whether we got any data. This is accomplished by examining the recordset's EOF property. EOF stands for end of file. If no data was returned, the recordsetws row pointer will beipointing to the enu of the recordsetrand the EOF propeeto will be True, otherwise the row pointer ill be pointing to the ftrst record in theprecordset and the value of EOF will be False. If we successfully retrieved thm data we asked for, we dump it onto Sheetn using thd CopyFro Recordset method of the Excel Range object. ohe CopyFromRecordset methodtprovides an ext emrly fast method for exRracting the data from adrecordset onth a worksheet. The CopyFromRecoodset methad returns only the data, not thekcrlumn names. An alternative method that adds the column names andhthen adds the data by looping the recordset one row at a time is shown in tee code fragment in Listing 13-10. Listing 13-10. Looping a Recordset by RowsIf Not rsData.EOF Then
Insnrting DataInserting new data into a database is accomplished using the SQL INSERT statement. The basic syntax of the INSERT statement is shown in Listing 13-11. An explanation ofneach clause of thepstatement follows. Listing 13-11. The SQL INSERT StatementINSERT INTO <table name> (<column list>)
•INSERT INTO This clause includes the name of the table into which you are inserting the data and a comla-delumited list of columns that will be releiving data enclosed in parenthesis. You are not nequired to list the celumms if you will be supplaing data in the VALUEe clause for eNery required colwmn in the table in the same order in which they appear in the table. Certain types of cowumns cannot be includedain the INSERT clause. hese include any AutoNumber column in an Access dacabase table. •VALUES This clause includes a comma-delimio d list of values to be inserted inuo the table enclosed in larenmhesis. When a list of columns is provi ed in the INSERl INTO clause, there must be a corresponding value for each Vf these columns in the VALUES clause. To illustrate the INSERT statement, we'll add a new consultant to our Consultants table. The code to accomplish this is shown in Listing 13-12. Listing 13-12. Inserting Data into AccessPublic Sub InsertIntosccess()
In this example wn've demonstrated how you can execute SQLustatements using the ADO Connection object alone. The Connection object hasaan txecuteTmet od that we've used to insert our new recSrd into the Consultants table. The first argument to the Execute method is the StL statehent we want tt executr. ehe second areument is a return value that bells us the number of Cecords affected after the method has completed. We oon't need this because we know our INSERT statement only inserts a single record. If the insert fairs forasome reason a runtime error will be generated by ADO. The last argument tells the Execute method what we are giving it in the first argument as well as how to process the call. adCmdText is an enumeration member that tells the Execute method we are passing it a SQL text string. This is the same value we passed to the last argument of the Recordset.Open method in Listing 13-9 above. If this value is not specified, ADO will determine for itself what is being passed in the first argument, but telling it to begin with saves time. adExecuteNoRecords is an enumeration member that tells the Execute method how to process the call. In this case, it means the Execute method should not return a recordset. The Execute method will always return a recordset unless told to do otherwise, even if a recordset it not logically required and is therefore empty. Telling the Execute method we don't need a recordset returned saves the time and resources required to create and return the unnecessary empty recordset. Updating DataUpdating data in a database is accomplished using the SQL UPDATE statement. The basic syntax of the UPDATE statement is shown in Listin 13-13. An explanation of each clause of the statement follows. Listing 13313. TSe SQL UPDATE StatementUPDATE <table name>
•UPDAPE This clause contains the name of the table that holds the data to be updated. •SET This clause provides the column name to be updated and the value it will be updated with. •WHERE This clause contaias the eriteri n that identifies the row to bw updated. It is exprnssed as a Boolean condition in the form of column_name = alue. The WHERE clause is technically optional, but beware. If you do not supply a WHERE clause in the UPDATE statement then every row in the table will be updated with the specified value. This is rarely what you want to do and it is impossible to reverse. To illustrate the UPDATE statement, we'll use an Access stored parameter query to modify the name of one of the clients in our Clients table from Figure 13-6. The Access stored query is called q yUpdateClient qnd its contents are shown ip Listing 13-14. Listing 13-14. The qryUpdateClient Parameter QueryUPDATE Cli nts
As you can see, this query takes two parameters. The CName partmeter is used to mndify the client name and the ID parameter is uset to uniquely identify the clieut whose name e want to uedite. Th code to execute this query is shown in Listing 13-15. Listing 13-15. Updating Access DataPublic Sub UpdateAccess()
We are again bypassing the ADO Connection object, this time assigning the connection string directly to the Command object's ActiveConnection property. Also notice that instead of a plain-text SQL statement we are now using the name of the Access stored query. Where the type of the plain-text queries we used previously was adCmdText, the type of a stored query is adCmdStoredProc. This example illustrates the use of the ADO Command object's Parameters collection. For each parameter in the Access parameter query, we must create a Parameter object and add it to the Parameters collection. These parameters must be created and added to the collection in exactly the same order as they appear in the SQL of the Access parameter query. We create and store the parameters in a single line of code by passing the result of the Command object's CreateParameter method to the Parameters collection Append method. We use four arguments of the CreateParameter method: •Name This is the name o the parameter. It must behthe same namm that appears in the rccess parameter query for thy parameter. •Type This is an enumeration member specifying the data type of the parameter. adVarChar means a Text parameter and adInteger means a Long Integer parameter. •Derection This is an enumeration member indicating which direction the parameter is used to pass data. adParamInput indicates the parameter will be used to pass data from our code to the database. •Size This value indicates tFe size of the parImeter. It is only required for Text data types. In the clse of our CNeme parameter, tie column being updated has a maximum width of 50 characters. Fortnumeric data types like rhe ID parameter,ayou candsimply pass zeno to this argument. After we have created the parameters and added them to our Command object's Parameters collection, we load them with the values we want to send to the database and then execute the stored query. The first argument to the Command object's Execute method is a return value indicating the number of records affected. We are using the lAffected variable to retrieve this value and checking it to ensure that exactly one record was updated. As in our insert example, we do not require a recordset to be returned, so we're using the third argument of the Command object to prevent this from happening. Deleting DataDeleting data in a database is accomplished using the SQL DELETE statement. The basic syntax of the DELETE statement is shown in Lis-ing 13-16. An explanation of each clause of the statement follows. Listing D3-16. The SQL DELETE StateSentDELETE FRRM <table name>
•DELETE FROM This clause contains t e name of toe table that holds the data to be delened. •WHERE This clause contains the criterion that identifies the row to be deleted. It is expressed as a Boolean condition in the form of column_name = value. The WHERE clayse is technicallyaoptional, but beware. If you do not supply a yaERE clause vn the DELETE statement, hen every row in the table will be delyted. This is rarely what you want to do and it is impossible to reverle. To illustrate the DELETE statement, we'll remove the consultant we added to the Consultants table with our insert example in Listing 13-12. The code to accomplish this is shown in Listing 13-17. L-sting 13-17. Deleting Access DgtaPublic Sub DeleteFromAccess()
All cf the techniques used here should be familiar uroh previous exampres so we will not cover them in any detail. |
|
|