Excel и SQL - VB

Формулировка задачи:

Вопро в cледующем... каким образом можно использовать язык SQL в VBA макросах... то есть.. как подсоедениться к книге... листу.. и диапазону и можно ли делать вставку, выборку, удаление.. где это можно почитать и желательно с примерами, может кто свои приведет.

Решение задачи: «Excel и SQL»

textual
                            Листинг программы 
                        

                            <font color="blue">Dim</font> cnn <font color="blue">As</font> ADODB.Connection
    <font color="blue">Dim</font> rst <font color="blue">As</font> ADODB.Recordset
    <font color="blue">Dim</font> strCnnString <font color="blue">As</font> <font color="blue">String</font>
    <font color="blue">Dim</font> strSource <font color="blue">As</font> <font color="blue">String</font>
    
    strSource = <font color="teal">"C:\MyBook.xls"</font>
    
    <font color="00AA00">' Строка подключения ODBC.</font>
    <font color="00AA00">'strCnnString = <font color="teal">"DRIVER={Microsoft Excel Driver (*.xls)};"</font> _</font>
    <font color="00AA00">'  & <font color="teal">"ReadOnly=1;DBQ="</font> & strSource</font>
    
    <font color="00AA00">' Строка подключение OLE DB.</font>
    strCnnString = <font color="teal">"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="</font> _
      & strSource & <font color="teal">";Extended Properties=Excel 8.0"</font>
    
    <font color="blue">Set</font> cnn = <font color="blue">New</font> ADODB.Connection
    
    cnn.<font color="blue">Open</font> strCnnString
    
    <font color="00AA00">' Используем метод Execute для создания набора записей.</font>
    <font color="00AA00">'Set rst = cnn.Execute(<font color="teal">"SELECT * FROM [Лист1$]"</font>)</font>
    
    <font color="00AA00">' Или для определенного диапазона.</font>
    <font color="00AA00">'Set rst = cnn.Execute(<font color="teal">"SELECT * FROM [Лист1$]A1:B10"</font>)</font>
    
    <font color="00AA00">'Do Until rst.EOF</font>
    <font color="00AA00">'    Debug.Print rst.Fields(<font color="teal">"Поле1"</font>) & <font color="teal">" "</font> & rst.Fields(<font color="teal">"Поле2"</font>)</font>
    <font color="00AA00">'    rst.MoveNext</font>
    <font color="00AA00">'Loop</font>

    <font color="00AA00">' Добавляем записи.</font>
    <font color="blue">Set</font> rst = <font color="blue">New</font> ADODB.Recordset
    
    <font color="blue">With</font> rst
        .CursorLocation = adUseClient
        .<font color="blue">Open</font> <font color="teal">"SELECT * FROM [Лист1$]"</font>, cnn, adOpenStatic, _
          adLockOptimistic
        .AddNew
        .Fields(<font color="teal">"Поле1"</font>) = <font color="darkblue"><b>123</b></font>
        .Fields(<font color="teal">"Поле2"</font>) = <font color="teal">"Test"</font>
        .Update
    <font color="blue">End</font> <font color="blue">With</font>
    
    <font color="00AA00">' Обновляем записи.</font>
    <font color="00AA00">'With rst</font>
    <font color="00AA00">'    .CursorLocation = adUseClient</font>
    <font color="00AA00">'    .Open <font color="teal">"SELECT * FROM [Лист1$] WHERE Поле2='Test'"</font>, cnn, _</font>
    <font color="00AA00">'      adOpenStatic, adLockOptimistic</font>
    <font color="00AA00">'    .Fields(<font color="teal">"Поле1"</font>) = 1234</font>
    <font color="00AA00">'    .Update</font>
    <font color="00AA00">'End With</font>

    <font color="00AA00">' По поводу удаления. Из справки MSDN.</font>
    <font color="00AA00">' You cannot:</font>
    <font color="00AA00">' 1. Delete an entire record at once or you receive the following error message:</font>
    <font color="00AA00">' Deleting data in a linked table is not supported by this ISAM.</font>
    <font color="00AA00">' You can only delete a record by blanking out the contents of each</font>
    <font color="00AA00">' individual field.</font>
    <font color="00AA00">' 2. Delete the value in a cell containing an Excel formula or you</font>
    <font color="00AA00">' receive the following error message:</font>
    <font color="00AA00">' Operation is not allowed in this context.</font>
    <font color="00AA00">' 3. You cannot delete the empty spreadsheet row(s) in which the deleted data</font>
    <font color="00AA00">' was located, and your recordset will continue to display empty</font>
    <font color="00AA00">' records corresponding to these empty rows.</font>
    
    rst.<font color="blue">Close</font>
    cnn.<font color="blue">Close</font>

    <font color="blue">Set</font> rst = <font color="blue">Nothing</font>
    <font color="blue">Set</font> cnn = <font color="blue">Nothing</font>
                        