добрый день всем. ну вот пришла и моя очередь задавать глупые вопросы.
пытаюсь решить "классическую" задачу удаления из одного списка значений, встречающихся в другом. "хитрым" способом. менее хитрые - просьба не предлагать.
в приложенном файле Excel - 2 листа и неработающий макрос. выдаёт ошибку "Невозможно удаление записей из указанных таблиц"
в файле Access - аналогичные таблички и аналогичный запрос. работающий.
товарищи, дорогие, в чем разница??! что делать дальше и кто виноват?
"за раннее спасибо", "мозг уже кипит", "спасите-помогите" ну и всё остальное, что положено - искренне прилагаю
Попробовал через Recordset с курсорами (adOpenDynamic, adLockBatchOptimistic) и в подключении прописывал Mode:ReadWrite - результат тот же, хотя по идее должно было сработать. Так что, скорей всего, проблема из-за первичных ключей (это касается только удаления данных), но возможно я и не прав.
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
В Excel нельзя удалять записи (с помощью ADO). В ссылке см. раздел Retrieve and Edit Excel Data with ADO, пункт Delete. Кроме того, применение ADO к открытой книге образует утечки памяти
ZVI, Владимир, писал свой пост, не видя Вашего. Вы, как всегда, спасаете мой мозг спасибо.
мде... хотя это довольно печально... (я про невозможность удаления) ни ADO, ни DAO? вообще - не открывая файл - никак? (* ушёл куда послали - по ссылкам *)
почитал ссылки... очистка всех полей вместо удаления строки - это, конечно, вариант. точнее - "вариант". ужас какой-то
в общем, я понял так - из всего SQL более-менее нормально (если забыть про индексацию-оптимизацию) для Excel-файлов можно использовать только SELECT. ну, возможно, UPDATE.
для всего остального - добро пожаловать в мир БД
с одной стороны - правильно. с другой - я всё же не понимаю, почему такие ограничения?.. не маркетологи ли вмешались?
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
Вряд ли. Вот, как всегда, интересная статья от Джоэла Спольски с объяснениями причин. Цитата о форматах файлов Excel: 1) намеренно сделаны сложными и тёмными; 2) такое мог выдумать только сумасшедший киборг; 3) были созданы крайне неумелыми программистами; 4) невозможно корректно считать и записать. Смею вас заверить: всё это неверно.
пс. подумал/вспомнил (в тумане мыслей головы проплывал старый Clipper)... а ведь в старых СУБД на DBF так и было?.. команда "удалить" приводила не к немедленному удалению записи, а к её особой пометке о "неиспользовании". удалялись же записи только при "сжатии" (кажется, так).
можно сделать такие же костыли для Excel. вместо DELETE в отдельную колонку командой UPDATE ставить "единички". а в соответствующие "селекты" добавить условие по этому "полю"... глупость или всё же достаточно реально?
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
Саш, может такой вариант подойдёт? (если я правильно понял суть того, что ты хочешь добиться). Т.к. DELETE не работает в ADO (ZVI спасибо, не знал), можно попробовать два вложенных запроса. Оставить только несовпадения и заменить таблицу. Можно и без цикла:
Код
Sub go()
Dim sCon$, cn As Object, rs As Object
Dim finalRow&, lCount&, sSQL$
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeReadWrite
Select Case CLng(Split(Application.Version, ".")(0))
Case Is < 12
sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Case Is >= 12
sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
End Select
sSQL = "SELECT * FROM [Лист1$A:A] " & _
"WHERE [Лист1$A:A].test " & _
"NOT IN (SELECT ttest FROM [Лист2$A1:B])"
cn.Open sCon
Set rs = cn.Execute(sSQL)
lCount = UBound(rs.GetRows, 2) + 3
With Лист1
.Cells(2, 1).CopyFromRecordset cn.Execute(sSQL)
finalRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(lCount, 1), .Cells(finalRow, 1)).Clear
End With
End Sub
хотя меня SQL привлекает именно элегантностью и простотой "чистых" команд, без последующих переборов рекордсетов, курсоров и т.п. но... "случаи бывают разными" (с) всё пригодится.
пс. по поводу эффективности NOT IN в подзапросах Gustav мне уже мозги промыл. если просто веришь ему (и мне) - НИКОГДА не делай так! если нужны подробности (я б затребовал) - завтра поищу.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
LightZ пишет: Где-то читал, что без первичного ключа запрос не может правильно идентифицировать запись
Для удаления необязательно иметь первичный ключ. Вообще, первичный ключ необязательно иметь. И data integrity тоже необязательно иметь. Внутрення структура хранения в БД (будь то Access или SQL Server) различит даже самые одинаковые строки.
R Dmitry пишет: Будь то составной ключ или первичный ключ, ОН(Ключ) должен быть всегда.
И где это написано? Если я создам индекс и сделаю его уникальным - вот это и будет Integrity. И чем этот столбец не первичный ключ? Когда я нажимаю, например, в Access на кнопку "Ключевое поле", то что он делает? Он просто создаёт уникальный индекс без возможности дубликатов. Могу я сделать то же самое без нажатия этой кнопки? Да.
ikki пишет: в общем, я понял так - из всего SQL более-менее нормально (если забыть про индексацию-оптимизацию) для Excel-файлов можно использовать только SELECT. ну, возможно, UPDATE.
В Экселе можно выполнять SELECT, INSERT и UPDATE. DELETE - нельзя.
Кстати, с помощью ADO + SQL можно опрашивать и текстовые файлы.
Спасибо, а может посоветуете хорошую ссылочку по описанию Jet, Ace, ADO? Да и вообще хотелось бы четко, ясно и конкретно ознакомится с sql-запросами через подключение ADODB. (и наверное не только одному мне ) А-то нахожу только какие-то форумные темы... Хотелось бы уже прояснить: что можно, а что нельзя :|
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
Я так понимаю, что для Вас различие между словами "Ключевое поле" и "Primary key" состоит в том, что они состоят из разных букв? Если не знаете, о чём говорите, не говорите лучше.
P.S. Установите английский офис или интерфейс - и посмотрите, как называется эта кнопка на английском.
JOIN'ы (LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN, FULL JOIN, Self Join), подзапросы, алиасы, использование функций VBA прямо в SQL, GROUP BY, HAVING, ORDER BY, TOP, DISTINCT, WHERE, IN, LIKE...
Johny, Я не хочу вступать с Вами в полемику. Если для Вас нет разницы между словами "Ключевое поле" и "Primary key", делайте как знаете. -------------------- Советую выучить Вам наизусть 12 правил Кодда. --------------------------------------------------
R Dmitry пишет: Советую выучить Вам наизусть 12 правил Кодда.
Я читал и Кодда, и правила нормализации и поверьте, что я знаю, что такое первичный ключ. Ещё раз повторюсь - поставьте английский офис и посмотрите название кнопки. После этого можно продолжить разговор.