Хочу написать макрос, который бы искал определённое значение в базе данных Access. Пока получился вот такой код:
[vba]
Код
Option Explicit Sub Search()
'переменная, хранящая результат запроса Dim tbl As Recordset
'строка запроса SQL Dim SQLr As String
'переменная, хранящая ссылку на подключенную БД Dim dbs As Database
'переменная, содержимое которой надо искать Dim txt txt = "'99999999'"
'подключаемся к mdb Set dbs = DAO.OpenDatabase("C:\Documents and Settings\user\Рабочий стол\base.mdb")
'составляем строку SQL запроса SQLr = "SELECT * FROM Table1 WHERE Table1.[ID устройства]=" & txt
'отправляем запрос открытой БД 'результат в виде таблицы сохранен в tbl Set tbl = dbs.OpenRecordset(SQLr)
'вставляем результат в лист, начиная с ячейки A1 Cells(1, 1).CopyFromRecordset tbl
'закрываем временную таблицу tbl.Close
'очищаем память от таблицы - ЭТО ВАЖНО! Set tbl = Nothing
'закрываем базу dbs.Close Set dbs = Nothing End Sub
[/vba]
Макрос ищет значение переменной txt в указанной базе MsAccess, после чего вставляет в лист Excel все данные из строки, где было найдено вхождение. С чем в упор не могу разобраться:
а) как заставить его искать не жёстко указанное значение, а то, которое находится в подсвеченной ячейке? Если вместо 99999999 подставить ActiveCell.Value, то всё ломается... пробовал играться с " и ', но результата это не принесло
б) как его заставить его делать фокус на найденном в самой базе? Делал похожий макрос для Excel, там это реализовывалось таким образом:
(перед этим, естественно, открывая нужную книгу, выводя её на передний план и производя поиск, более подробно выложил решение тут)
Но как провернуть это в Access? Как открыть базу и вывести её на передний план - понятно, а что делать с ActiveSheet.Range(...).Select? Там-то такой трюк не сработает...
Хочу написать макрос, который бы искал определённое значение в базе данных Access. Пока получился вот такой код:
[vba]
Код
Option Explicit Sub Search()
'переменная, хранящая результат запроса Dim tbl As Recordset
'строка запроса SQL Dim SQLr As String
'переменная, хранящая ссылку на подключенную БД Dim dbs As Database
'переменная, содержимое которой надо искать Dim txt txt = "'99999999'"
'подключаемся к mdb Set dbs = DAO.OpenDatabase("C:\Documents and Settings\user\Рабочий стол\base.mdb")
'составляем строку SQL запроса SQLr = "SELECT * FROM Table1 WHERE Table1.[ID устройства]=" & txt
'отправляем запрос открытой БД 'результат в виде таблицы сохранен в tbl Set tbl = dbs.OpenRecordset(SQLr)
'вставляем результат в лист, начиная с ячейки A1 Cells(1, 1).CopyFromRecordset tbl
'закрываем временную таблицу tbl.Close
'очищаем память от таблицы - ЭТО ВАЖНО! Set tbl = Nothing
'закрываем базу dbs.Close Set dbs = Nothing End Sub
[/vba]
Макрос ищет значение переменной txt в указанной базе MsAccess, после чего вставляет в лист Excel все данные из строки, где было найдено вхождение. С чем в упор не могу разобраться:
а) как заставить его искать не жёстко указанное значение, а то, которое находится в подсвеченной ячейке? Если вместо 99999999 подставить ActiveCell.Value, то всё ломается... пробовал играться с " и ', но результата это не принесло
б) как его заставить его делать фокус на найденном в самой базе? Делал похожий макрос для Excel, там это реализовывалось таким образом:
(перед этим, естественно, открывая нужную книгу, выводя её на передний план и производя поиск, более подробно выложил решение тут)
Но как провернуть это в Access? Как открыть базу и вывести её на передний план - понятно, а что делать с ActiveSheet.Range(...).Select? Там-то такой трюк не сработает...Venique
Сообщение отредактировал Venique - Понедельник, 17.11.2014, 17:58
б) как его заставить его делать фокус на найденном в самой базе?
Тут надо понимать, что набор записей, который достается из базы данных при помощи DAO или ADO, сам по себе не имеет визуального представления. Чтобы увидеть эти данные, их надо передать в какое-либо средство визуализации. Например, на лист Excel или в форму Access или в какой-нибудь элемент управления Grid. Сделать запрос в DAO и потом вызвать "какой-нибудь" метод для визуализации нельзя, потому что такого метода нет ни в DAO, ни в ADO, ни в SQL.
В Access "на безрыбье" можно макрокомандами объекта DoCmd открыть таблицу, найти запись по условию, встать на эту запись и даже встать в конкретное поле этой записи. Будет похоже на Excel: [vba]
Код
Sub test() DoCmd.OpenTable "Таблица1", acViewNormal DoCmd.SearchForRecord acDataTable, "Таблица1", acFirst, "Поле4 = 17" DoCmd.GoToControl "Поле4" End Sub
[/vba] А так вообще надо форму (объект Form) ваять и уже в ней отображать желаемые записи и позиционироваться на них. В Access не всё так "вдруг", как в Excel...
б) как его заставить его делать фокус на найденном в самой базе?
Тут надо понимать, что набор записей, который достается из базы данных при помощи DAO или ADO, сам по себе не имеет визуального представления. Чтобы увидеть эти данные, их надо передать в какое-либо средство визуализации. Например, на лист Excel или в форму Access или в какой-нибудь элемент управления Grid. Сделать запрос в DAO и потом вызвать "какой-нибудь" метод для визуализации нельзя, потому что такого метода нет ни в DAO, ни в ADO, ни в SQL.
В Access "на безрыбье" можно макрокомандами объекта DoCmd открыть таблицу, найти запись по условию, встать на эту запись и даже встать в конкретное поле этой записи. Будет похоже на Excel: [vba]
Код
Sub test() DoCmd.OpenTable "Таблица1", acViewNormal DoCmd.SearchForRecord acDataTable, "Таблица1", acFirst, "Поле4 = 17" DoCmd.GoToControl "Поле4" End Sub
[/vba] А так вообще надо форму (объект Form) ваять и уже в ней отображать желаемые записи и позиционироваться на них. В Access не всё так "вдруг", как в Excel...Gustav
Pelena, спасибо большое, сработало - теперь ищется значение, которое находится в активной ячейке. А как можно выдавать MsgBox 'Ничего не найдено', если вхождения нет? Пробовал поиграться с tbl / SQLr < 0 / Is Nothing, но результата не дало... По идее, тут надо все-таки вставлять переменную Txt в сам запрос, у которой значение должно быть ActiveCell.Value, а потом уже его проверять на Is Nothing / < 0? В таком случае я опять начинаю с начала и не понимаю, как правильно использовать переменную в моем случае.
Gustav, я Вас понял... тяжело, конечно. Но я подумал, что раз уж такое дело, то лучше даже не открывать базу (а это лишние действие, с учетом того, что поиск можно производить даже без открытия оной, а это благо) и не фокусироваться на найденной строке, а выводить экселевскую UserForm, в которой и будут отображаться найденные данные. Для удобства можно даже добавить кнопку "скопировать в буфер обмена". Подскажите, как это можно реализовать?
И как потом задать форматирование выводимой информации? Интересует перевод строки, чтобы информация из каждого отдельного столбца выводилась с новой строки.
Pelena, спасибо большое, сработало - теперь ищется значение, которое находится в активной ячейке. А как можно выдавать MsgBox 'Ничего не найдено', если вхождения нет? Пробовал поиграться с tbl / SQLr < 0 / Is Nothing, но результата не дало... По идее, тут надо все-таки вставлять переменную Txt в сам запрос, у которой значение должно быть ActiveCell.Value, а потом уже его проверять на Is Nothing / < 0? В таком случае я опять начинаю с начала и не понимаю, как правильно использовать переменную в моем случае.
Gustav, я Вас понял... тяжело, конечно. Но я подумал, что раз уж такое дело, то лучше даже не открывать базу (а это лишние действие, с учетом того, что поиск можно производить даже без открытия оной, а это благо) и не фокусироваться на найденной строке, а выводить экселевскую UserForm, в которой и будут отображаться найденные данные. Для удобства можно даже добавить кнопку "скопировать в буфер обмена". Подскажите, как это можно реализовать?
И как потом задать форматирование выводимой информации? Интересует перевод строки, чтобы информация из каждого отдельного столбца выводилась с новой строки.Venique
И как потом задать форматирование выводимой информации? Интересует перевод строки, чтобы информация из каждого отдельного столбца выводилась с новой строки.
Вот константа vbCrLf как раз и отвечает за перевод строки.
И как потом задать форматирование выводимой информации? Интересует перевод строки, чтобы информация из каждого отдельного столбца выводилась с новой строки.
Вот константа vbCrLf как раз и отвечает за перевод строки.Gustav
Pelena, прикладываю архив с файлом и базой. С переменной пока так и не разобрался - каким образом выводить MsgBox о том, что результатов поиска нет? Надо как-то проверять содержимое tbl или SQLq?
Gustav, нюанс в том, что в MsgBox, в отличии от UserForm (если я ничего не путаю) нельзя добавить "свои" кнопки. Например, кнопку "скопировать в буфер обмена". Как все-таки прикрутить UserForm?
Тем не менее, попробовал реализовать на основе MsgBox и сразу возник следующий вопрос - как получить и распарсить значение tbl, в котором содержится ответ от базы? Это значение успешно копируется на лист вот этой строкой:
[vba]
Код
Cells(1, 1).CopyFromRecordset tbl
[/vba]
Но что подставлять вместо "значение Поля #", чтобы вывести оное уже в окне? Нечто вроде tbl.[Поле 1], tbl.[Поле 2] и т.п...
Pelena, прикладываю архив с файлом и базой. С переменной пока так и не разобрался - каким образом выводить MsgBox о том, что результатов поиска нет? Надо как-то проверять содержимое tbl или SQLq?
Gustav, нюанс в том, что в MsgBox, в отличии от UserForm (если я ничего не путаю) нельзя добавить "свои" кнопки. Например, кнопку "скопировать в буфер обмена". Как все-таки прикрутить UserForm?
Тем не менее, попробовал реализовать на основе MsgBox и сразу возник следующий вопрос - как получить и распарсить значение tbl, в котором содержится ответ от базы? Это значение успешно копируется на лист вот этой строкой:
[vba]
Код
Cells(1, 1).CopyFromRecordset tbl
[/vba]
Но что подставлять вместо "значение Поля #", чтобы вывести оное уже в окне? Нечто вроде tbl.[Поле 1], tbl.[Поле 2] и т.п...Venique
По поводу вывода - макрос вставляет полученные через SQL данные на лист чисто для дебага, чтобы понимать, что он сам по себе работает и данные получает корректно. Поэтому копировать и подставлять в окно данные из ячеек - хитро, но не туда, так как в конечном счете он должен вставлять эти данные напрямую в окно, а не на лист
Pelena, спасибо, не догадался про "RecordCount".
По поводу вывода - макрос вставляет полученные через SQL данные на лист чисто для дебага, чтобы понимать, что он сам по себе работает и данные получает корректно. Поэтому копировать и подставлять в окно данные из ячеек - хитро, но не туда, так как в конечном счете он должен вставлять эти данные напрямую в окно, а не на лист Venique
Сообщение отредактировал Venique - Вторник, 18.11.2014, 15:52
С переменной пока так и не разобрался - каким образом выводить MsgBox о том, что результатов поиска нет? Надо как-то проверять содержимое tbl или SQLq?
Venique, при программировании с использованием DAO существуют определенные традиции, например, префиксом (или переменной) tbl обозначаются объекты таблицы. Рекордсет в таком требуквии обычно обозначается rst. Самый надежный признак отсутствия записей в выборке (ладно уж, по-вашему будем): tbl.BOF = True And tbl.EOF = True (или просто tbl.BOF And tbl.EOF). Либо можно использовать tbl.RecordCount = 0.
Вам надо немножко почитать про всё это, пока знаний маловато для беседы. В том смысле, что нам придется объяснять слишком много - мы на это не рассчитываем Поищите материал и примеры кода по строкам "DAO.Recordset" и "Excel UserForm". Гугл еще никто не отменял.
С переменной пока так и не разобрался - каким образом выводить MsgBox о том, что результатов поиска нет? Надо как-то проверять содержимое tbl или SQLq?
Venique, при программировании с использованием DAO существуют определенные традиции, например, префиксом (или переменной) tbl обозначаются объекты таблицы. Рекордсет в таком требуквии обычно обозначается rst. Самый надежный признак отсутствия записей в выборке (ладно уж, по-вашему будем): tbl.BOF = True And tbl.EOF = True (или просто tbl.BOF And tbl.EOF). Либо можно использовать tbl.RecordCount = 0.
Вам надо немножко почитать про всё это, пока знаний маловато для беседы. В том смысле, что нам придется объяснять слишком много - мы на это не рассчитываем Поищите материал и примеры кода по строкам "DAO.Recordset" и "Excel UserForm". Гугл еще никто не отменял.
Pelena, Gustav, спасибо огромное и за решение, и за пищу к размышлению! До этого ни разу не организовывал взаимосвязь Excel с MSAccess, посему даже не знал, в какую сторону гуглить.
С вашей помощью блок обработки результатов получился таким (с учетом цикла, если записей несколько):
Надо только обработчик ошибок прикрутить, чтобы по достижению последней записи макрос никого не пугал Единственный вопрос, который остался - каким образом на протяжении цикла собрать все strMsg воедино, а вывести разом уже после его завершения? Т.е. чтобы не приходилось при нахождении новой строки каждый раз тыкать "ОК", а разом получить всю информацию по найденным записям.
Pelena, Gustav, спасибо огромное и за решение, и за пищу к размышлению! До этого ни разу не организовывал взаимосвязь Excel с MSAccess, посему даже не знал, в какую сторону гуглить.
С вашей помощью блок обработки результатов получился таким (с учетом цикла, если записей несколько):
Надо только обработчик ошибок прикрутить, чтобы по достижению последней записи макрос никого не пугал Единственный вопрос, который остался - каким образом на протяжении цикла собрать все strMsg воедино, а вывести разом уже после его завершения? Т.е. чтобы не приходилось при нахождении новой строки каждый раз тыкать "ОК", а разом получить всю информацию по найденным записям.Venique
Сообщение отредактировал Venique - Вторник, 18.11.2014, 18:18
Отлично, спасибо! Получилось. Как причешу - обязательно выложу сюда, может, кому-то будет полезно уже готовое решение
Правда, вылез один нюанс... одно из значений в таблице является связью с другой таблицей, в итоге мне выводится не "Модель устройства - Intel", а "Модель устройства - 13". И если посмотреть в другую таблицу, то там под номером 13 как раз и будет записан "Intel". Т.е. в данном виде мой макрос (и запрос к базе) не может интерпретировать эту цифру "13", т.е. посмотреть что она значит в другой таблице.
Как научить его такой "вложенности", т.е. чтобы он понимал, что нужно перед выводом модели устройства заглянуть в соседнюю таблицу и посмотреть, что там значится под номером 13, а мне уже вывести слово "Intel"?
Была идея сделать в цикле проверку вроде IF tbl1.[Модель устройства] = 1 Then strMsg.[Модель устройства] = ATOM, IF tbl1.[Модель устройства] = 2 Then strMsg.[Модель устройства] = AMD и так со всеми ID из "соседней" базы, но это очень громоздко + придется постоянно обновлять не только саму базу, но и макрос, дописывая в него новые ID и изменяя уже существующие.
Отлично, спасибо! Получилось. Как причешу - обязательно выложу сюда, может, кому-то будет полезно уже готовое решение
Правда, вылез один нюанс... одно из значений в таблице является связью с другой таблицей, в итоге мне выводится не "Модель устройства - Intel", а "Модель устройства - 13". И если посмотреть в другую таблицу, то там под номером 13 как раз и будет записан "Intel". Т.е. в данном виде мой макрос (и запрос к базе) не может интерпретировать эту цифру "13", т.е. посмотреть что она значит в другой таблице.
Как научить его такой "вложенности", т.е. чтобы он понимал, что нужно перед выводом модели устройства заглянуть в соседнюю таблицу и посмотреть, что там значится под номером 13, а мне уже вывести слово "Intel"?
Была идея сделать в цикле проверку вроде IF tbl1.[Модель устройства] = 1 Then strMsg.[Модель устройства] = ATOM, IF tbl1.[Модель устройства] = 2 Then strMsg.[Модель устройства] = AMD и так со всеми ID из "соседней" базы, но это очень громоздко + придется постоянно обновлять не только саму базу, но и макрос, дописывая в него новые ID и изменяя уже существующие.Venique
Сообщение отредактировал Venique - Среда, 19.11.2014, 17:54
Создайте в БД запрос на основе связанных таблиц, в котором будут выводится поля в нужном виде и создавайте recordset из этого запроса. Либо включите в Ваш запрос SQL связь с другой таблицей с помощью INNER JOIN
Создайте в БД запрос на основе связанных таблиц, в котором будут выводится поля в нужном виде и создавайте recordset из этого запроса. Либо включите в Ваш запрос SQL связь с другой таблицей с помощью INNER JOINPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
SQLr = "SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Тип_оборудования=Table2.Rec_ID WHERE EquipStorage.[ID устройства]='" & ActiveCell.Value & "';"
[/vba] Но в Table1 содержатся связанные записи не только из Table2, но и из Table3. Гугление на протяжении всего этого времени выдало кучу вариантов и ни один из них не сработал. Либо я неправильно составляю запрос, либо намудрил со скобками... пробовал составить его через SQL-вид конструктора в самом Access, но там совсем какая-то мешанина получается, которая в Excel отрабатывается, но переменные из "связанных" таблиц снова отображаются как цифры (ID), словно теряется сама связь между таблицами.
Не подскажете, как всё-таки добавить второй INNER JOIN? Нечто вроде такого:
[vba]
Код
SQLr = "SELECT * FROM (Table1 INNER JOIN Table2 ON Table1.Тип_оборудования=Table2.Rec_ID) INNER JOIN Table3 ON Table1.Место_установки=Table3.Rec_ID WHERE EquipStorage.[ID устройства]='" & ActiveCell.Value & "';"
[/vba] Нашел хорошее руководство, но оно только по SQL и не отображает всех нюансов Access, из-за чего предложенные там варианты не отрабатывают, как и мои попытки совладать со скобками
Pelena, спасибо, в который раз выручаете
С INNER JOIN получилось, вышел такой запрос:
[vba]
Код
SQLr = "SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Тип_оборудования=Table2.Rec_ID WHERE EquipStorage.[ID устройства]='" & ActiveCell.Value & "';"
[/vba] Но в Table1 содержатся связанные записи не только из Table2, но и из Table3. Гугление на протяжении всего этого времени выдало кучу вариантов и ни один из них не сработал. Либо я неправильно составляю запрос, либо намудрил со скобками... пробовал составить его через SQL-вид конструктора в самом Access, но там совсем какая-то мешанина получается, которая в Excel отрабатывается, но переменные из "связанных" таблиц снова отображаются как цифры (ID), словно теряется сама связь между таблицами.
Не подскажете, как всё-таки добавить второй INNER JOIN? Нечто вроде такого:
[vba]
Код
SQLr = "SELECT * FROM (Table1 INNER JOIN Table2 ON Table1.Тип_оборудования=Table2.Rec_ID) INNER JOIN Table3 ON Table1.Место_установки=Table3.Rec_ID WHERE EquipStorage.[ID устройства]='" & ActiveCell.Value & "';"
[/vba] Нашел хорошее руководство, но оно только по SQL и не отображает всех нюансов Access, из-за чего предложенные там варианты не отрабатывают, как и мои попытки совладать со скобками Venique
Сообщение отредактировал Venique - Среда, 19.11.2014, 21:18
Попробуйте вместо * явно перечислить нужные поля по типу имя_таблицы.имя_поля
И ещё: не видя таблиц, могу ошибаться, но возникают большие сомнения, что таблицы связаны именно по указанным полям. Например, место установки и Reс_Id - это, имхо, совершенно разные поля
Попробуйте вместо * явно перечислить нужные поля по типу имя_таблицы.имя_поля
И ещё: не видя таблиц, могу ошибаться, но возникают большие сомнения, что таблицы связаны именно по указанным полям. Например, место установки и Reс_Id - это, имхо, совершенно разные поляPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Pelena, не получилось... видимо, я некорректно организовываю связь между таблицами. В приложении архив с xls и базой, выделяете ID и жмакаете кнопку - будет произведён поиск с одним INNER JOIN, благодаря чему поле "Модель" будет заполнено верно. А вот "Филиал" отображается числами, так как второй INNER JOIN не получилось заставить работать (он закомментирован в коде). Либо я неправильно его записал, либо действительно таблицы в самой базе связаны как-то иначе.
Единственное условие - базу менять нельзя, увы.
Pelena, не получилось... видимо, я некорректно организовываю связь между таблицами. В приложении архив с xls и базой, выделяете ID и жмакаете кнопку - будет произведён поиск с одним INNER JOIN, благодаря чему поле "Модель" будет заполнено верно. А вот "Филиал" отображается числами, так как второй INNER JOIN не получилось заставить работать (он закомментирован в коде). Либо я неправильно его записал, либо действительно таблицы в самой базе связаны как-то иначе.
Почти всё правильно Кроме: 1) так как в БД есть незаполненные Филиалы, связь INNER JOIN меняем на LEFT JOIN (если модели тоже могут быть пустыми, то и для них надо будет поменять аналогично) 2) так как в двух вспомогательных таблицах одинаковые имена полей, надо к ним обращаться с указанием имени таблицы (исправленные строчки пометила ****) 3) перечисление полей можно сократить, не все они нужны для отчёта
Почти всё правильно Кроме: 1) так как в БД есть незаполненные Филиалы, связь INNER JOIN меняем на LEFT JOIN (если модели тоже могут быть пустыми, то и для них надо будет поменять аналогично) 2) так как в двух вспомогательных таблицах одинаковые имена полей, надо к ним обращаться с указанием имени таблицы (исправленные строчки пометила ****) 3) перечисление полей можно сократить, не все они нужны для отчётаPelena
Единственный вопрос, который остался - иногда приложение выдает ошибку 429 ActiveX. Чтобы воспроизвести эту ошибку, достаточно добавить заведомо ошибочный код и запустить его, чтобы вылезло окно дебага, поправить эту ошибку (хотя можно без этого), а потом запустить выполнение макроса ещё раз. Он единожды ругнется и потом всё будет нормально.
Как это можно исправить?
В теории, если ошибок в коде макроса не будет, то и ошибки 429 тоже не появится, но это явно говорит о том, что что-то не так, вот и хотелось бы понять, что надо исправить.
Вот, теперь работает! Снимаю перед Вами шляпу
Единственный вопрос, который остался - иногда приложение выдает ошибку 429 ActiveX. Чтобы воспроизвести эту ошибку, достаточно добавить заведомо ошибочный код и запустить его, чтобы вылезло окно дебага, поправить эту ошибку (хотя можно без этого), а потом запустить выполнение макроса ещё раз. Он единожды ругнется и потом всё будет нормально.
Как это можно исправить?
В теории, если ошибок в коде макроса не будет, то и ошибки 429 тоже не появится, но это явно говорит о том, что что-то не так, вот и хотелось бы понять, что надо исправить.Venique
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
