12.3 Database Access with the ADO Library


12.3 Database Access with the ADO Library

Remarks  

As the author of this book I face a dilemma at the start of this section. In 1999 I completed a six-hundred-page book on ADO from the viewpoint of Visual Basic 6. A great part of the basic information on ADO given there is also relevant to programming under Excel 2000/2002. Of course, I would love to discuss all of this here, though to be sure, a complete description is out of the question. Therefore, I can say only that the following description is greatly compressed, that many functions are not described at all, that from among the many options offered by ADO only the most important are mentioned, and so on. For simple tasks and those first baby steps this information should suffice. If your desires lead to a greater involvement with ADO, then you should consult the specialized literature on this subject. (But do not purchase any book on ADO.NET! While ADO.NET is the successor library to ADO for the .NET framework, ADO and ADO.NET are fully incompatible!)

Introduction

What Is ADO?

ADO stands for ActiveX Data Objects and is a comparatively new library for database programming. ADO is the successor to the DAO library (which is still available; see below).

With ADO you can

  • establish a connection to almost any database operating under Windows (provided that this database possesses an ODBC, or, better, OLE-DB driver)

  • query data and evaluate them in your program

  • add new data, edit, and delete (to the extent that you have access privileges)

Note  

In order for the ADO functions to be available for use in your Excel programs, you must activate them with ToolsReferences. The full name of the library is Microsoft ActiveX Data Objects 2.1 Library. In the object browser the abbreviation is ADODB. Take care that you do not select one of the numerous related libraries.

Versions of ADO

As it was with Office 2000, ADO 2.1 is included with Office 2002. With Office 2000 this made sense, since ADO 2.1 was the current version at that time. But when Office 2002 was released, there was already ADO 2.7, which was installed, for example, with Windows XP. The decision to install the relatively old ADO version 2.1 with Office 2002 was made presumably for reasons of compatibility.

In principle, you may, of course, use a newer version of ADO with your VBA programs, provided that the newer version is installed on your computer. Simply use ToolsReferences to select a more recent version of the library. The advantage to this course of action is that the newer versions contain fewer errors and some additional functionality (for example, for procesing XML data with the SQL server.) The disadvantage , however, is that your Excel program will function on another computer only if the corresponding ADO version is installed.

ADO Versus DAO

In addition to the new ADO library, the much older DAO library is also included with Excel 2000/2002. However, DAO is included only for reasons of compatibility, and should not be used in new projects.

There is, then, no good reason not to leave existing DAO code as it is. DAO may be somewhat out of fashion, but in exchange it functions reliably, in particular in combination with Access databases.

Tip  

It is possible, in principle, to use both DAO and ADO in the same program (during the changeover phase from one to the other, say). However, one is then faced with a number of like-named objects, such as the DAO and ADO Recordset objects. For unique identification, in declaring variables you will now have to prefix the library name (that is, DAO.Recordset or ADODB. Recordset).

Tip  

The ADO library supports many, but not all, functions of DAO. If you wish to use new Access databases or tables, or need to manage indexes or access privileges, then you must use the ADOX library in addition to ADO.

Introductory Example

As with all the examples of the section, the following procedure accesses the database Nwind.mdb . (The construction of the database is described in the previous chapter. The database file is located in the sample files for this book at http://www.apress.com.)

In the first two lines of the procedure intro two new objects are created, a Connection object and a Recordset object. The task of conn is to establish the connection to the database. To this end the method Open must be executed, in which a character string is passed that contains the parameters of the connection. The Connection character string can become somewhat long and complex, but in the simplest case two parameters suffice for access to an Access database:

 Provider=microsoft.jet.oledb.4.0;Data Source=C:\path\name.mdb 

Provider denotes the driver that is to be used for the access. For each database system (Access, SQL Server, Oracle, etc.) there is a separate driver. Data Source specifies the location at which the database file is located, while intro presupposes that Nwind.mdb is in the same folder as the Excel file.

If the construction of a connection is successful, then in the next step a query can be executed. For this two things are necessary: a Recordset object (which actually serves for evaluating the query result) and a character string with the SQL command of the query. Without a minimal knowledge of SQL, ADO programming is unthinkable. In the following example we have the following query:

 SELECT LastName, FirstName FROM employees ORDER BY LastName, FirstName 

This creates a list of all employees of the Northwind company, ordered alphabetically . (The names of all employees are stored in the table employees .) For the execution of the query the method Open serves as well.

The following loop selects from the Recordset object all the data records found. Here it is important to understand that the Recordset object indeed enables access to all the found data records, but that at any time only one data record (one line, so to speak) is active and can be read. To select the next data record the method MoveNext must be executed. If it happens that there are no more records, then the property EOF attains the value True .

It now remains only to explain how the individual fields of the active record are read: Quite simply, the rec variable is followed by an exclamation point and then the name of the field. In the course of the loop the cells A1, A2, A3, etc., will be filled with employees names one after the other (Figure 12-18).

 ' ADO.xls, Module1 Sub  intro  ()   Dim conn As New Connection, rec As New Recordset   Dim ws As Worksheet   Dim sql$, i&   Set ws = ThisWorkbook.Worksheets("intro")   conn.Open "Provider=microsoft.jet.oledb.4.0;" + _     "Data Source=" + ThisWorkbook.Path + "\nwind.mdb"   sql = "SELECT LastName, FirstName " & _         "FROM employees ORDER BY LastName, FirstName"   rec.Open sql, conn   While Not rec.EOF     i = i + 1     ws.[A1].Cells(i) = rec!LastName + ", " + rec!FirstName     rec.MoveNext   Wend   rec.Close: conn.Close End Sub 
click to expand
Figure 12-18: The result of our introductory example

It is a part of good ADO programming practice that ADO objects that are no longer needed be closed with Close . With intro that would not be absolutely necessary, since in any case the variables conn and rec exist only so long as the procedure is being executed. Nonetheless, if you get used to the logical use of Close , then you make possible a more efficient use of the database and save yourself annoyance and possible access conflicts.

ADO Overview

click to expand

Even though the above hierarchy represents a logical connection among the objects, this hierarchy is not a requirement for the creation of new objects. Figure 12-19 better represents this very flat object hierarchy. For example, you can create a new Recordset object without previously explicitly creating a connection to a database.

 Dim rec As New Recordset rec.Open "SELECT * FROM table ...", "Provider=..." 
click to expand
Figure 12-19: Connections between objects

Naturally, you must specify at some point the data source, and internally, in any case, a Connection object is generated. In many cases this convenience leads to code that is difficult to maintain and to unnecessarily many connections to the database. But there are exceptions, for example in generating Recordsets from a file instead of from a database, where in truth no Connection is necessary.

Tip  

The indices of all ADO enumerations go from 0 to Count-1 (in contrast to Excel enumerations, which generally range from 1 to Count).

Additional (Dynamic) Properties

The objects Connection , Command , Field , Parameter , and Recordset refer to the enumeration object Properties , which for the sake of clarity was not given in the hierarchy above. With Properties additional (dynamic) properties can be addressed. Access to individual properties is via the Property object. Which dynamic properties exist depends on the driver being used. In the case of simple applications it is not necessary that you be concerned with these properties.

Connections

In principle, it is simple to create a connection to a database: Simply create a new Connection object and then execute Open , passing a character string with the connection information. This character string has the structure "parameter1=value1;parameter 2=value2; " . Both the parameter names and their settings may contain spaces.

 Dim conn As New Connection conn.Open "Provider=..." 
Tip  

You can also execute Open the first time without additional parameters if you first save the connection information in the ConnectionString property. This property contains, after Open, a much longer character string, which in addition to Provider and Data Source also contains a host of other information. (This concerns the default settings of additional parameters that give the default settings for various other parameters.) Individual parts of the character string can be easily selected with conn.Properties("xy"), where xy is a parameter of the character string (for example, "DataSource").

ADO limits the establishment of the connection to the time span contained in ConnectionTimeout (the default is 15 seconds). If the connection does not succeed within this time frame, then an error occurs. In making connections to database servers that are overburdened it makes sense to raise this time limit before executing the Open method.

The property Mode specifies which database operations are allowed (for example, read-only access) and whether other users are allowed to access the database at the same time. The default setting in the case of Jet databases is adModeShareDenyNone . In contrast to the information in the ADO documentation, this means that other users may likewise open, read, and edit the database. If you wish to open the database in another mode, you must set the Mode property differently before executing the Open method. The two most important settings are adModeShareExclusive (excludes any further access) and adModeRead (readonly access).

ConnectionString for Access Databases

The real problem in establishing a database connection is in correctly setting the ConnectionString character string. The construction of the character string depends heavily on the database system. In the case of Access databases the most important parameters are the following:

ACCESS DATABASES

Provider

Microsoft.Jet.OLEDB.4.0

Data Source

<file name of the database file>

User ID

<user name> (optional)

Password

<password> (optional)

In any case, if you plan on working only with Access databases, you can skip the remainder of this section. The following information is relevant only if you wish to establish a connection to another database system. The examples in this book deal exclusively with Access databases.

ConnectionString for SQL Server or MSDE (OLEDB)

If your data are being administered by an SQL Server or the MSDE (Microsoft Data Engine), then you must provide more information:

SQL SERVER / MSDE

Provide

SQLOLEDB or SQLOLEDB.1

Data Source

<network name of the computer on which the SQL server runs>

Initial Catalog

<name of the database on the SQL server>

Integrated Security

SSPI (if you use Windows NT/2000/XP for authentication)

User ID

<user name> (only if no NT/2000/XP authentication)

Password

<password> (only if no NT/2000/XP authentication)

Prompt

Prompt or Complete (if login dialog is to be displayed)

Here is another remark on the optional Prompt parameter. If you use here the setting Prompt , then before each attempt to establish a connection a login dialog appears, in which the name and password can be provided. The setting Complete means that the dialog appears only when the connection fails. If you do not specify Prompt , then an error occurs if the connection fails.

Finally, we offer an example for establishing a connection to the example database pubs where the SQL server runs on the computer mars and you rely on NT authentication:

 Dim conn As New Connection conn.Open "Provider=SQLOLEDB;Data Source=mars;" & _   "Initial Catalog=pubs;Integrated Security=SSPI" 

With the following instruction you leave the login to the user:

 conn.Open "Provider=SQLOLEDB;Data Source=mars;" & _   "Initial Catalog=pubs;Prompt=Complete" 

ConnectionString for ODBC Data Sources

Not every database possesses a new, ADO-compatible, OLE-DB driver. (OLE-DB is the internal basis on which ADO rests.) In such a case an ODBC driver can be used. (ODBC is the precursor technology to OLE DB: old, but reliable and usable on almost every database system.) The communication between your program and the database proceeds on three fronts: database ODBC OLE DB ADO.

The keywords for the ConnectionString character string are then once again different:

ODBC

Provider

MSDASQL

DNS

<name of an ODBC data source>

Driver

<name of the ODBC driver> (e.g., SQL Server)

Server

<network name of the computer on which the database server runs>

Database

<name of the database on the SQL server>

Trusted Connection

yes (if you use Windows NT/2000/XP for authentication)

UID

<user name> (only if no NT/2000/XP authentication)

PWD

<password> (only if no NT/2000/XP authentication)

To create a connection again to the SQL server, but this time via its ODBC driver, the following template can be followed:

 conn.Open "Provider=MSDASQL.1;Driver=SQL Server;" & _   "Server=mars;Database=pubs" 

If an ODBC data source was defined (with the ODBC system administration tools), its name can be simply specified with DNS= . All further information is omitted.

Reading a Connection String from a *.udl File

A fourth variant consists in saving the connection parameters in a so-called DataLink file with the suffix *.udl . Such a file can be created in Explorer: FileNewMicrosoft Datalink. A dialog appears in which you can specify all the settings.

DATALINK FILE

File Name

<file name of the *.udl file>

Prompt

same as for an SQL server

 conn.Open "File Name=C:\path\name.udl" 

Recordset

The Recordset object handles the management of data records that result from a database query. In comparison to the other ADO objects here there are many properties and methods available. The reason for this complexity lies in the great range of applications for Recordset objects. The following list enumerates the most important functions of this object:

  • You can read all the records in sequence.

  • You can edit, insert, and delete data records.

  • You can move through the list of data records (that is, jump to the first, last, next, or previous record or search for a particular record).

  • You can search within a list of records and alter the local sort order.

  • You can save the list of records in a local file (independent of the database) and load it again later from there.

With a number of properties, the purpose of the object must be specified before the creation of a Recordset object. Here there is a simple rule: The less you require of the Recordset object, the fewer methods and properties you may use, but the more efficiently the remaining operations can be executed.

 rec.Open sql, conn [, cursortype [, locktype]] 
Tip  

If you create a list of data records simply with rec.Open sql, conn, the Recordset object is automatically optimized with respect to optimally efficient performance. This means, however, among other restrictions, that you cannot change any data (read only) and that you can move through the record list only with MoveNext (forward only), that you cannot sort data locally, that you cannot use the property Bookmark. If you wish to execute more demanding operations, you must make this known in the optional parameters of the Open method (see the heading Recordset Types).

SQL Abbreviations

As a rule, to open a Recordset object is required that you pass the database query as an SQL command. For this reason we provide, in the subsection after next, an overview of the most important SQL commands. In many cases, however, you can avoid formulating an SQL command:

  • If you wish to access all the fields of a table, it suffices to specify in the first parameter of Open simply the name of the table. Internally, this is interpreted as SELECT * FROM table .

  • If you have Access available, you can define queries there. When accessing Access databases you can then simply specify in Open the name of the query (just as with the name of a table).

  • If you are working with a database server (such as Oracle), you cannot predefine any queries, but you can define so-called views . Even the name of a view suffices for Open ; the internal interpretation is again SELECT * FROM view .

Recordset Types

The three properties CursorLocation , CursorType , and LockType determine the type of the Recordset . These properties must be set in advance or sent as optional parameters to Open . Once the object has been opened once, no further changes are possible.

The easiest property to understand is certainly LockType . This property specifies whether data can be altered and how possible conflicts are to be handled (when two or more users wish to alter the same record simultaneously ).

LOCKTYPE SETTINGS

adLockReadOnly

data may be read, but not changed (default)

adLockPessimistic

pessimistic conflict control

adLockOptimistic

optimistic conflict control (more efficient)

adLockBatchOptimistic

several data records may be changed simultaneously

There is insufficient space here to go deeply into the fine distinctions between optimistic and pessimistic locking. In most cases adLockOptimistic is the better choice. If a conflict arises, an error occurs only at the first attempt to actually save the data (as a rule, during execution of the Update method). It is this point in the program that you must therefore protect. The setting adLockBatchOptimistic enables so-called batch updating, by which several data records are changed simultaneously. This is efficient, but also complicated and seldom necessary in Excel applications, for which reason we shall omit mention of the details here.

The property CursorType determines how the list of data records is to be managed internally (that is, by the database system and by ADO). The setting influences the degree of flexibility with which you can move through the record list.

CURSORTYPE SETTINGS

adOpenForwardOnly

minimal administrative overhead (that is, very efficient), but fewer supported functions (default)

adOpenDynamic

relatively low administrative overhead, but better navigation options

adOpenKeyset

higher administrative overhead, all functions

adOpenStatic

even greater administrative overhead; comparatively slow access to the first records, fast in subsequent accesses

The setting adOpenForwardOnly is suitable when you simply wish to read all the data records in sequence (and possibly edit them as well). It is not possible to reactivate a record that has already been processed , and any type of local processing (search, sort, filter) is excluded.

It is impossible to describe the differences among the other three types without going deeply into the principles of database design. The main issue is at what moment how much data is to be transferred from the database into the Recordset , and whether subsequent changes to the data are to be visible to other users in Recordset . In case of doubt, adOpenKeyset is often a good compromise between efficiency and versatility.

Finally, the property CursorLocation determines where the management of the record list is to take place: directly in the database ( adUseServer ) or through the ADO library ( adUseClient ). This makes a great deal of difference, particularly in the case of a network system: In one case the computer with the database server is more computationally burdened, while in the other case it is the computer on which your Excel program is running.

If you are working with large quantities of data (record lists with many more than one thousand records), then adUseServer is often the better choice: This avoids the necessity of unnecessarily transferring many data records. However, with adUseServer a large number of ADO features are unavailable.

With adUseClient all data are immediately transferred when the Recordset is opened. For large record lists this can involve a large amount of time while the program is blocked. Furthermore, the data must be maintained in local RAM. The advantages are that once the data are present, there are very versatile and efficient forms of further processing.

Here, too, we make a recommendation without being able to illuminate all the background issues: If you wish to move more through the record list than you can with simply MoveNext , then adUseClient is usually the better choice. ADO is better optimized with respect to this access method.

CURSOR LOCATION SETTINGS

adUseServer

data management directly in the database (default)

adUseClient

data management by the ADO library

The situation is made more complicated in that not every combination of these three properties is permissible. For example, with CursorLocation=adUseClient only CursorType=adOpenStatic is possible. (If you request a Recordset in an impermissible combination, you receive automatically a Recordset with other settings. No error message occurs.) Which combinations are allowed depends again on the database driver. With Access databases different combinations are allowed from those in a database run by Oracle.

If you wish to determine which operations are possible with the Recordset type that has just been selected, you can use the method Supports . With Supports(adBookmark) , for example, you can test whether Bookmarks are supported ( True or False ).

Data Fields

Every data record of a Recordset consists of one or more fields. (Data fields correspond to the column names of a table.) As already demonstrated in the introductory example, access to fields is accomplished by writing rec!feld . If the field name contains special or blank characters , the name must be placed in square brackets, for example, rec![article-nr] .

Here name is an abbreviation for rec.Fields("name").Value . Internally, the data fields are managed in a Fields enumeration. Not surprisingly, Fields(n) or Fields("name") refers to Field objects. Each of these objects provides information about the field name ( Name ), the data type ( Type ), its size ( DefinedSize and ActualSize ), and so on. Here Value contains the value of the field for the current data record.

The following example provides field information for all data fields of the employee table. The result is shown in Figure 12-20.

 ' ADO.xls, Module1 Sub  rec_fields  ()   Dim conn As New Connection   Dim rec As New Recordset, f As Field   Dim ws As Worksheet, i&   Set ws = ThisWorkbook.Worksheets("fields")   conn.Open "Provider=microsoft.jet.oledb.4.0;" + _     "Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"   rec.Open "employees", conn   For Each f In rec.Fields     i = i + 1     ws.[a1].Cells(i) = f.Name     ws.[b1].Cells(i) = f.Type     ws.[c1].Cells(i) = TypeName(f.Value)   Next   rec.Close: conn.Close End Sub 
click to expand
Figure 12-20: The data fields of the employee table
Tip  

Please note that most databases can place NULL in a field. This means that no data are available. This can lead to problems if you wish to allocate a particular type to the field of a variable, such as x$ = rec!comment. You must test with IsNull(rec!comment) whether the field contains any data at all.

Navigation in Data Records

Navigation within the list of data records is accomplished primarily with the help of five methods:

MoveNext

activates the next record

MovePrevious

activates the previous record

MoveFirst

activates the first record

MoveLast

activates the last record

Move n

move forward n records (or backward in the case of negative n )

Note that you must not proceed on the assumption that the data records appear in any particular order, unless the record list is based on an SQL query with an ORDER-BY clause.

The pointer to the current record may be moved with MoveNext or MovePrevious to a position beyond the first or last record. In this case the property EOF ( end of file ) or BOF ( bottom of file ) has the value True , and there is no valid record being pointed to.

The method Move enables the pointer to be moved not one, but several, positions from the current data record. Optionally, a second parameter can be given that relates n to the first or last record.

Caution  

Let us underscore the following point: EOF and BOF do not indicate the last valid data record. When one of these properties is True, then the region of valid records has been exceeded. At this point an invalid data record is indicated, which cannot be further processed.

Positional Information

The property AbsolutePosition determines which of the RecordCount records is currently active (the first, second, etc.). AbsolutePosition can also contain the value adPosUnknown (-1), adPosBOF (-2) , or adPosEOF (-3) . This allocation of a value to AbsolutePosition represents a further possibility, namely, to select the currently active record.

Sometimes one wishes to return to the current record at a later time. To accomplish this, save the contents of the Bookmark property in a Variant variable. (This variable serves, then, as a bookmark.) To return, allocate the saved value again to the Bookmark property.

Searching and Locally Sorting Data Records

With the method Find you can find, beginning with the current record, the next data record that satisfies a particular criterion. All parameters other than the first are optional. If no suitable record is found, the Recordset object points to an invalid record ( EOF=True for forward search, BOF=True for backward search).

 rec.Find criterion, offset, direction 

As search criterion you provide a character string with the column name and comparison operation, such as "UnitPrice > 10" . In the case of text comparison with the operator Like , the underscore character can serve as a wildcard for a text character, the asterisk for several characters, say "Name Like ˜M* " . The asterisk can be used only at the end of the search pattern, or at the beginning and the end. More complex expressions such as Like ˜a*b are not allowed.

offset specifies at which record the search is to begin. In the default setting the search is begun at the current record. To begin with the first record, execute the method MoveFirst before Find .

If the current record satisfies the search criterion, then it will be returned. In order to find the next satisfactory record, you must execute either MoveNext or Find with offset:=1 . With direction you can specify the search direction ( adSearchForward / -Backward ).

Editing Existing Records

In order for data records to be edited, deleted, or inserted, the Recordset object must be opened with a suitable LockType setting.

To edit an existing data record, you need to change only one of the fields (thus rec!fieldname = newvalue ). In contrast to the DAO library there is no longer an Edit method to introduce the editing process.

Changes made are saved as soon as the method Update is explicitly executed. However, saving is automatic if another data record is activated (such as with a Move method).

Unsaved changes can be undone with CancelUpdate . In this case all fields of the current record reassume their original values. (These values can also be determined without CancelUpdate , by evaluating the Field property OriginalValue , that is, by rec!fieldname.OriginalValue .)

Adding a Data Record

If you wish to generate a new data record, execute AddNew . (This will also save any unsaved changes.) You will have created a new, empty, data record, which is now the active record. Furthermore, the record fields that are managed automatically by the database (usually ID fields, in which the next number in a running sequence is inserted) are automatically initialized . All other operations follow the same course as with editing a data record: You must set the properties and save changes with Update .

Deleting a Record

It is much simpler to delete a record. Simply execute the method Delete .

Possible Errors

In all these three operations there is a rather large chance that an error will occur. The most probable causes are the following:

  • An edited/new record cannot be saved because certain data field have impermissible values. Depending on how the table has been defined, certain fields may not be empty or may not contain the value Null or "" .

  • An altered record cannot be saved because a second user is editing the same record or perhaps has already edited it.

  • A record cannot be deleted or changed because the conditions of referential integrity would be violated. (That is, the relation to a record in another table would be destroyed . In such cases the dependent records of the other table must first be deleted.)

  • The record cannot be changed because you do not have sufficient privileges. (Often, databases are so strongly secured that except for the administrator, no one may directly change a table. Changes must then be carried out via the specific procedures provided.)

Note that these errors can occur at any point of your program at which the current data record can be altered. Thus very thorough error handling is required.

SQL Commands (Command)

The Command object has as its purpose the execution of SQL commands. You are perhaps wondering how this differs from Recordset , with which an SQL command can also be executed. The Command object must be used if the query (Access) or an SQL procedure (various database servers) is to be executed with parameters.

A command executed with Command can also return as result a list of data records, but that need not be the case. For example, you can execute a DELETE command that deletes a particular data record. In this case you do not obtain an immediate result (at most an error message if the deletion is impossible).

We cannot go into all of the many particularities of the Command object here, such as asynchronous queries, the use of a wide variety of types of parameters, the multiplicity of syntax variants for executing commands. Instead, the following example demonstrates a possible application, whereby in command_parameters we shall pose a new query with a parameter:

 SELECT companyname FROM customers WHERE country = ? 

This query returns a list of all customers in a particular country. Before the query can be executed, the Command object must be given a country name by means of the Parameters enumeration, such as " germany " , for example. To execute the command, Open will be used again for the Recordset object. But as the first parameter an SQL command will not be given, but rather the Command object.

This example also demonstrates the application of the method CopyFromRecordset . This method is defined for the Excel Range object and enables it to transfer most efficiently the entire contents of a Recordset into a worksheet. The process begins with the cell that is specified as object by CopyFromRecordset . (The While “Wend loop used in the other examples is strictly for didactic purposes. It is supposed to show how data records are read.)

 Sub  command_parameters  ()   Dim conn As New Connection   Dim rec As New Recordset   Dim comm As New Command   Dim ws As Worksheet   Dim countryname$   Set ws = ThisWorkbook.Worksheets("command")   conn.Open "Provider=microsoft.jet.oledb.4.0;" + _     "Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"   Set comm.ActiveConnection = conn   comm.CommandText = _     "SELECT companyname FROM customers WHERE country = ?"   countryname = InputBox("Please input the name of a country " & _     "(for example, 'germany')")   comm.Parameters(0) = countryname   rec.Open comm   ws.[a1].CopyFromRecordset rec   rec.Close: conn.Close End Sub 

SQL Basics

In the previous examples a bit of SQL code has appeared here and there, but almost always of the most elementary kind. Although this section can discuss only the first five percent of the fine art of SQL, it should at least communicate the fact that SQL is a rather powerful language. Perhaps you will acquire an appetite for more.

Pointer  

In this book SQL commands are written with uppercase letters to distinguish them from Visual Basic keywords. SQL commands are not case sensitive. All examples here refer to the Northwind database.

SQL Queries

SELECT selects the fields from the table named with FROM . The field names are separated by commas. If the names of fields contains special or blank characters, you must place them in square brackets. If the same field names are used in several tables, then the table name must be prefixed to the field name, for example, table.name . The asterisk holds for all fields in the table.

FROM specifies the tables from which the fields previously named with SELECT are to be taken.

INNER JOIN joins two tables through a common field (for example, an ID number). INNER JOIN is of central importance in relational databases when data are to be combined from several tables.

WHERE specifies the conditions that the fields must satisfy . If WHERE is not used, then the resulting Recordset contains all the data records of the table.

GROUP BY field combines all the resulting rows for which the given data field has the same value. For the other fields in the query so-called aggregate functions must be applied, such as SUM(field) , to calculate the sum.

ORDER BY specifies the fields according to which the list resulting from the previous instructions is to be ordered. With the suffixed command ASC or DESC one specifies whether the sort order is to be ascending or descending (default is ascending ).

Tip  

If you encounter difficulties in constructing an SQL query, try to formulate it in Access or MS Query. Both of these programs allow convenient interactive posing of queries; and both programs then reveal to you the associated SQL code. If you are processing databases in Access format, you can save your queries directly in the database. In VBA code you can use such predefined queries immediately, without worrying about the underlying SQL code.

In the following examples, SQL instructions are divided among several lines merely to improve the visual presentation. In a program the SQL instruction must be within a single character string.

SELECT Example 1

We shall create a list of all products that cost more than 50 pricing units. The list is sorted according to product name.

  SELECT  ProductName, UnitPrice  FROM  Products  WHERE  UnitPrice > 50  ORDER BY  ProductName Product Name            Unit Price Carnarvon Tigers        62.50 Cte de Blaye           263.50 Manjimup Dried Apples   53.00 Mishi Kobe Niku         97.00 ... 

SELECT Example 2

Our second example is a variant of the first. The only difference is that in addition to the product name, its category is shown. To be sure, this is more easily said than done: Since the category name is defined in another table, a linkage between the two tables must be established with INNER JOIN . Note that the field CategoryID appears in both tables Categories and Products . For this reason, with INNER JOIN the fields must be prefixed by the table name.

  SELECT  ProductName, CategoryName, UnitPrice  FROM  Categories  INNER JOIN  Products  ON  Categories.CategoryID  =  Products.CategoryID  WHERE  UnitPrice>50  ORDER BY  ProductName Product Name            Category Name      Unit Price Carnarvon Tigers        Seafood            62.50 Cte de Blaye           Beverages          263.50 Manjimup Dried Apples   Produce            53.00 Mishi Kobe Niku         Meat/Poultry       97.00 ... 

SELECT Example 3

In SELECT queries so-called aggregate functions can be employed. The following example returns the number of products and their average price. The keyword AS is used to name the result field.

  SELECT COUNT  (*)  AS  nrOfProducts,  AVG  (unitprice)  AS  avgPrice  FROM  products nrOfProducts      avgPrice 77                28.87 

SELECT Example 4

Our last example proves that Access can calculate. The query returns a list with the value of every order. To this end, for each row of the table Order Details the product of unit price, quantity, and discount is computed. The resulting list is broken into groups with common order IDs. For each group the sum of the order values of the individual items is calculated.

  SELECT  OrderID,  SUM  (UnitPrice * Quantity * (1-Discount))  AS  Sales  FROM  [Order Details]  GROUP BY  OrderID Order ID         Sales 10248            440 10249            1863.4 10250            1552.59998965263 10251            654.059999750555 ... 

Editing Data

The previous examples of queries had the task of creating an ordered list according to certain criteria, that is, to read data. You can also use SQL to edit data, and indeed, in many cases this is much more efficient than line by line processing in program code. This holds particularly for network applications: The amount of data required to send a SQL command over a network is so small that it won't affect the performance at all. On the other hand, if you first read each record over the network and then must edit and write it, much time will be lost in data transfer. In Access such commands are called action commands.

DELETE deletes the records selected with WHERE .

UPDATE updates individual data fields with SET , to the extent that they satisfy conditions named in WHERE . (For example, you could raise all prices by ten percent.)

INSERT INTO inserts data records into an existing table.

SELECT INTO creates a new table.

Syntax Summary

CONNECTION PROPERTIES AND METHODS

ConnectionString

character string with connection data ( Data Source , Provider, )

ConnectionTimeout

maximal time to establish connection (in seconds)

CursorLocation

create Recordset s with client or server cursor

Mode

access privileges (read only, write, sharing, etc.)

Close

terminate connection

Execute

execute database query or command

Open

establish connection

RECORDSET PROPERTIES

ActiveConnection

reference to Connection object

BOF

the invalid record before the first record is active

Bookmark

identification of the current data record

CursorLocation

client or server cursor

CursorType

cursor type (e.g., adOpenForwardOnly , adOpenStatic )

EditMode

edit mode (e.g., adEditNone , adEditAdd )

EOF

the invalid record after the last record is active Fields reference to the listing of the data record fields

Filter

specifies which data records are visible

LockType

locking mechanism (e.g., adReadOnly , adLockOptimistic )

RecordCount

number of all data records belonging to the object

Sort

contains the column name (text) for local sorting (client)

State

state of the entire Recordset object (e.g., adStateOpen )

!Name

shorthand for Fields("name")

RECORDSET METHODS

CancelUpdate

undo changes in the current record (do not save)

Close

close Recordset

Find

find record satisfying a particular criterion

GetRows

copy contents of entire Recordset s into twodimensional field

Move n

move record cursor by n positions

MoveFirst

activate first record

MoveLast

activate last record

MoveNext

activate next record

MovePrevious

activate previous record

Open

open Recordset (execute query, load from file, etc.)

Save

save Recordset in a file

Supports

test whether the Recordset supports specific functions

Update

save changes in current record

rng.CopyFromRecordset

copy Recordset in Excel table( rng is a Range object)

FIELD PROPERTIES

 

ActualSize

actual memory usage in bytes

Attributes

special attributes (e.g., adFldUpdatable )

DefinedSize

maximal size (for character string of variable length)

Name

name of the data field

NumericScale

number of decimal places (for fixed-point numbers )

OriginalValue

original value (only for batch updating)

Precision

number of digits (for fixed-point numbers)

Type

data type

Value

value

COMMAND PROPERTIES AND METHODS

ActiveConnection

refers to the associated Connection object

CommandText

code of the command/query (normally SQL)

CommandTimeout

maximal time for query (in seconds)

CommandType

command type (e.g., adCmdText , adCmdTable )

Parameters

refers to Parameter object

State

state of asynchronous query (e.g., adStateExecuting , -Closed )

!Name

shorthand for Parameters("name")

Execute

execute command/query

ELEMENTARY SQL COMMANDS

 

SELECT field1, field2

which fields

FROM table1, table2

from which tables

WHERE condition

under what conditions

GROUP BY field

how to group

ORDER BY field

how to sort

DELETE

command for deleting records

UPDATE

command for updating several records

INSERT INTO

command for inserting records into a table

SELECT INTO

command for creating a new table




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net