Access 7 VBA- SQL(2) Створення таблиці. Для створення таблиці використовується інструкція CREATE TABLECREATE [TEMPORARY] TABLE таблиця (поле_1 тип[(розмір] [NOT NULL] [індекс_1][, поле_2 тип [(розмір] [NOT NULL] [індекс_2][,…]]) NOT NULL. Означає, що відповідне поле обов’язково повинно містити значення. Пропозиція Constraint застосовується для створення, видалення та модифікації індексів, а також для створення зв’язків між таблицями. Constraint ім’я {Primary key| Unique|Not Null| References зовнішняТаблиця [(зовнішнєПоле_1, зовнішнєПоле_2)} Модифікація даних. Команда Update застосовується для одночасної зміни вмісту полів однієї чи кількох записів.Синтаксис: Update ім’яТаблиціSet ім’яПоля1=Значення1[,ім’яПоля2= Значення2] Where [пропозиція] Update Музика Set Альбом= Ucase(Альбом) Update Музика Set Видавець= ‘Colambia Records’ Where Видавець= ‘Colambia’ Видалення даних. Delete From ім’яТаблиці Where [пропозиція]Збережені процедури. З появою засобів ActiveX Data Objects арсенал програміста, що використовує Access, доповнився можливостями створення та застосування збережених процедур. Збережена процедура в SQL рівнозначна функції.Переваги збережених процедур :Процедури виконуються на серверах баз даних та дозволяють визначати інтерфейси. При запуску процедур на сервері, який, як правило більш потужний, ніж робоча станція, прикладна програма виконуватиметься набагато швидше. Цей вивід істинний, навіть, якщо Access 2002 працює на тому ж комп’ютері, що й ваш додаток, оскільки збережені процедури виконуються безпосередньо ядром Access. А іменовані процедури забезпечують настільки ж прості можливості звертання до збережених процедур, як і при використанні звичайних функцій. Ви передаєте значення аргументів, потім визначені вами операції виконуються та повертають потрібні результати.Процедури зберігаються в базі даних, в колекції Procedures, яка входить до складу об’єкту Catalog. Кожний елемент колекції Procedures – це об’єкт класу Procedure. Об’єкт Procedure містить атрибути DateCreated, DateModified, Name та Command. Command - – це об’єкт, що містить власне код тіла процедури.Синтаксис: PARAMETERS [Параметр1]Тип {,[Параметр2],Тип,…}; Текст SQL Після службового слова PARAMETERS іде список пар Параметр, Тип, що визначають інтерфейс процедури. Квадратні дужки є складовою частиною конмтрукції, а не ознакою необов’язковості синтаксичного елементу. Список параметрів завершується символом крапки з комою, після якого набирається текст тіла процедури на мові SQL.Додання збереженої процедури в каталог. Щоб збережена процедура стала доступною для використання, її слід додати до бази даних за допомогою властивостей та методів об’єктів ADODB.Command та ADOX. Catalog.Приклад додання збереженої процедури до бази даних. Sub CreateStoredProcedure() Dim Connection As ADODB.Connection Set Connection = CurrentProject.Connection Dim Command As New ADODB.Command Dim Catalog As New ADOX.Catalog Set Command.ActiveConnection = Connection Command.CommandText = "PARAMETERS [APublisher] TEXT;" & _ "SELECT ARTIST,TITLE, FORMAT," & _ "PUBLISHER FROM Music WHERE Publisher=[APublisher]" Set Catalog.ActiveConnection = Connection Call Catalog.Procedures.Append("Artist By Publisher", Command) Set Command = Nothing Set Catalog = Nothing Set Connection = Nothing End Sub Для розв ’язання цієї задачі створюється об’єкт Command, призначений для занесення до нього тексту збереженої процедури. В якості безпосереднього виконувача основних дій по збереженню процедури в базі даних виступає об’єкт класу Catalog . Далі послідовність символів, яка містить текст процедури присвоюється атрибуту CommandText об’єкту Command. Далі процедура додається до каталогу.Виконання збереженої процедури. Зараз, коли збережена процедура створена, ви можете звертатися до неї в будь-який момент по мірі необхідності. Щоб виконати процедури, слід використати метод Execute класу Command. Якщо збережена процедура повертає деякий набір даних (як в нашому випадку), потрібно присвоїти результат виконання метода Execute змінній класу ADODB.Recordset. Якщо збережена процедура виконує команди SQL (такі як Insert, Delete або Update), що повертає значення або наборів даних , то об’єкт RecordSet непотрібний.Приклад виконання збереженої процедури Sub ExecuteProcedure() Dim Connection As ADODB.Connection Set Connection = CurrentProject.Connection Dim Catalog As New ADOX.Catalog Set Catalog.ActiveConnection = Connection Dim Command As ADODB.Command Set Command = Catalog.Procedures("Artist By Publisher").Command Dim Music As ADODB.RecordSet Dim RecordsAffected As Long Command.Parameters("[APublisher]").Value = "Elektra" Set Music = Command.Execute() Music.MoveFirst Do While Music.EOF = False Debug.Print Music("Artist") Music.MoveNext Loop Music.Close Set Music = Nothing Set Command = Nothing Set Catalog = Nothing Set Connection = Nothing End Sub Метод Execute повертає набір даних,що являється результатом виконання запиту збереженої процедури.
Пошук записів. Існує два основних способи пошуку записів. Ви можете створити та застосувати запит мовою Access. Побудувати символьну строку, яка містить вираз мовою SQL, відкрити таблицю та використати циклічну VBA-процедуру, яка дозволяє відібрати потрібні записи.Команда SQL виконується сервером бази даних (найчастіше, найбільш потужним комп’ютером), тому продуктивність прикладної програми в цьому випадку дуде ймовірно більш високою. Але, код мовою VBA більш гнучкий. Попереднє створення запиту суттєво спрощує код.Приклад пошуку записів в наборі даних засобами процедури VBA Sub Test() Call FindRecords("Artist", "Queen") End Sub Sub FindRecords(ByVal SearchFieldName As String, _ ByVal FindValue As String) Dim Connection As ADODB.Connection On Error GoTo Finally Set Connection = CurrentProject.Connection Dim RecordSet As New ADODB.RecordSet Call RecordSet.Open("Music", Connection, adOpenKeyset, adLockOptimistic) RecordSet.MoveFirst Do While Not RecordSet.EOF If (RecordSet(SearchFieldName) = FindValue) Then Debug.Print RecordSet("Title") End If RecordSet.MoveNext Loop RecordSet.Close Finally: If (Err.Number <> 0) Then Call MsgBox(Err.Description) Err.Clear End If Set RecordSet = Nothing End Sub Пошук з використанням запиту Перевага використання запиту в тому, що він здатний повертати набори даних, що містять інформацію зразу з кількох таблиць, За допомогою запиту легко виконується попередня фільтрація полів та записів набору даних. Приклад Sub Test() Call FindRecords("Artist", "Queen") End Sub Sub FindRecords(ByVal SearchFieldName As String, _ ByVal FindValue As String) Dim Connection As ADODB.Connection On Error GoTo Finally Set Connection = CurrentProject.Connection Dim SQL As String SQL = "Select * FROM MUSIC WHERE " & SearchFieldName & _ "='" & FindValue & "'" Dim RecordSet As New ADODB.RecordSet Call RecordSet.Open(SQL, Connection, adOpenKeyset, adLockOptimistic) RecordSet.MoveFirst Do While Not RecordSet.EOF Debug.Print RecordSet(SearchFieldName) Debug.Print RecordSet("Title") RecordSet.MoveNext Loop RecordSet.Close Finally: If (Err.Number <> 0) Then Call MsgBox(Err.Description) Err.Clear End If Set RecordSet = Nothing End Sub Властивість Filter. Об’єкт класу RecordSet має корисну властивість Filter.RecordSet. Filter= SearchFieldName & "='" & FindValue &"'". Можна вставити перед відкриттям набору даних.
|