Подтвердить что ты не робот

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

MS Access имеет ограниченные возможности для управления сырыми SQL-запросами: редактор довольно плох, не выделяет синтаксис, он переформатирует ваш необработанный SQL в длинную строку и вы не можете вставлять комментарии.

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

Как вы управляете сложными SQL-запросами в MS Access и как их отлаживать?

Edit
На данный момент я в основном использую Notepad ++ для некоторой раскраски синтаксиса и SQL Pretty Printer для корректного переформатирования исходного SQL из Access.
Использование внешнего репозитория полезно, но при этом всегда существует риск того, что две версии не синхронизируются, и вам все равно придется удалять комментарии, прежде чем пытаться выполнить запрос в Access...

4b9b3361

ОТВЕТЫ

Ответ 1

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

Еще одна крупная PITA.

Ответ 2

У меня есть несколько советов, которые относятся к SQL в VBA.

Поместите свой SQL-код в строковую переменную. Я использовал это:

DoCmd.RunSQL "SELECT ..."

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

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

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

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. (Heck, StackOverflow делает лучше.) Он также эффективен для удаления Access cruft, как лишние ссылки на таблицы и груды скобок в предложении 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

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

Ответ 3

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

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

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

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

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

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

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

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

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

Ответ 4

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

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

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

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

Ответ 5

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

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

Ответ 6

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

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

Ответ 7

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

  • бесплатное управление SQL Server Studio Express, где я буду разрабатывать и тестировать мои запросы
  • таблица запросов на клиенте сторона, с одним полем для запроса имя (id_Query) и другое (queryText, тип memo) для сам запрос.

Затем у меня есть небольшая функция 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"

Ответ 8

Развернувшись на этом предположении от Смандоли:

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)

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

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

Ответ 9

Я думаю, что я не пишу сложный SQL, потому что большую часть времени у меня не проблема с редактором Access SQL. Это связано с тем, что, по большей части, я использую QBE для написания SQL и только погружаюсь в представление SQL, чтобы делать то, что QBE не поддерживает (например, объединения без equi, некоторые формы подзапросов, UNION и т.д..). Это не означает, что у меня нет SQL, с которым очень сложно работать, но в основном потому, что он СЛАВНО ПЛОХОЙ ПИСЬМО, и что я виноват, а не Access fault. У меня есть один ужасный, ужасающий сохраненный QueryDef в приложении, которое было в производстве с 1997 года, у которого есть SQL, что 11 934 символов. И, да, это ужасно для устранения неполадок. И почти любое редактирование, которое я ему делаю, что-то ломает. Но это потому, что IT BAD SQL.

Почему каждый хотел бы написать свой SQL вручную как общее правило, я не могу сказать. Для всего, кроме самого тривиального SQL, мне кажется больше неприятностей, чем того стоит.

Мне кажется, что это похоже на другой случай, когда люди сопротивляются стандарту Access Access. Почти всегда это происходит с пользователями, имеющими опыт в других средах программирования, которые слишком нетерпеливы, чтобы попробовать что-то, как Access делает их по умолчанию. Конечный результат обычно недоволен всем.