Создаем редактор SQL запросов для SQL сервера на VBA Access 2003

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

И начать хотелось бы с того, для чего нам может понадобится такая программа. Допустим, что Вы программист SQL и у Вас реализована связка клиент-сервер, т.е. есть какое-то клиентское приложение и соответственно SQL сервер, также у Вас есть удаленные офисы, в которых сидят простые администраторы, которые следят за работой всех установленных сервисов и иногда им необходимо самим писать запросы и выгружать данные. Но Вы не хотите им устанавливать Management Studio или просто некоторые пользователи сами пишут простые запросы и им уж точно не нужна Management Studio. Или, например, Вы сами работаете в удаленном офисе и Вам руководство не разрешает устанавливать Management Studio или другое подобное ПО, в общем, у Вас существует необходимость пользоваться функционалом, с помощью которого Вы могли бы писать SQL запросы и выгружать, результаты этих запросов в формат Excel. Именно для таких целей отлично подойдет программа, которую мы сегодня напишем.

Примечание! Как Вы уже поняли, что в качестве СУБД мы будем использовать SQL сервер от Microsoft, а если конкретней, то MS SQL Server 2008 Express. Как его установить мы рассматривали в материале Установка MS SQL Server 2008 Express и Management Studio Express. А в качестве клиентского приложения будем использовать adp проект Access 2003, что он из себя представляет и как его создать мы рассматривали в статье Как создать и настроить ADP проект Access на работу с SQL сервером. Другими словами все ниже перечисленные действия подразумевают, что у Вас есть установленный sql сервер, и Вы умеете создавать adp проекты, а также имеете хоть какое то представление о Формах Access, VBA и SQL.

И давайте сразу я приведу скриншот той программы, которую мы сегодня сделаем, в конечном итоге у нас получится что-то вроде этого:

Скриншот 1

Написание редактора SQL запросов на VBA Access

И сразу хотелось бы рассказать, что за механизм мы будем использовать, он состоит в том, что мы будем, на основе sql запроса, динамически создавать форму на VBA с нужным количеством полей, и затем вставлять объект этой формы в подчиненную форму нашего редактора в табличном виде. Вот собственно и вся суть, а теперь давайте поговорим по подробней.

И для начала, допустим, что у нас на sql сервере есть тестовая база «test», если Вы не знаете как создать базу, то об этом мы разговаривали в материале Как создать базу данных в MS Sql 2008. Также у нас в ней есть таблица, допустим test_table, мы ее создадим вот так:

CREATE TABLE [dbo].[test_table](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [fio] [varchar](50) null,
        [dolg] [varchar](50) null,
        [otdel] [varchar](50) null,
        [summa] [float] null,
        [date] [datetime] null,
        [comment] [varchar](50) null,
) ON [PRIMARY]
GO

И она будет содержать данные, например, вот такие

Скриншот 2

Затем мы настраиваем adp проект на работу с нужной нам базой, открываем его в режиме конструктора, создаем форму. Далее мы добавляем на форму всего 4 элемента.

Первое это, конечно же «Поле» в котором мы и будем писать наши sql запросы, я его назвал myquery, учтите это, так как в коде будет использовано именно это имя.

Оформить данной поле можно как угодно, но один единственный момент я Вам посоветую, это изменить значение по умолчанию «Поведение по Enter» а именно на «Перевод строки». Это нужно, для того чтобы писать sql запросы, и в случае необходимости перевести строку на новую, путем простого нажатия на enter. Данный параметр меняется в:

Свойства->Другие->Поведение по Enter

Скриншот 3

Второе это добавляем подчиненную форму, но, не выбирая при этом саму форму, т.е. добавляем пустой элемент «Подчиненная форма» и называем его «testform»

Скриншот 4

Третий и четвертый элемент это кнопки, одну из которых я назвал «Выполнить», а вторую «ExportEX». Они служат для запуска sql запроса и для выгрузки в Excel соответственно.

Скриншот 5

Скриншот 6

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

Код VBA Access для программы «Редактор запросов»

   Option Compare Database
        'Обработка события нажатие кнопки «Выполнить»
        Private Sub Выполнить_Click()
        On Error GoTo Err_Выполнить_Click
        'Объявляем переменные
        Dim strSQL As String
        Dim rs As Recordset
        Dim getForm As Integer
        Dim newForm As Object
        Dim formNewName As String
        Dim fieldForm As Control
        Dim labelForm As Control
        'Получаем sql запрос
        strSQL = Me.myquery
        'Если хотите, то ставим ограничение на запуск только запросов select
        If Mid(strSQL, 1, 6) <> "select" Then
                MsgBox "Можно пускать только запросы на выборку (Select)"
                Exit Sub
        End If
        'Очищаем объект в подчиненной формой, 
        'для того чтобы мы ее могли удалить и заново создать
        Me.testform.SourceObject = ""
        'Получаем наши данные
        Set rs = New ADODB.Recordset
        rs.open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        'Отключим вывод на экран, для того чтобы 
        'мы ничего не видели в процессе выполнения
        DoCmd.Echo False
        'Проверяем, есть ли наша временная форма,
        'если есть, то закрываем ее и удаляем
        getForm = 0
        i = 0
        For i = 0 To CurrentProject.AllForms.count - 1
                If CurrentProject.AllForms.Item(i).NAME = "tmpForm" Then
                   getForm = 1
                   Exit For
                End If
        Next i
        If getForm = 1 Then
                DoCmd.close acForm, "tmpForm"
                DoCmd.DeleteObject acForm, "tmpForm"
        End If
        'Создаем нашу временную форму
        Set newForm = CreateForm(, "tmpForm")
            'Задаем ей источник данных, наш запрос
            newForm.RecordSource = strSQL
            'Выравнивание по центру
            newForm.AutoCenter = True
            'Включаем вывод в режиме таблицы
            newForm.DefaultView = 2
            'Запрещаем удаление, изменение и добавление данных
            newForm.AllowDeletions = False
            newForm.AllowEdits = False
            newForm.AllowAdditions = False
            'Запоминаем название нашей временной формы, 
            'она создается с названием по умолчанию, 
            'в дальнейшем мы ее переименуем
            formNewName = newForm.NAME
            'Циклом пробегаемся по всем полям в нашем запросе,
            'для их создания на временной форме 
            i = 0
            For i = 0 To rs.Fields.count - 1
             Set fieldForm = CreateControl(newForm.NAME, acTextBox, ,"", "d_" & i)
             Set labelForm = CreateControl(newForm.NAME, acLabel, , fieldForm.NAME, "n_" & i)
             labelForm.Caption = Replace(rs(i).NAME, "_", ".")
             fieldForm.ControlSource = rs(i).NAME
            Next i
        'Закрываем форму с сохранением
        DoCmd.close acForm, formNewName, acSaveYes
        'Переименовываем форму на tmpForm
        DoCmd.Rename "tmpForm", acForm, formNewName
        'Присваиваем нашей пока пустой подчиненной форме
        'объект форма, т.е. та которую мы только что создали
        Me.testform.SourceObject = "tmpForm"
        'Включаем обратно вывод всего на экран
        DoCmd.Echo True
        Exit_Выполнить_Click:
                Exit Sub
        Err_Выполнить_Click:
                MsgBox Err.Description
                Resume Exit_Выполнить_Click
        End Sub
        'Обработка события нажатие кнопки «Выгрузить в Excel»
        Private Sub ExportEX_Click()
        On Error GoTo Err_ExportEX_Click
                'Объявляем переменные
                Dim XL As Object
                Dim XLT As Object
                Dim rs As Recordset
        'Проверяем, выгрузили ли мы данные в форму
        If Me.testform.SourceObject = "" Then
                MsgBox "Отсутствуют данные для выгрузки"
                'Если нет, то завершаем процедуру
                Exit Sub
        End If
        'Копируем в rs Recordset нашей временной формы
        Set rs = Me.testform.Form.Recordset.Clone
        'Создаем объект Excel
        Set XL = CreateObject("Excel.Application")
        'Добавляем книгу
        Set XLT = XL.Workbooks.add
        'Циклом заполняем заголовки наших полей
        For i = 0 To rs.Fields.count - 1
          XLT.Worksheets("Лист1").Range("A1").offset(0, i) = rs.Fields.Item(i).NAME
        Next i
        'И просто перекидываем наш Recordset, на лист в Excel
        XLT.Worksheets("Лист1").Range("A1").offset(1, 0).CopyFromRecordset rs
        'Отображаем Excel 
        XL.Visible = True
        On Error Resume Next
        XL.UserControl = True
        Exit_ExportEX_Click:
                Exit Sub
        Err_ExportEX_Click:
                MsgBox Err.Description
                Resume Exit_ExportEX_Click
        End Sub

Вот в принципе и все, можете сохранять форму и попробовать написать SQL запрос и выгрузить его в Excel. Как Вы поняли по коду, мы разрешаем пускать только запросы Select все остальные (Insert, Update, Delete) выполнять нельзя, но в случае необходимости, Вы можете просто, закомментировать строки с проверкой на select и эти запросы можно будет выполнять.

Примечание! Данный способ запуска запросов возможен только в ADP проекте, если Вы используете ADE проект, то реализовать редактор запросов таким способом не удастся, так как в ade проекте запрещены любые изменения, в том числе удаление и создание форм. Также если у Вас на компьютере не установлен Excel, то соответственно выгрузить в него не получится.

Писать запросы можно как угодно с алиасами и без, просто со звездочкой, вот например пару примеров одного и того же запроса, разница только в название полей полученных данных.

Скриншот 7

Скриншот 8

Если нажать на кнопку «Выгрузить в Excel» то все данные экспортируются в Excel, вот в таком виде:

Скриншот 9

Вот и все получился достаточно универсальный редактор запросов, который отлично подходит для написания SQL запросов и выполнение их на сервере, а также с отличным функционалом по выгрузки результатов запросов в формат Excel.

Если у Вас возникают, какие либо проблемы с реализацией редактора запросов таким способом, то задавайте свои вопросы в комментариях, будем разбираться. На этом все, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.