Управление и отладка SQL-запросов в MS Access

Для отладки я редактирую их в отдельном текстовом редакторе, что позволяет мне разумно их форматировать. Когда я обнаруживаю, что мне нужно внести изменения, я редактирую версию в текстовом редакторе и вставляю ее обратно в Access, никогда не редактируя версию в Access.

По-прежнему крупный PITA.

Если вы выполняете действительно сложные запросы в MS Access, я бы подумал о том, чтобы сохранить репозиторий этих запросов где-нибудь за пределами самой базы данных Access ... например, в файле .sql, который вы затем можете редактировать в редакторе, таком как Intype, который обеспечит подсветку синтаксиса. Вам потребуется обновить запросы в обоих местах, но вы можете в конечном итоге найти для себя удобное «официальное» место для этого, которое будет правильно отформатировано и выделено.

Или, если это вообще возможно, переключитесь на SQL Server 2005 Express Edition, который также является бесплатным и предоставит вам желаемые функции через SQL Management Studio (также бесплатно).

Подобно рекурсивный,, я использую внешний редактор для написания своих запросов. Я использую Notepad ++ с расширением Light Explorer для поддержки нескольких скриптов одновременно и Notepad2 для разовых скриптов. (Я неравнодушен к редакторам на основе Scintilla.)

Другой вариант - использовать бесплатную SQL Server Management Studio Express, которая поставляется с SQL Server Express. (Обновлено: извините, Эдгар Верона, я не заметил, что вы уже упомянули об этом!) Я обычно использую его для написания SQL-запросов вместо использования Access, потому что я обычно использую ODBC для связи с серверной частью SQL Server в любом случае. Помните, что различия в синтаксисе T-SQL, используемого SQL Server, и Jet SQL, используемого Access MDB, иногда существенны.

Отладка - это более сложная задача. Если один столбец отключен, это обычно довольно легко исправить. Но я предполагаю, что у вас есть более сложные задачи по отладке, которые вам нужно выполнить.

Когда я сбит с толку, я обычно начинаю отладку с предложения FROM. Я прослеживаю все таблицы и подзапросы, составляющие более крупный запрос, и убеждаюсь, что соединения определены правильно.

Затем я проверяю свой пункт WHERE. Я выполняю множество простых запросов к таблицам и подзапросам, которые я уже проверил или которым я уже доверяю, и убеждаюсь, что когда я запускаю более крупный запрос, я получаю то, что ожидаю, с условиями WHERE. на месте. Одновременно я перепроверяю условия JOIN.

Я дважды проверяю определения столбцов, чтобы убедиться, что получаю то, что действительно хочу видеть, особенно если используемые формулы сложны. Если у вас есть что-то сложное, например, скоординированный подзапрос в определении столбца

Затем я проверяю, правильно ли я группирую данные, убеждаясь, что «DISTINCT» и «UNION» без UNION ALL не удаляют необходимые дубликаты.

Не думаю, что когда-либо сталкивался с SQL-запросом, который нельзя было бы разбить таким образом. Я не всегда так методичен, как этот, но это хороший способ начать ломать голову.

При написании запросов я мог бы порекомендовать следующее: Никогда не используйте SELECT * в производственном коде. Выбор всех столбцов таким образом - кошмар обслуживания, и он приводит к большим проблемам при изменении ваших базовых схем. Вы всегда должны записывать каждый столбец, если вы пишете код SQL, который вы будете поддерживать в будущем. Я сэкономил много времени и волнений, просто избавившись от "SELECT *" в своих проектах.

Обратной стороной этого является то, что эти дополнительные столбцы не будут автоматически отображаться в запросах, относящихся к запросам «SELECT *». Но вы все равно должны знать, как ваши запросы связаны друг с другом, и если вам нужны дополнительные столбцы, вы можете вернуться и добавить их.

Есть некоторые хлопоты, связанные с поддержанием репозитория кода, но если у вас есть программное обеспечение для управления версиями, эти хлопоты более чем того стоят. Я слышал о способах управления версиями кода SQL, написанного в базах данных Access, но, к сожалению, никогда не использовал их.

Вы говорите здесь о том, что MS-Access называет «запросами», а SQL вызывает «представления», или о запросах «сквозного доступа MS-Access», которые являются SQL-запросами? Кто-то мог легко заблудиться! Мое решение следующее

1. бесплатное управление SQL Server Студия Экспресс, где я буду разработать и протестировать мои запросы
2. таблица запросов на клиенте сторона, с одним полем для запроса имя (id_Query) и еще одно (queryText, тип памятки) для сам запрос.

Затем у меня есть небольшая функция getSQLQuery в моем коде VBA, которая будет использоваться, когда мне нужно выполнить запрос (либо вернуть набор записей, либо нет):

Dim myQuery as string, _
    rsADO as ADODB.recorset

rsADO = new ADODB.recordset
myQuery = getSQLQuery(myId_Query)

'if my query retunrs a recordset'
set rsADO = myADOConnection.Execute myQuery
'or, if no recordset is to be returned'
myADOConnection.Execute myQuery

Что касается представлений, их можно даже сохранить на стороне сервера и ссылаться на них со стороны клиента.

set rsADO = myADOConnection.execute "dbo.myViewName"

У меня есть несколько советов, относящихся к SQL в VBA.

Поместите свой код SQL со строковой переменной. Раньше я так делал:

DoCmd.RunSQL "SELECT ..."

С этим трудно справиться. Вместо этого сделайте это:

strSQL = "SELECT ..."
DoCmd.RunSQL strSQL

Часто вы не можете исправить запрос, если не видите, что именно выполняется. Для этого выгрузите свой SQL в окно немедленного выполнения непосредственно перед выполнением:

strSQL = "SELECT ..."
Debug.Print strSQL
Stop
DoCmd.RunSQL strSQL

Вставьте результат в стандартный построитель запросов Access (необходимо использовать Представление SQL). Теперь вы можете протестировать окончательную версию, включая переменные, обрабатываемые кодом.

Когда вы готовите длинный запрос в виде строки, разбейте свой код:

strSQL = "SELECT wazzle FROM bamsploot" _
      & vbCrLf & "WHERE plumsnooker = 0"

Я впервые научился использовать vbCrLf, когда хотел придать пользователю красивый вид длинных сообщений. Позже я обнаружил, что он делает SQL более читабельным при кодировании и улучшает вывод Debug.Print. (Другое небольшое преимущество: не требуется места в конце каждой строки. Синтаксис новой строки учитывает это.)

(ПРИМЕЧАНИЕ: вы можете подумать, что это позволит вам добавлять комментарии справа от строк SQL. Будьте готовы к разочарованию.)

Как уже говорилось в другом месте, посещение текстового редактора позволяет сэкономить время. Некоторые текстовые редакторы обеспечивают лучшую подсветку синтаксиса, чем официальный редактор VBA. (Черт возьми, StackOverflow работает лучше.) Он также эффективен для удаления мусора Access, такого как лишние ссылки на таблицы и груды скобок в предложении WHERE.

Порядок действий при устранении серьезных неисправностей:

VBA Debug.Print >       (capture query during code operation)
  query builder   >     (testing lab to find issues)
     Notepad++      >   (text editor for clean-up and review)
  query builder   >     (checking, troubleshooting) 
VBA

Конечно, устранение неполадок обычно сводится к уменьшению сложности запроса до тех пор, пока вы не сможете изолировать проблему (или, по крайней мере, заставить ее исчезнуть!). Затем вы можете создать из него шедевр, который вы хотели. Поскольку для решения «липкой» проблемы может потребоваться несколько циклов, вы, вероятно, будете использовать этот рабочий процесс неоднократно.

Расширяя это предложение Smandoli:

NO:   DoCmd.RunSQL ("SELECT ...")
YES:  strSQL = "SELECT ..."
      DoCmd.RunSQL (strSQL)

Если вы хотите сохранить код SQL во внешнем файле для редактирования в вашем любимом текстовом редакторе (с раскраской синтаксиса и всем остальным), вы можете сделать что-то вроде этого псевдокода:

// On initialization:
global strSQL
f = open("strSQL.sql")
strSQL = read_all(f)
close(f)

// To to the select:
DoCmd.RunSQL(strSQL)

Это может быть немного неуклюже - может быть, очень неуклюже - но позволяет избежать проблем с согласованностью редактирования-копирования-вставки.

Очевидно, что это напрямую не касается отладки SQL, но управление кодом в удобочитаемом виде является частью проблемы.

Я написал Доступ к редактору SQL - надстройку для Microsoft Access - потому что я пишу довольно много сквозных запросов и более сложный SQL в Access. Преимущество этой надстройки заключается в том, что она позволяет хранить форматированный SQL (с комментариями!) В самом приложении Access. Когда запросы копируются в новое приложение Access, форматирование сохраняется. Когда встроенный редактор искажает ваше форматирование, инструмент покажет ваш исходный запрос и уведомит вас о различиях.

В настоящее время отладка не выполняется; если бы был достаточный интерес, я бы продолжил это - но пока набор функций намеренно ограничен.

На данный момент это не бесплатно, но лицензия покупка стоит очень дешево. Если вы не можете себе этого позволить, вы можете свяжитесь со мной. Есть бесплатная 14-дневная пробная версия здесь.

После его установки вы можете получить к нему доступ через меню надстроек (в Access 2010 это Инструменты для баз данных-> Надстройки).

Насколько мне известно, есть 2 варианта:

• Notepad ++ с плагином для форматирования t-sql для бедняков. Я знаю, что уже упоминается о SQL Pretty Printer, но я не использовал его ... так что мой рабочий процесс ... я создаю запрос в Access ... я копирую, вставляю его в Блокнот ++ ... я форматирую его ... я работаю над ним ... обратно в Access..only issue..it в некоторых случаях пробелы в этом случае: [Forms]! [AForm]. [Ctrl] и они становятся [ Формы]! [AForm]. [Ctrl] но я привык и меня это не беспокоит ..
• SoftTree SQL Assistant (http://www.softtreetech.com/sqlassist/index.htm) обеспечивает практически все, что вы хотели, в редакторе SQL ... Я немного работал в прошлом (пробная версия), но его цена немного жесткая