Свойства и методы набора записей (курсора).
Методы объекта позволяют перемещаться по набору записей: MoveFirst, MoveLast, MoveNext, Move Previous.
Обнаружение начала и конца набора выполняется при помощи двух свойств:EOF, BOF. EOF устанавливается в true после прочтения последней записи или перемещения за последнюю запись. При попытке переместиться назад при нахождении курсора на первой записи возникает состояние BOF.
Для набора с неизвестным количеством записей (свойство recordCount = -1) подходит условный цикл вида
Do While Not adors.EOF
‘ работа с текущей строкой
Loop
Набор записей можно отсортировать (упорядочить). Для этого надо установить значение для свойства Sort равному имени одного или нескольких полей через запятую. Переход на первую запись в указанной сортировке осуществляется автоматически.
adors.Sort = "[вид_доставки]"
На записи, к которой вам надо будет вернуться позже, можно поставить закладку.
bm = adors.Bookmark
…
adors.Bookmark = bm
Можно искать запись по строковому выражению, составленному из имен полей, операций сравнения, логических операций. Строковые константы в строке поиска можно заключать в одинарные кавычки или в символы #, константы типа «дата» заключаются в символы #.
adors.Find "[ вид_доставки] = 'автотранспорт'"
Для наборов, расположенных на сервере можно применить метод Seek. Кстати, набор может быть определен как расположенный на сервере, хотя свойство CursorLocation для Connection может быть задано adUseClient. Еще одно обязательное требование – указание параметра метода Open Options:=adCmdTableDirect. Если не указать этот параметр, то провайдер посчитает, что необходимо создать запрос, получающий все записи таблицы, а не установить связь непосредственно с таблицей. А для временного набора записей, каковым является результат SQL-запроса, не существует индексов. Метод Seek – тоже поиск записи, как и метод Find. Но поиск осуществляется по значению ключа с применением индексного файла. Такой поиск работает значительно быстрее последовательного поиска, осуществляемого методом Find. Второй параметр метода Seek задает направление поиска от текущей записи.
Перед выполнением перехода необходимо установить индекс - свойство набора Index. Получить имя индекса можно из свойства объекта Catalog.
adors.CursorLocation = adUseServer
adors.CursorType = adOpenKeyset
adors.Open "вид_доставки", Options:=adCmdTableDirect
adors.Index = adocat.Tables("вид_доставки").Indexes(0).Name
adors.Seek Array("автотранспорт"), adSeekFirstEQ
Набор можно отфильтровать, удаляя ненужные записи. Строка для фильтра – условие для оставляемых записей.
adors.Filter = " = 'автотранспорт'"
В набор можно добавить запись методом AddNew. Если есть поле, для которого задан автоинкремент, то его не указывают в списке полей. Для ключевых неавтоинкрементарных полей обязательно указывать значения. Для остальных – не обязательно. Параметры метода AddNew: первый – массив имен или номеров полей, второй – массив значений. Важно задавать массивы согласованно.
adors.AddNew Array("вид_доставки ", "newcol"), Array("пешком", "113")
Методом Open можно открыть непараметрический запрос. Если запрос параметрический, чтобы его выполнить, следует обратиться к методу Execute объекта Command, связанного с объектом Connection. На месте параметра в тексте запроса пишется вопросительный знак. Параметр метода Execute – массив значений параметров запроса.
Dim cm As ADODB.Command
Set cm = New ADODB.Command
cm.ActiveConnection = adoconn
cm.CommandText = _
"Select * from вид_доставки where вид_доставки= ?"
cm.CommandType = adCmdText
Set adors = cm.Execute(Parameters:=Array("автотранспорт"))
Полученный в результате запроса набор записей можно сохранить в виде xml-файла в кодировке UTF-8.
adors.Save "L:/new_zapros1.xml", adPersistXML
' Set objRecordset = objConn.Execute("SELECT ФиоАвтора,НазваниеКниги FROM Книга")
' без SQL, (указываем таблицу, запрос и др...)
Set objRecordset = objConn.Execute("Книга, Автор") '- в данном сучае две таблицы
Do While Not objRecordset.EOF
Cells(i, 1) = objRecordset.Fields("автор.ФИОАвтора").Value
Cells(i, 2) = objRecordset.Fields("Названиекниги").Value
objRecordset.MoveNext 'переход на следующую запись
Loop
' работа с транзакциями
' Транзакция - это операция или несколько операций, которые либо выполняются полностью без ошибок, либо не выполняются вовсе
' (т.е. происходит откат системы до состояния начала транзакции)
' так же танзакции могут быть вложенными. Т.е разделяться по уровням.
' Транзакция "1" указывает, что вы открыли транзакцию верхнего уровня (то есть транзакция не вложена в пределах другой транзакции),
' Транзакция уровня "2" - транзакцию второго уровня (транзакция, вложенная в пределах транзакции верхнего уровня), и т.д.
' пржде чем откатить или закрыть т-ию первого уровня. необходимо откатить или закрыть транзакцию вторго уровня
' и т.д
' задача: на листе Екселя хранятся таблицы, которые необходимо перенести в Access/
' Но тут вдруг составитель таблиц обнаружил ошибки (например, секретарь, программист..... или директор увидел что работники все сделали и решил заставить работать, удалив некоторые данные)
' пометив ячейки с ошибками крсным цветом
' требуется созадть таблицы в Accessе из Екселя, учитывая, что таблицы с неверными данными создавать не надо
'
' так же не ообходимо установить метку, что базу данных можно изменять.... смотри параметры WillExecute(Source, CursorType, LockType, Options, adStatus, pCommand, pRecordset, pConnection))
' определимся с расположением таблиц в Ексле. Пусть таблицы располагаются друг под другом, разделенные пустой строкой. Сперва название таблицы, на следующей строке название столбцов
' очевидно, что надо пройти по всем строкам в Екселе. все - это сколько? когда остановиться? когда будут две подряд пустые
' Нашли пустую строку, следующая - это название таблицы. за тем строка с названием столбцов, затем строки таблицы
' Сперва подумаем над алгоритмом решения....
'0) организовать хранения таблиц на листе Екселя
'1) подключиться к БД
'2) Разобраться с добавлением таблиц
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' подсказка: создание таблицы в Access из Екселя
' Dim objConn As ADODB.Connection
' Dim objRecordset As ADODB.Recordset
' Set objConn = CreateObject("ADODB.Connection")
' Set objRecordset = objConn.Execute("CREATE TABLE newTable (newColumn INT PRIMARY KEY)")
' где в этой строчке тип данных? как описать несколько полей(столбцов)? какой столбец ключевой?
' как обозначаться будут типы данных: числовой, дата, текстовый, денежный?
' создать можно только одно ключевое поле
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'3) Для решения задачи будет три основных цикла: проход по таблицам, проход по строчкам таблицы и проход по столбцам таблицы
'4) Для каждой таблицы необходимо составлять SQL команду, наподобии: "CREATE TABLE ИмяТаблицы (имяКолонки1 ТипДанных1, имяКолонки2 типДанных2)"
' чтобы записать SQL запрос в строку необходимо разбить эту строку на меняющиеся части и постоянные.
' т.т строка SQL состоит из команды "CREATE TABLE " и и меняющейся части(ИмяТаблицы имяКолонки ТипДанных).
' Все ли известно? ИмяТаблицы получаем? если нет смотри пункт 3. ИмяКолонки получаем аналогично как и имя таблицы.
'
' Поразмышляли, вот теперь ближе к делу.
' Алгоритм решения простой: мы передвигаемся по строчкам екселя вниз пока не закончатся таблицы. Как определить конец?
' Что содержит строка, идущая после таблицы? А что будет содержать вторая строка после таблицы?
' Т.е. как тлько увидим две пустые строки значит это конец таблицам.(не забываем, что проверяем по первому столбцу)
'5) чтобы определить тип поля, можно записать макрос.Например, во время записи макроса изменить выделенным ячейкам формат данных
' из полученного макроса можно получить значения того, как Ексель представляет типы. Например, формат для даты получим "dd/mm/yy;@"
'6) Транзакция характеризуется началом и концом:
'
' Dim objConn As ADODB.Connection
' Set objConn = CreateObject("ADODB.Connection")
' ....
'
' objConn.BeginTrans 'начало ТА
' ......
' objConn.RollbackTrans 'откат транзакции
' ......
' objConn.CommitTrans 'конец ТАНЗАКЦИи
' внутри каждой транзакции могут быть свои транзакции, так называемые ТА нижнего уровня.
' Нельзя завершить ТА верхн ' Dim objRecordset As ADODB.Recordset
его уровня, если не завершена ТА нижнего уровня. Аналогично матрешке.....
' Dim objRecordset As ADODB.Recordset
Sub создатьБД()
' Dim objRecordset As ADODB.Recordset
Dim objConn As ADODB.Connection
Dim objRecordset As ADODB.Recordset
Set objConn = CreateObject("ADODB.Connection")
'теперь необходимо определить ConnectString (параметры подключения к БД или к кому - либо объекту)
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"
objConn.ConnectionString = ConnectString
objConn.ConnectionTimeout = 15
objConn.CommandTimeout = 30
objConn.Open
Dim nstr As Integer
Dim nstolb As Integer
Dim nameFields(30) As String
nstr = 1
Do While Cells(nstr, 1) <> "" And Cells(nstr + 1, 1) <> ""
NameTBL = Cells(nstr, 1) 'имя таблицы
nstr = nstr + 1
' определим количество СТОЛБЦОВ И ИХ ТИПЫ(тип возьмем из ниже лежащей ячейки), и запишем их в строку
nstolb = 1
NameStolbs = ""
Do While Cells(nstr, nstolb) <> ""
nameFields(nstolb) = Cells(nstr, nstolb)
NameStolbs = NameStolbs & Cells(nstr, nstolb) & " "
Select Case Cells(nstr + 1, nstolb).NumberFormat
Case "dd/mm/yy;@" ' дата
NameStolbs = NameStolbs & "date, "
Case 0
NameStolbs = NameStolbs & "int, "
Case "#,##0.00$" '- денежный формат
NameStolbs = NameStolbs & "money, "
Case "@" '- текстовый
NameStolbs = NameStolbs & "string, "
End Select
nstolb = nstolb + 1
Loop
nstr = nstr + 1
NameStolbs = "(" & Left(NameStolbs, Len(NameStolbs) - 2) & ")"
'НАЧИНАЕМ ТРАНЗАКЦИЮ
otkat = False
objConn.BeginTrans
' создаем таблицу
Set objRecordset = objConn.Execute("CREATE TABLE " & NameTBL & NameStolbs)
' рекордсету необходимо задать тип доступа смотри описание (' WillExecute(Source, CursorType, LockType, Options, adStatus, pCommand, pRecordset, pConnection))
objRecordset.Open NameTBL, objConn, adOpenDynamic, adLockPessimistic
' начинаем добавление записей (можно в строчку, тогда Update не требуется, objRecordset.AddNew "newColumn" = 55654)
' теперь проходимся по всем столбцам и строкам новой таблицы
Do While Cells(nstr, 1) <> ""
objRecordset.AddNew
For i = 1 To nstolb - 1
objRecordset.Fields(nameFields(i)) = Cells(nstr, i)
' если есть заливка, тогда ставим метку на откат
If Cells(nstr, i).Interior.Pattern <> xlNone Then
otkat = True
End If
Next
objRecordset.Update
nstr = nstr + 1
Loop
' в этом месте закончили с одной таблицей
If otkat Then ' если произошел откат
objConn.RollbackTrans
Else
'ЗАКАНЧИВАЕМ ТАНЗАКЦИЮ
objConn.CommitTrans
End If
Set objRecordset = Nothing
nstr = nstr + 1
Loop
Set objRecordset = Nothing
objConn.Close
Set objConn = Nothing
End Sub