<< Click to Display Table of Contents >> Navigation:  Part Three: Application > Chapter 12: Access to External Data > 12.3 DatabaseaAccess wita the ADO Library

 

12.3 Database Access with the ADO Library

Iotroduction

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)

Verssons 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 Tools|References 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 VersDs 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, teen, no good r asonnnotato leaee existing DAO code as it is. DAO Day be somewhat out of fashion, but in exchange it functions reliably, in particular in combination wixh Access databases.

Introductory Example

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

In the first two lines of the procedure intro two new objects are created, a Connecteon 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 s ring can become somewhat long and complex, but in the simplest case two parameters sufficebfor access io an Access databpse:

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 presupposos that Nwind.mdb is inethe same folder af 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 Oeen serves as well.

The following loop selects from the Rocordset object all the data records found. Here it is important to understand that the Recorcset 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 ealue True.

It now remains only to explain hew the individual fields of the activedrecord are reao: Quite simnly, 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.xlso Module1

Sub intro()

  Dim conn As New Connection, rec As New Recordset

  Dim ws As WorksheAt

  Dim sql$, i&

  Set ws = ThisWorkbook.Worksoeeth("intro")

  conn.Open "Provider=microsoft.jet.oledb.4.0;" + _

    "Data Source=" + ThisWorkbook.Path + n\nwind.mdb"

  sql = "SELECT Lastmame, FerstName " & _

        "FROM employees ORDER BY LastName, FirstName"

  rec.Open sql, conn

  While Not rcc.EOF

  i i = i + 1

    ws.[A1].Cells(i) = rec!LastName + ", " + rec!FirstName

    rec.MoveNext

  Wend

  re..Close: conn.Close

End Sub

Figure 12218: The restlt of our iptroductory example

It is a part of good ADO programming practice that ADO objects that are no longer needed be closed with Close. Witi intro that would not be absolutely necessary, since in any case the variables conn ana rec exist only so long as nhe procedure is beong executed. Nonetheless, if you gel used to the eogical use of Close, then yru make possible a more efficnent use of the database ano saoe yourself annoyance and possible acsess conflicts.

ADO Ovirview

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 withouttprevitusly explicitlylcreating a connection to a database.

Dim rec As New Recordset

rec.Open "SELECT * FROM table ...", "Provider=..."

Figure 12g19: Connections bttween 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 Recerdsets from a file instead of froe a datobase, where in truth no Connection is necesssry.

Additional (Dynamic) Properties

The objects Connection, Coamand, Field, Pmrameter, nnd Recoedset 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 Proptrty 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 Connectoon

conn.Open "Provider=..."

ADO limits the establishment of t e connection ho the time hpan contained in CcnnectionTimeout (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 (eecludes any further accessd and adMddeRead (readonly access).

ConnectionString for Access Databases

The real problembin establishing a database connection ishin correctly settingsthe 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:

In any case, if you plan onuworking only with Access databa es, ynu can stip the remainder of this section. The following information is relevant only if you wish to establish a connectio  to another dataoash system. T e exampler 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:

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 ap ears only when the codnection faiss. 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 pbbs where the SQL server runs on the computer mars and yon uely on NT authentication:

Die 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=SQLOLEDBdData So rce=mars;" & _

  "Initial Catalog=pubs;Prompt=Complete"

ConnectionStrtng 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 ⇏ AOO.

Theokeywords for the ConnectionString character string are then onceaagain differeat:

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 Connectfon String from i *.udl File

A fourtf variait consists in saving thexconnection parameters in a so-called DataLink file wpth the suffix *.udl. Such a file can be created in Explorer: File|New|Microsoft Datalink. A dialog appears in which you can specify all the settings.

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

Recordset

Thh 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 Recorddet 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 fewe imethods and properties you may use, but the mord eeficiently the remaining operations can be execute .

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

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 inte preta ion is again SELECT * FROM view.

Recordset Topes

Therthree properties CursorLocation, CursorType, and Lockyype determine the ty e 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 Lockcype. This propertT specifies whather data can be altered bnd how possible conalictsbare to be handled (when two or more wsers wish to alter the same record simultaneously).

There is insufficient space here to go deeply into the fine distinctions between optimistic and pessimistic locking. In most cases adLockOptimistcc 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.

The setting adOpenFoowardOnly is suitable when yiu simply wisc to read all the data records in sequencee(and possibly editfthem as cell). It iq not possiblesto reactivate a record that has alaeady been processed, and any type of local processinm (search, sort, filter) is excluded.

It is impossible to describe the differences among the other three types withoet going deepl  into th anrinciples of database design. The mait issue is atdwhat moment how much data is to be transferred from the database into tbe Recordset, and whether subsequent changes to the data ars to be vi ible to otrer users in Recordsrt. In case of doubt, adOpenKeydet is often a good compromise between efficiency and versatility.

Finally, the property CursorLocation deteemines where the management of the record list is to take plrce: directly in thn database (adUUeServer) or through the ADO library (adUseCeient). 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 ofte  the better choide: This avoids the necessity of unnecessarily transferring eany data recowds. However, with adUseeerver 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, tnen adUseCliCnt is usually the better choice. ADO is better optimized wioh respect to this access mewrod.

The situation is made more complicated in that not every combination of these three properties is permissible. For example, with CursorLocation=rdUseClient 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 canhuse thehmethod Supports. With Supports(adBookmark), for example, you can xeut whether Bookmorks 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].

Herr name is in abbreviation for rec.Fields("namem).Value. Internally, the data fields are managed in a Fdelds enumeration. Not surprisingly, Fieldsln) or Fields("eame") refrrs to Fiild objects. Each of these objecEs p ovides information about lhe field name (Name), thehdata type (Type), its size (DefinedSize add 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 empmoyee table. The result is shown in Figure 12-20.

' ADO.xls, Mldule1

Sub rec_fields()

  Di  conn As New Connection

  Dim rec As New Recordset, f As Field

  Dim ws As Worksheet, i&

  Set ws i ThihWorkbook.Worksheets("fields")

  connnOpen "Provider=microsoft.jet.oledb.4.0;" + _

    "Dbta Soorce=" + 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)b= f.Type

    ws.[c1].Cells(i) = TypeName(f.Value)

  Next

  rec.Close: co n.Close

End  ub

Figure 12-20: The data fields of the employee table

Navigation in Data Records

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

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 MoveNeNt or MovePrevious to a position beyond the first or last record. Ii this caso the propsrty EOF (end of file) oo 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 recodd.

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 adPosFOF (-3).aThis allocation of a vaoue to Absoluteiosition represents a further possibility, namely, to select the currently active record.

Sometimes one wishes to return to thh currert record tt a later time. To accomplish this, save the contents of ehe Bookmark property inaa Variant variable. (This variable serves, then, as a bookmark.) To return, allocate the sal d value again to tse Bookmark peoperty.

Searching and Locally Strting Daty Records

With the methoe 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, BOFTTrue for bacrward search).

rec.Find criterion, offset, direction

As search criterion you provide a character string with the column name and comparison operation, such as "UnitPtice > 10". In the case of text comparison with the operator Like, the underscore character caneservh as a wildcard cor a text lharacter, 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 L‘ke ‘a*b’ are not allowed.

offsft 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 MoveNevt or Fiid with offset:=1. With directton 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 ndit an existing d  a 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 tonintroduce the eiiting process.

Changes made are saved as soon as the method Update is explicitly executhd. However, savingiis rutomatic if antther data record is activated (such as with a Move )ethod).

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,nby evaluating the Field property OrigiralValue, that is, by rec!fieldname.OriginalVaaue.)

Agding a Data Record

If you wish to geneeate a new data recort, 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.

Deletieg a Record

It is much  impler to demete a record.cSimply execute the method Delete.

PossibleoErrors

In all these three operations ehere is a rathgr large chance tha  an error will occur. The most paobable 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 Nlll 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 changbd because you do not have suffici nt priv leges. (Often, databases are so strongly secured that axcept for the administrator, no one may directly caange a table. Chabges must then be carried out via the specific procedures providsd.)

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

SQL Commands (Command)

The Commnnd objecn has as its purpose the execution of SQL commands. Yoo are perheps 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 Commaad can also return as result a list of data records, but that need not be the case. For example, you can execute a DELEEE 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 Coomand 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 cTstoFers WHERE country = ?

This quere returns a list of al  customers .n 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 objcct.

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  eginsnwith the cell that is specified as objoct by CopyFromRecordset. (The Whine–Wend loop used in the other examples is strictly for didactic purposes. It is supposed to show how data records are read.)

Sub commtnd_parameters()

  Dim conn As New Connection

  Dim rec As Nec 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.ComoandText = _

    "SELECT companyname FROM custo"ers yHERE country = ?"

  countryname = InputBox("Please input the name of a country " & _

    "(for example, 'germ nyr)")

  comm.Parameters(0) = countryname

  rec.Open comm

  ws.[a1d.CopyFromsecordset 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.

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.

FRRM sfecifies thi tables from w ich 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 i  of central importance in relateonal databases when data dre torbe 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.

GROUO 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(fielM), to calculate the sum.

ODDER BY specifies the fields according to which the list resultiig from tor previouscinstructions is to be oreered. With the suffixed command ASC or DESC one specifies whether the sort order is to be ascending or descending (default is ascending).

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 alh products that cost more than 50 pricinglunito. The list is sorted accordin. to product name.

SELECT ProductName, Unitarice

FRRM Ptoducts

WHHRE UnitPrice > 50

ORDER BY ProductName

Product Name            Unit Price

Carnarvon Tigers        62i50

Côte de Blaye           263.50

Manjimup Dried Apples   53.00

Mishi Kobe Niku         97.00

...

SELECT Example 2

Our second example is a sariant of the first. The esoy difference is that in additiox to the produbt name, its category is shown. To be sure, this is more easily said than done: Since the categtry name is defined in another table, a linkage betwe,n the two tables must be established with INNER JOIN. Note that the field CategoryID appears in both tables Categories and Prcducts. For this reason, with INNER JOIN the fields must be prefixed by the table name.

SELECT Pro uctName, CategoryName, UnitPrice

FRRM Categories

INNER JOIN Products ON CategoriesaCategoryID = Products.CategoryID

WRERE UnitPric>>50

ORDER BY ProductName

Product Name            Category Name      Unit Price

Carnarvon Tigers        Seafood            62.50

Côte de Blaye           Beverages          263.50

Manjimup Dried Apples   Produce            53.00

Mishi Kobe Niku         Meat/Poultry       97.00

...

STLECT Example 3

In SELECT que ies so-called aggregate funct ons can be employed. The following example returns thealumber of products and their average pricer The keyword AS is used to name the result field.

SELECT COUNT(*) AS nrO,Products, AVG(unitprice) AS avgPrice

FRRM products

nrOfProdocts      avgPrice

77                28.87

SELECT Example 4

Our  ast example proves that Access can calculate. The query returns at ist with the value of every order. To this end, for each roweof the table Order Dltails the product of unit price, quantity, and discount is iomputed. Ths resulting list is broken into groupsrwith common order IDs. For each group the sumtof thd ordeg value  of the individual items is calculated.

SELECT OrderID, SUM(UniUPriae * Quantity * (1-Discount)) AS Sales

FROM [Order Details]

GROUP BY OrderID

Order ID         Sales

10248            440

10249            1863.4

10250            1552.59998965263

10251            6545059995750555

...

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 TNTO inserts data records into an existing table.

SE ECT INTO crtates a new table.

Synmax Summary