Данный материал посвящен созданию простого редактора 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.
И давайте сразу я приведу скриншот той программы, которую мы сегодня сделаем, в конечном итоге у нас получится что-то вроде этого:
Написание редактора 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
И она будет содержать данные, например, вот такие
Затем мы настраиваем adp проект на работу с нужной нам базой, открываем его в режиме конструктора, создаем форму. Далее мы добавляем на форму всего 4 элемента.
Первое, это, конечно же, «Поле» в котором мы и будем писать наши sql запросы, я его назвал myquery, учтите это, так как в коде будет использовано именно это имя.
Оформить данной поле можно как угодно, но один единственный момент я Вам посоветую, это изменить значение по умолчанию «Поведение по Enter» а именно на «Перевод строки». Это нужно для того, чтобы писать sql запросы, и в случае необходимости перевести строку на новую, путем простого нажатия на enter. Данный параметр меняется в:
Свойства->Другие->Поведение по Enter
Второе, это добавляем подчиненную форму, но, не выбирая при этом саму форму, т.е. добавляем пустой элемент «Подчиненная форма» и называем его «testform»
Третий и четвертый элемент это кнопки, одну из которых я назвал «Выполнить», а вторую «ExportEX». Они служат для запуска sql запроса и для выгрузки в Excel соответственно.
И теперь осталось написать код в обработчике события нажатие кнопки, а точнее двух кнопок, в общем, открывайте редактор кода 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, то соответственно выгрузить в него не получится.
Писать запросы можно как угодно с алиасами и без, просто со звездочкой, вот, например, пару примеров одного и того же запроса, разница только в название полей полученных данных.
Если нажать на кнопку «Выгрузить в Excel», то все данные экспортируются в Excel, вот в таком виде:
Вот и все, получился достаточно универсальный редактор запросов, который отлично подходит для написания SQL запросов и выполнение их на сервере, а также с отличным функционалом по выгрузки результатов запросов в формат Excel.
Если у Вас возникают какие либо проблемы с реализацией редактора запросов таким способом, то задавайте свои вопросы в комментариях, будем разбираться. На этом все, удачи!
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
