Вызов отчетов, сформированных в базе данных

Свойства и методы набора записей (курсора).

Методы объекта позволяют перемещаться по набору записей: 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

Технология ADO предоставляет объекты и методы работы с данными, т.е. с таблицами, запросами. Отчеты – это показ данных. Поэтому ADO не дает возможности это сделать. У COM объекта Access.Application есть соответствующий метод. Но этот подход возможен только, если на компьютере установлен MS Access.

Dim abd As Access.Application

Set abd = CreateObject("Access.Application")

abd.OpenCurrentDatabase "i:\BD\БД_Книж_издат_V98.mdb"

abd.DoCmd.OpenReport "Книга", acViewPreview

Технология ADO предоставляет доступ к данным. Интерфейсная часть, т.е. входные и выходные формы вы должны составить в клиентском (интерфейсном) приложении.

Дата добавления: 2013-12-12