Страницы: 1 2 След.
RSS
SQL в Excel. Инструкция DELETE
 
добрый день всем.
ну вот пришла и моя очередь задавать глупые вопросы.

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

в приложенном файле Excel - 2 листа и неработающий макрос.
выдаёт ошибку "Невозможно удаление записей из указанных таблиц"

в файле Access - аналогичные таблички и аналогичный запрос.
работающий.

товарищи, дорогие, в чем разница??!  :oops:  
что делать дальше и кто виноват?

"за раннее спасибо", "мозг уже кипит", "спасите-помогите" ну и всё остальное, что положено - искренне прилагаю :D
Изменено: ikki - 17.03.2013 16:08:41
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Где-то читал, что без первичного ключа запрос не может правильно идентифицировать запись
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Попробовал через Recordset с курсорами (adOpenDynamic, adLockBatchOptimistic) и в подключении прописывал Mode:ReadWrite - результат тот же, хотя по идее должно было сработать.
Так что, скорей всего, проблема из-за первичных ключей (это касается только удаления данных), но возможно я и не прав.
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
возможно...
но ведь в access-версии тоже нет первичных ключей?..
как проверить?

пс. удалять поэлементно внутри цикла не хотелось бы.
хотя программно это выглядит просто, но множество обращений к БД вместо одного - не айс.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
В Excel нельзя удалять записи (с помощью ADO).
В ссылке см. раздел Retrieve and Edit Excel Data with ADO, пункт Delete.
Кроме того, применение ADO к открытой книге образует утечки памяти
Изменено: ZVI - 17.03.2013 19:12:20
 
нет...
я чего-то глобально недопонимаю...  :cry:
не выполняется даже такая команда
Код
    sSQL = "DELETE * FROM [Лист1$A:A] AS a WHERE a.F1=""mop"""
    cn.Execute sSQL 

с тем же результатом (значение такое есть).

а вообще удаление через DELETE в xls-файлах возможно?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
ZVI, Владимир, писал свой пост, не видя Вашего.
Вы, как всегда, спасаете мой мозг  ;)  
спасибо.

мде... хотя это довольно печально...
(я про невозможность удаления)
ни ADO, ни DAO?
вообще - не открывая файл - никак?
(* ушёл куда послали - по ссылкам  :D *)
Изменено: ikki - 17.03.2013 19:13:51
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
не открывая файл - никак?
Записи и строки в модели Excel не одно и тоже, поэтому - никак.
Можно делать замену рекордсета  но это - циклы.
 
почитал ссылки...
очистка всех полей вместо удаления строки - это, конечно, вариант.
точнее - "вариант".
ужас какой-то  :(

в общем, я понял так - из всего SQL более-менее нормально (если забыть про индексацию-оптимизацию) для Excel-файлов можно использовать только SELECT. ну, возможно, UPDATE.

для всего остального - добро пожаловать в мир БД  :)

с одной стороны - правильно.
с другой - я всё же не понимаю, почему такие ограничения?..
не маркетологи ли вмешались?  ;)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki: не маркетологи ли вмешались?
Вряд ли. Вот, как всегда, интересная статья от Джоэла Спольски с объяснениями причин.
Цитата о форматах файлов Excel:
1) намеренно сделаны сложными и тёмными;
2) такое мог выдумать только сумасшедший киборг;
3) были созданы крайне неумелыми программистами;
4) невозможно корректно считать и записать.
Смею вас заверить: всё это неверно.
Изменено: ZVI - 17.03.2013 19:42:04
 
пс. подумал/вспомнил (в тумане мыслей головы проплывал старый Clipper)...
а ведь в старых СУБД на DBF так и было?..
команда "удалить" приводила не к немедленному удалению записи, а к её особой пометке о "неиспользовании".
удалялись же записи только при "сжатии" (кажется, так).

можно сделать такие же костыли для Excel.
вместо DELETE в отдельную колонку командой UPDATE ставить "единички".
а в соответствующие "селекты" добавить условие по этому "полю"...
глупость или всё же достаточно реально?
:oops:
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
глупость или всё же достаточно реально?
Вполне разумно
 
Цитата
ZVI пишет: Смею вас заверить: всё это неверно .
не, ну в мировой заговор я тож не верю.
но - почитать об этом - та-а-ак интересно  :D  :D  :D
фрилансер 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
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Цитата
LightZ пишет:
можно попробовать
спасибо.
конечно, можно попробовать.

хотя меня SQL привлекает именно элегантностью и простотой "чистых" команд, без последующих переборов рекордсетов, курсоров и т.п.
но...
"случаи бывают разными" (с)
всё пригодится.
;)

пс. по поводу эффективности NOT IN в подзапросах Gustav мне уже мозги промыл.
если просто веришь ему (и мне) - НИКОГДА не делай так!  :D
если нужны подробности (я б затребовал) - завтра поищу.
;)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki пишет: если просто веришь ему (и мне) -  НИКОГДА не делай так!
Верю, не буду :)
Просто пытался сделать с помощью except - но почему-то не вышло, возможно в ado опять что-то урезано
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Цитата
LightZ пишет:
Где-то читал, что без первичного ключа запрос не может правильно идентифицировать запись
Для удаления необязательно иметь первичный ключ.
Вообще, первичный ключ необязательно иметь. И data integrity тоже необязательно иметь.
Внутрення структура хранения в БД (будь то Access или SQL Server) различит даже самые одинаковые строки.
There is no knowledge that is not power
 
LightZ,
Богдан, добрый день
Для определения  количества записей я бы использовал стандартные свойства
рекордсета
Код
  lCount = UBound(rs.GetRows, 2) + 3

так очень плохо, и если ты будешь грузить большие объемы данных ты почуствуешь разницу в скорости исполнения кода.
Правильно делать необходимо так:

Код
.........................
cn.CursorLocation = 3
 cn.Open sCon
...........................
lCount=rs.RecordCount


Johny,
Цитата
Вообще, первичный ключ необязательно иметь

Будь то составной ключ или первичный ключ, ОН(Ключ) должен быть всегда.
Спасибо
 
Цитата
R Dmitry пишет:
Будь то составной ключ или первичный ключ, ОН(Ключ) должен быть всегда.
И где это написано?
Если я создам индекс и сделаю его уникальным - вот это и будет Integrity.
И чем этот столбец не первичный ключ?
Когда я нажимаю, например, в Access на кнопку "Ключевое поле", то что он делает?
Он просто создаёт уникальный индекс без возможности дубликатов.
Могу я сделать то же самое без нажатия этой кнопки?
Да.
There is no knowledge that is not power
 
Цитата
ikki пишет:
в общем, я понял так - из всего SQL более-менее нормально (если забыть про индексацию-оптимизацию) для Excel-файлов можно использовать только SELECT. ну, возможно, UPDATE.
В Экселе можно выполнять SELECT, INSERT и UPDATE.
DELETE - нельзя.

Кстати, с помощью ADO + SQL можно опрашивать и текстовые файлы. :)
There is no knowledge that is not power
 
Цитата
R Dmitry пишет: Правильно делать необходимо так:
Здравствуйте, Дмитрий. Спасибо, буду знать.

Ps. А что скажете на счет Except в ADO?
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Цитата
LightZ пишет:
Ps. А что скажете на счет Except в ADO?
скорее в JET или ACE
Except в Jet нет, впрочем как и еще много чего :)
но по сути Except это аналог NOT IN, и очень медленный


Johny,
не путайте понятия "Ключевое поле"
И
primary key
Спасибо
 
Спасибо, а может посоветуете хорошую ссылочку по описанию Jet, Ace, ADO?
Да и вообще хотелось бы четко, ясно и конкретно ознакомится с sql-запросами через подключение ADODB.
(и наверное не только одному мне :))
А-то нахожу только какие-то форумные темы...
Хотелось бы уже прояснить: что можно, а что нельзя  :|
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Цитата
Johny пишет:
В Экселе можно выполнять SELECT, INSERT и UPDATE.
без каких-либо ограничений?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
LightZ пишет:
четко, ясно и конкретно
ZVI здесь давал хорошую ссылку на первоисточник.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
не путайте понятия "Ключевое поле" и  primary key
Я так понимаю, что для Вас различие между словами "Ключевое поле" и "Primary key" состоит в том, что они состоят из разных букв?
Если не знаете, о чём говорите, не говорите лучше.

P.S. Установите английский офис или интерфейс - и посмотрите, как называется эта кнопка на английском.
Изменено: Johny - 20.03.2013 23:12:52
There is no knowledge that is not power
 
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...
There is no knowledge that is not power
 
Johny,
Я не хочу вступать с Вами в полемику.
Если для Вас нет разницы между словами
"Ключевое поле" и "Primary key", делайте как знаете.
--------------------
Советую выучить Вам наизусть 12 правил Кодда.
--------------------------------------------------
Спасибо
 
Цитата
R Dmitry пишет:
Советую выучить Вам наизусть 12 правил Кодда.
Я читал и Кодда, и правила нормализации и поверьте, что я знаю, что такое первичный ключ.
Ещё раз повторюсь - поставьте английский офис и посмотрите название кнопки.
После этого можно продолжить разговор.
There is no knowledge that is not power
 
Дело не в кнопке, и не в английском офисе, поле и индентификатор записи это разные  вещи!
Спасибо
Страницы: 1 2 След.
Наверх