Управление и отладка SQL-запросов в MS Access. Запрос в access в sql
Управление и отладка SQL-запросов в MS Access Безопасный SQL
MS Access имеет ограниченные возможности для управления сырыми SQL-запросами: редактор довольно плох, не выделяет синтаксис, он переформатирует ваш необработанный SQL в длинную строку, и вы не можете вставлять комментарии.
Отладка сложных SQL-запросов также является болью: либо вы должны разбить ее на множество небольших запросов, которые становятся труднодоступными, когда ваша схема изменяется, либо вы получаете гигантский запрос, который является кошмаром для отладки и обновления.
Как вы управляете сложными SQL-запросами в MS Access и как их отлаживать?
редактировать На данный момент я в основном использую Notepad ++ для некоторой синтаксической раскраски и SQL Pretty Printer для переформатирования разумного исходного SQL из Access. Использование внешнего репозитория полезно, но при этом всегда существует риск того, что две версии не синхронизируются, и вам все равно придется удалять комментарии, прежде чем пытаться выполнить запрос в Access …
Для отладки я редактирую их в отдельном текстовом редакторе, который позволяет отформатировать их разумно. Когда я нахожу, что мне нужно внести изменения, я редактирую версию в текстовом редакторе и вставляю ее обратно в Access, не редактируя версию в Access.
Еще одна крупная ПИТА.
У меня есть несколько советов, которые относятся к 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"(ПРИМЕЧАНИЕ. Возможно, вы подумаете, что это позволит добавлять комментарии справа от строк 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Конечно, устранение проблем обычно связано с уменьшением сложности запроса, пока вы не сможете изолировать проблему (или, по крайней мере, заставить ее исчезнуть!). Тогда вы можете построить его обратно до желаемого шедевра. Поскольку для решения липкой проблемы может потребоваться несколько циклов, вы, вероятно, будете использовать этот рабочий поток повторно.
Отладка более сложна. Если один столбец выключен, это обычно довольно легко исправить. Но я предполагаю, что у вас есть более сложные задачи отладки, которые вам нужно выполнить.
Когда flummoxed, я обычно начинаю отладку с предложением FROM . Я возвращаюсь ко всем таблицам и подзапросам, которые содержат более крупный запрос, и убедитесь, что соединения правильно определены.
Затем я проверяю WHERE . Я запускаю множество простых запросов в таблицах и в подзапросах, которые я уже проверил, или что я уже доверяю, и убедитесь, что, когда я запускаю более крупный запрос, я получаю то, что ожидаю с условиями WHERE на месте. Я одновременно проверяю условия JOIN .
Я дважды проверю определения столбцов, чтобы убедиться, что я извлекаю то, что я действительно хочу видеть, особенно если сложные формулы сложны. Если у вас есть что-то сложное, как скоординированный подзапрос в определении столбца
Затем я проверю, правильно ли я группирую данные, убедившись, что « DISTINCT » и « UNION » без UNION ALL не удаляют необходимые дубликаты.
Я не думаю, что когда-либо сталкивался с SQL-запросом, который не мог быть разбит таким образом. Я не всегда так методичен, как это, но это хороший способ начать разрушать настоящий камень.
Одна вещь, которую я мог бы порекомендовать при написании ваших запросов, заключается в следующем: никогда не используйте SELECT * в производственном коде. Таким образом, выбор всех столбцов – это кошмар для обслуживания, и это приводит к большим проблемам при изменении основных схем. Вы всегда должны выписывать каждый столбец, если вы пишете SQL-код, который вы будете поддерживать в будущем. Я сэкономил много времени и беспокоился, просто избавившись от « SELECT * » в своих проектах.
Недостатком этого является то, что эти дополнительные столбцы не будут отображаться автоматически в запросах, которые относятся к запросам « SELECT * ». Но вы должны знать, как ваши запросы связаны друг с другом, так или иначе, и если вам нужны дополнительные столбцы, вы можете вернуться и добавить их.
Существует некоторая проблема, связанная с поддержкой репозитория кода, но если у вас есть программное обеспечение для управления версиями, хлопот более чем стоит. Я слышал о версиях кода SQL, написанных в базе данных Access, но, к сожалению, я их никогда не использовал.
Я написал Access SQL Editor – надстройку для Microsoft Access – потому что я пишу довольно много сквозных запросов и более сложный SQL в Access. Преимущество этой надстройки состоит в возможности хранить форматированный SQL (с комментариями!) В самом приложении Access. Когда запросы копируются в новое приложение Access, форматирование сохраняется. Когда встроенный редактор сжимает ваше форматирование, инструмент покажет ваш исходный запрос и уведомит вас об этой разнице.
В настоящее время он не отлаживает; если бы хватило интереса, я бы преследовал это … но пока набор функций намеренно невелик.
Пока это не бесплатно, но покупка лицензии очень дешевая. Если вы не можете позволить себе это, вы можете связаться со мной . Существует бесплатная 14-дневная пробная версия здесь .
После его установки вы можете получить доступ к нему через меню надстроек (в Access 2010 – это инструменты базы данных -> Add Ins).
Если вы выполняете действительно сложные запросы в 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. (EDIT: Извините, ЭдгарВерона , я не заметил, что вы уже упомянули об этом!) Обычно я использую его для написания SQL-запросов вместо использования Access, потому что я обычно использую ODBC для ссылки на конец SQL Server. Помните, что различия в синтаксисе T-SQL, используемого SQL Server, и Jet SQL, используемые Access MDB, иногда существенны.
Вы говорите здесь о том, что MS-Access вызывает «запросы» и SQL-вызовы «views» или о запросах «сквозного доступа MS-Access», которые являются SQL-запросами? Кто-то может легко потеряться! Мое решение следующее
- бесплатный SQL Server Management Studio Express, где я буду разрабатывать и тестировать свои запросы
- таблица запросов на стороне клиента, одно поле для имени запроса ( id_Query ) и другое ( queryText , memo type) для самого запроса.
Затем у меня есть небольшая функция 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Расширение этого предложения от Смандоли:
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, но управление кодом в читаемом виде является частью проблемы.
Я думаю, что я не пишу сложный SQL, потому что большую часть времени у меня не проблема с редактором Access SQL. Это связано с тем, что, по большей части, я использую QBE для написания SQL и только погружаюсь в представление SQL, чтобы делать то, что QBE не поддерживает (например, объединения без equi, некоторые формы подзапросов, UNION и т. Д. .). Это не означает, что у меня нет SQL, с которым очень сложно работать, но это в основном из-за того, что он СЧАСТЛИВЫЙ ПЯТЬ ПИСЬМО, и это моя ошибка, а не ошибка Access. У меня есть один ужасный, ужасающий сохраненный QueryDef в приложении, которое было в производстве с 1997 года, у которого есть SQL, который составляет 11 934 символа. И, да, это ужасно для устранения неполадок. И почти любое редактирование, которое я ему делаю, что-то ломает. Но это из-за того, что IT'S BAD SQL.
Почему каждый хотел бы написать свой SQL вручную как общее правило, я не могу сказать. Для всего, кроме самого тривиального SQL, мне кажется больше неприятностей, чем того стоит.
Мне кажется, что это похоже на другой случай, когда люди сопротивляются способу доступа по умолчанию. Почти всегда это происходит с пользователями, имеющими опыт в других средах программирования, которые слишком нетерпеливы, чтобы попробовать что-то, как Access делает их по умолчанию. Конечный результат, как правило, несчастлив для всех.
sql.fliplinux.com
sql - Форматирование SQL-запросов в MS Access
Для чего это стоит, я могу повторить ваше сообщение об ошибке, если объявить параметр и не назначать его:
strSQL = "parameters [CID] number; " & _ "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " ORDER BY MaturityDate" Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)Дает мне то же самое, о чем вы сообщаете:
Слишком мало параметров. Ожидаемый 1
Я думаю, вы могли бы убить двух птиц одним камнем здесь - исправить свою ошибку и использовать параметры, как это предлагают некоторые из этой темы. Это пример того, как вы будете ссылаться на параметр:
Sub SampleReadCurve() Dim rs As Recordset Dim iRow As Long, iField As Long Dim strSQL As String Dim CurveID As Long Dim MarkRunID As Long Dim ZeroCurveID As String Dim qry As QueryDef CurveID = 15 strSQL = "parameters [CID] number; " & _ "SELECT * FROM VolatilityOutput WHERE CurveID = [CID] ORDER BY MaturityDate" Set qry = CurrentDb.CreateQueryDef("GetCurve", strSQL) qry.Parameters("CID") = CurveID Set rs = qry.OpenRecordset CurrentDb.QueryDefs.Delete ("GetCurve") End SubПомните, что это создает определение запроса и сжимает его каждый раз, когда выполняется подпрограмма, что не является лучшей практикой. Я бы изменил это, чтобы объявить запрос раньше времени (через обычный доступ, а не VBA) и вызывать его в пределах sub, оставив его там в следующий раз, когда вам это нужно:
В заключение следует отметить, что среди многочисленных преимуществ использования параметра привязки/параметра привязки управляются типы данных. Это означает, что если CurveID был датой или строкой, эта методология все равно будет работать. Вам не нужно будет менять SQL для включения кавычек или какой-либо специальной обработки для форматов даты - переменные привязки позаботятся об этом для вас. Это означает, что если ваша строка была:
I'd like to say "hello"(как одинарные, так и двойные кавычки), не требуется специальной обработки. Довольно удивительно, не так ли?
qaru.site
sql - SQL-запрос в MS-Access для организации записей в иерархии
У меня есть база данных MS-доступа, у которой есть следующие столбцы
Userid (первичный ключ)
имя пользователя
Userparent (это не что иное, как userid пользователя, который является боссом текущего пользователя)
userlevel (это позиция текущего пользователя в иерархии)
Есть только 3 уровня. например, если Том и Джерри работают для Боба, а Боб и Билл работают для Стива, тогда данные в таблице будут выглядеть так:
Userid, Username, Userparent, userLevel (это заголовки col)
1, Стив, 0, 1
2, Боб, 1, 2
3, Том, 2, 3
4, Джерри, 2, 3
5, Билл, 1, 2
Мне нужно использовать SQL-запрос в MS-Access 2003 для упорядочения данных в новой таблице, которая будет иметь 3 столбца. Уровень 1, уровень2, уровень3. и сотрудники на уровне подряда появятся сразу ниже более высокого уровня. Как это.
Level1, Level2, Level3 (это заголовки col)
Стив, NULL, NULL
нуль, Боб, нуль
NULL, NULL, Том
NULL, NULL, Джерри
нуль, Билл, нулевой
Поскольку рекурсивные запросы не поддерживаются в доступе, я застрял. Не могли бы вы помочь мне? Заранее большое спасибо
ОБНОВИТЬ
После часа попытки, ближайший я мог бы пойти, используя этот SQL-запрос:
Select y.Level1, x.Level2, x.Level3 FROM (Select ID, Empname as Level1 from Table1 where level=1)y right outer join (select b.FirstLevelID, b.Level2, a.Level3 from (select ParentID as SecondLevelID, Empname as Level3 from Table1 where level = 3)a right outer Join (select ParentID as FirstLevelID, ID, Empname as Level2 from Table1 where level = 2) b on a.SecondLevelID = b.ID)x ON y.ID = x.FirstLevelIDЭто показывает результат таким образом,
Level1, level2, level3 (это заголовки)
Стив, боб, канистра
Стив, боб, Том
Стив, Билл, null
Это правильно устанавливает отношения, но может быть лучше, если я смогу удалить повторное появление имен Уровня 1 и Уровня 2 несколько раз. Может ли кто-нибудь изменить запрос для достижения этого, пожалуйста?
qaru.site
access - Sql запрос в Microsoft Access
У меня есть этот запрос, написанный на Microsoft Access:
SELECT p.artnbr AS [Number], p.name AS Name, s.sizename AS Sizes, s.sizeindex AS SizeIndex, s.oid AS SizeId, l.name AS LocationName, (SELECT od.quantity FROM orderdetails od WHERE od.ORDER = (SELECT o.oid FROM [order] o WHERE o.active = -1 AND o.location = l.oid) AND od.productsize = s.oid) AS Quantity FROM [size] AS s INNER JOIN (product AS p INNER JOIN (favorite AS f INNER JOIN location AS l ON f.customer = l.customer) ON p.oid = f.product) ON p.oid = s.product WHERE f.customer = @customerСсылка ниже показывает таблицы, которые я использовал и их отношения. https://dl.dropbox.com/u/18377860/QueryTables.png
Этот запрос возвращает правильный результат, но поскольку вы можете видеть, что я использую запрос Sub для получения количества. Я не могу понять, как я могу переписать этот запрос с помощью операторов соединения вместо этого длинного подзапроса. Любая помощь будет оценена по достоинству. С уважением
EDIT: Чтобы сделать его более понятным, мой запрос должен получить все уникальные комбинации (продукт, размер, местоположение), имеют ли они [Заказ] или нет, и отображают упорядоченное количество. Если заказ не существует для конкретной комбинации, количество должно быть нулевым.
EDIT2: мне удалось построить запрос, и это выглядит следующим образом:
SELECT p.ArtNbr AS [Number], p.Name AS Name, s.SizeName AS Sizes, s.SizeIndex AS SizeIndex, s.Oid AS SizeId, l.Name AS LocationName, so.qty AS Quantity FROM ([Size] AS s INNER JOIN (Product AS p INNER JOIN (Favorite AS f INNER JOIN Location AS l ON f.Customer = l.Customer) ON p.OID = f.Product) ON p.OID = s.Product) LEFT JOIN (SELECT od.ProductSize AS PS, od.Quantity AS qty, o.Location as Location FROM OrderDetails AS od INNER JOIN [Order] AS o ON od.Order = o.OID WHERE o.Active = -1) AS so ON so.PS = s.OID WHERE f.Customer = @customer AND (l.OID = so.Location OR so.Location is null)Я сделал, как сказал Мэтт, я переместил подзапрос в FROM секции, я включен ProductSize и расположение в выберите запрос производной таблицы, а затем свяжите результат с таблицей «Размер», и я, наконец, добавил условие к разделу where, чтобы исключить повторение одной и той же записи для каждого местоположения.
stackoverrun.com
sql - Нечетные результаты SQL-запроса в MS Access
Отклонение: обсуждение Recordcount наборов DAO-записей
Списком записей набора DAO не гарантируется точность до тех пор, пока не будет .MoveLast, но если какие-либо записи будут возвращены набором записей,.RecordCount будет 1 или более.
Обратите внимание, что набор записей табличного типа немедленно возвращает точный .RecordCount без .MoveLast, но имейте в виду, что вы не можете открыть таблицу записей типа таблицы в связанной таблице. Кроме того, будьте осторожны и не предполагайте, что вы хотите получить тип набора записей, если вы явно не указали его. Хотя dbOpenTable является типом набора записей по умолчанию, если таблицу или строку SQL нельзя открыть в виде набора записей в виде табличного типа, он упадет до открытия динамического набора. Таким образом, вы можете думать, что вы открываете таблицу записей типа таблицы с этим, потому что тип таблицы по умолчанию и вы передали имя таблицы:
Set rs = CurrentDB.OpenRecordset("MyTable")но вы должны помнить, что только потому, что вы передаете ему таблицу, она не обязательно откроет набор записей типа таблицы, а показатель записи не обязательно будет точным. Если вы действительно хотите убедиться, что вы открываете набор записей типа таблицы, вам необходимо явно указать это:
Set rs = CurrentDB.OpenRecordset("MyTable", dbOpenTable)Если "MyTable" - связанная таблица, это вызовет ошибку. Если у вас есть сочетание связанных таблиц и локальных таблиц, вам придется использовать два разных метода для получения набора записей в виде таблицы. В противном случае (т.е. Если вы не укажете тип набора записей и не позволяете ему быть табличным типом, когда это возможно, и с помощью dynaset, если нет), вам нужно знать, когда вам нужно .MoveLast, чтобы получить точный .RecordCount. Если вы действительно хотите быть эффективными в этом случае, вы проверите тип .Type набора записей:
Set rs = CurrentDB.OpenRecordset("MyTable") If rs.Type = dbOpenDynaset Then rs.MoveLast End IfВ этот момент у вас будет точное свойство .RecordCount, если набор записей открыт как тип таблицы или как dynaset.
Но имейте в виду, что очень редко вам нужно использовать полный набор записей для получения записи. Обычно вы проверяете .RecordCount только для того, чтобы увидеть, вернул ли ваш набор записей какие-либо записи, и в этом случае вам не нужен точный счет.
Аналогичным образом, если вы собираетесь пройти полный набор записей, в конечном итоге вы получите точный RecordCount. То есть, было бы бессмысленно делать это:
Set rs = CurrentDB.OpenRecordset("MyTable") rs.MoveLast If rs.RecordCount > 0 Then .MoveFirst Do Until rs.EOF [something or other] .MoveNext Loop End If Debug.Print rs.RecordCount.MoveLast полностью не нужен в этом контексте, так как вам не нужно знать точное количество в этой точке кода.
Кроме того, имейте в виду, что в некоторых контекстах, где вам действительно нужно знать точный .RecordCount, может быть более эффективно использовать встроенную функцию Access DCount() или сделать что-то вроде этого:
Dim lngRecordCount As Long lngRecordCount = CurrentDB.OpenRecordset("SELECT COUNT(*) FROM MyTable")(0)Или:
lngRecordCount = DBEngine.OpenDatabase(Mid(CurrentDB.TableDefs("MyTable").Connect, 11)).TableDefs("MyTable").RecordCountЕсть всевозможные эффективные ярлыки для получения точного RecordCount без принуждения указателя набора записей к последней записи.
Кстати, одна из причин, по которой RecordCount для чистой записи в виде таблицы является точной, заключается в том, что ее не нужно вычислять. Jet/ACE поддерживает свойство RecordCount как часть его регулярных операций.
Отклонение: обсуждение Recordcount наборов записей ADO
By @oneday когда
Для набора записей ADO свойство RecordCount всегда будет конечным. То есть, в отличие от DAO, если вы проверите его значение, оно не может впоследствии измениться. Навигация по EOF никак не влияет на значение RecordCount для ADO.
Это верно даже при асинхронном извлечении записей (то, что набор записей DAO явно не поддерживается), то есть даже если набор записей еще не заполнен, свойство RecordCount по-прежнему отражает окончательное значение (а не количество принятых записей пока что, что касается DAO).
Некоторые комбинации CursorLocation и CursorType всегда будут иметь значение RecordCount, что означает, что свойство не поддерживается. Но, опять же, это останется постоянным, то есть, если оно первоначально равно -1, оно всегда будет равным -1.
Применяемые комбинации для механизма базы данных Access, для которых RecordCount не поддерживаются, являются adOpenForwardOnly и adOpenDynamic, но только при использовании расположения курсора на стороне сервера. (Обратите внимание, что механизм базы данных Access фактически не поддерживает динамические курсоры: вместо этого adOpenDynamic перегружается для пользователя, чтобы обеспечить "подсказку" оптимизации для двигателя, что набор записей Source является динамическим кодом SQL).
Обратите внимание, что установка свойства recordset Filter изменит значение RecordCount, чтобы отразить количество записей после применения фильтра.
qaru.site
