Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 63500 Skopiuj do schowka ADO - nadawanie ID
Autor Wiadomość
BrunO 
ExcelSpec



Pomógł: 125 razy
Posty: 541
Wysłany: 14-12-2018, 14:31   ADO - nadawanie ID

Z poziomu Excela działam na bazie w Accesie.

Z bazy liczącej kilkadziesiąt rekordów wydłubałem sobie listę ok. 13 tys. (względnie) niepowtarzalnych podmiotów bazując na SELECT DISTINCT Name, Town.

Ta lista jest dla mnie punktem wyjścia do dalszych działań, ale żeby miało to ręce i nogi, muszę nadać tym wybranym rekordom jakieś ID. Wielkich wymagań nie mam, zwykłe liczby całkowite. Wydawało mi się, że banał ale siedzę nad tym już kilka wieczorów. Większość przepisów na numerowanie w Accesie doradza użycie autonumeracji, ale z kilku powodów wolałbym tego uniknąć.

Najlepsze co wymyśliłem (i co przynajmniej działa zgodnie z założeniami) to petla, która wybiera po jednym rekordzie i nadaje mu kolejne ID takim zapytaniem:
Kod:
sql = "UPDATE (SELECT TOP 1 ID FROM Temp " _
        & "WHERE ISNULL(ID) " _
        & "ORDER BY Town, Name) AS a " _
        & "SET a.ID = " & nrID

No ale jak się można było spodziewać, demon prędkości to nie jest... :(

Więcej sensu widziałem w zrobieniu tego recordsetem
Kod:
With rsTemp
    .MoveFirst
   
    Do Until .EOF
        nrID = nrID + 1
        .Fields("ID").Value = nrID
        .MoveNext
    Loop
   
End With

a na koniec potraktowanie go UpdateBatch, ale wtedy dostaję komunikat błędu
Cytat:
Informacje o kolumnie klucza są niedostateczne lub niepoprawne. Za dużo wierszy podlegało aktualizacji.
z którym w sumie nie sposób się nie zgodzić, bo przecież identyfikator dopiero nadaję... Co ciekawe w bazie ID się pojawiają, ale są zdublowane dla poszczególnych Town.

Macie jakiś pomysł, żeby zaczęło to sensownie działać?

Aby załączniki zadziałały, obydwa pliki musza być w tym samym miejscu.

ID.rar
Pobierz Plik ściągnięto 17 raz(y) 258.74 KB

ID posta: 358399 Skopiuj do schowka
 
 
Artik 



Wersja: Win Office 365
Pomógł: 2613 razy
Posty: 8614
Wysłany: 15-12-2018, 01:00   

Nie czuję się kompetentny w tych sprawach, ale... :-)
W rozwiązaniu ID_Przez_Update, z każdym rekordem otwierasz i zamykasz połączenie do bazy. Wykonaj te czynności tylko raz. Z pewnością wydajność się zwiększy. Ale czy to najwydajniejsze rozwiązanie, nie mnie osądzać.

Artik
_________________
Persistence is a virtue in the world of programming.
ID posta: 358434 Skopiuj do schowka
 
 
ąćęłńóś
ExcelSpec


Pomógł: 186 razy
Posty: 847
Wysłany: 15-12-2018, 02:29   

Może coś takiego wygibalistycznego, w oparciu o transakcje:

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-example-vb?view=sql-server-2017

można by przerobić ... (?)
ID posta: 358435 Skopiuj do schowka
 
 
ąćęłńóś
ExcelSpec


Pomógł: 186 razy
Posty: 847
Wysłany: 15-12-2018, 10:50   

Artik napisał/a:
Nie czuję się kompetentny w tych sprawach

:-D ... akurat ... :->

Wyglądawszy, że działawszy ... ale (?) ...
Kod:
Option Explicit

Sub Temp_ID_wstaw()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cnstr As String, strSQL As String
    Dim i As Long
   
    cnstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Test.accdb"
    strSQL = "Temp"
   
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    cn.Open cnstr
    rs.Open strSQL, cn, adOpenDynamic, adLockPessimistic, adCmdTable
   
    cn.BeginTrans
        rs.MoveFirst
       
        Do Until rs.EOF
            i = i + 1
            rs!ID = i
            rs.Update
            rs.MoveNext
        Loop
       
        If MsgBox("Czy zachować zmiany dokonane w 'ID' tabeli 'Temp' ?", vbYesNo) = vbYes Then
    cn.CommitTrans
        Else
    cn.RollbackTrans
        End If
   
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub
ID posta: 358441 Skopiuj do schowka
 
 
BrunO 
ExcelSpec



Pomógł: 125 razy
Posty: 541
  Wysłany: 15-12-2018, 21:17   

Artik, odnośnie przerywania połączenia to trochę mi głupio, że o tym nie pomyślałem, bo na to mógłbym sam wpaść. Chyba z założenia, że jest to plan Z, który i tak szybko działał nie będzie, nawet nie próbowałem tego zoptymalizować działając na sprawdzonych elementach kodu. Odpalenie tego na jednym połączeniu wyraźnie przyspiesza proces, ale wciąż te moje 13k rekordów mieli ładnych kilkanaście minut. Docelowo jednak do obrobienia byłyby tylko aktualizacje na kilkadziesiąt wpisów, więc dałoby się to jakoś przeżyć.

ąćęłńóś, no kurde wow! Przecież twoje rozwiązanie zaiwania jak mały samochodzik! Kliknąłem, coś plumknęło, myślę "error" a tu komunikat końcowy :shock:
No dobra, może nie od razu, bo najpierw wyskoczyło "„Przekroczona liczba blokad współużytkowania pliku", no ale po grzebnięciu w rejestrze już śmiga. Teraz muszę jeszcze doczytać, czy zmiana w rejestrze była mądrym posunięciem, ale to inna bajka ;-)

Bardziej zastanawia mnie twoje
Cytat:
... ale (?) ...

To taka figura stylistyczna, czy jest coś, o czym powinienem wiedzieć?

Tak czy inaczej, po raz nie wiem który, DZIĘKI! :beer :beer
ID posta: 358468 Skopiuj do schowka
 
 
Artik 



Wersja: Win Office 365
Pomógł: 2613 razy
Posty: 8614
Wysłany: 15-12-2018, 23:18   

No, to teraz rozumiem sens stosowania transakcji.

Też DZIĘKI ąćęłńóś.

Artik
_________________
Persistence is a virtue in the world of programming.
ID posta: 358473 Skopiuj do schowka
 
 
ąćęłńóś
ExcelSpec


Pomógł: 186 razy
Posty: 847
Wysłany: 16-12-2018, 01:16   

BrunO napisał/a:
taka figura stylistyczna, czy jest coś
Czasami jest to "cóś" ... zwłaszcza jak się ma wszystko "dobro informatyczne" leciwe i niemodernizowane ... pierwotnie próbowałem z DAO, ale niestety "ono sie wyryPAO" ... :-> ... nie trawi nowego formatu "accdb" ... musiałem pojść "na żebry po somsiadach" za nowszym biuroszczakiem ... tak więc nie byłem pewny ...

BrunO napisał/a:
Przekroczona liczba blokad współużytkowania pliku
Rozumiem, że pomocne było:

https://support.microsoft.com/pl-pl/help/815281/file-sharing-lock-count-exceeded-error-message-during-large-transactio

Chyba najrozsądniejsze obejście to "SetOption", bez przegrzebki w "wielkiej tajemnicy okien" ...

Tak na marginesie ... :-) ...

https://answers.microsoft.com/pl-pl/office/forum/office_2007-access/przekroczono-licznik-blokad/f0b40ae9-282a-40be-9d8b-7a5fa8017565?db=5

... ón cóś chiba na tyn tymat wi ... :->

Artik napisał/a:
teraz rozumiem sens stosowania transakcji

Szybkość ... panie ... szybkość ... ale czy zawsze potrzebna, jeśli ma tworzyć problemy ?
ID posta: 358479 Skopiuj do schowka
 
 
BrunO 
ExcelSpec



Pomógł: 125 razy
Posty: 541
Wysłany: 16-12-2018, 08:33   

Nie użyłem SetOption (w przykładzie odnosiło się do DAO i nie byłem pewny czy u mnie zadziała) ale trafilem w sieci na takiego gotowca:

Kod:
cn.Properties("Jet OLEDB:Max Locks Per File") = 200000
wstawiony pomiędzy cn.open a rs.open spełnia swoje zdanie po przywróceniu rejestrowi pierwotnej wartości.

Jeszcze tylko pytane kontrolne. Czy da się jakość zrobić, by
Kod:
strSQL = "Temp"
zamiast całą tablicę wskazywało na jakiegoś selecta. Na chwilę obecną mi to nie przeszkadza, bo i tak działam na tablicy tymczasowej, gdzie mam już rekordy wybrane do obróbki, ale może kiedyś coś takiego przydałoby się zastosować na tablicy docelowej, gdzie warto byłoby wskazać wybrane elementy.
ID posta: 358490 Skopiuj do schowka
 
 
andie 
Exceloholic


Pomógł: 55 razy
Posty: 189
Wysłany: 16-12-2018, 12:09   

BrunO, a czy jakiś powód, dla którego dla pola ID nie używasz pola typu autonumerowanie, które może być automatycznie wypełnione przy dodawaniu rekordów do tabeli TEMP?



andie
ID posta: 358499 Skopiuj do schowka
 
 
BrunO 
ExcelSpec



Pomógł: 125 razy
Posty: 541
Wysłany: 16-12-2018, 12:19   

We wstępie pisałem, że mam nawet kilka powodów.
Pierwszy to taki, że Temp jak nazwa wskazuje nie jest miejscem docelowym. ID nadane w nim automatycznie rozjechałyby się z ich ostatecznym miejscem przeznaczenia. W przykładzie tego nie widać, ale nadawanie ID rozpoczynam od wartości pobranej z bazy właściwej.

Po drugie, prawdopodobnie docelowo baza nie będzie trzymana w accessie, więc co się da staram się pisać względnie uniwersalnie.
ID posta: 358501 Skopiuj do schowka
 
 
ąćęłńóś
ExcelSpec


Pomógł: 186 razy
Posty: 847
Wysłany: 16-12-2018, 12:39   

BrunO napisał/a:
Czy da się jakość zrobić ... zamiast całą tablicę wskazywało na jakiegoś selecta

Znaczy się przy zastosowaniu "Select ..." wyskakuje błąd: "Błąd składniowy w klauzuli FROM" ?
Więc, jak zmienimy na:
Kod:
strSQL = "Select ID From Temp;"

to w połączeniu:
Kod:
rs.Open strSQL, cn, adOpenDynamic, adLockPessimistic, adCmdTable

trzeba usunąć to 'adCmdTable' lub wpisać: 'adCmdText' albo lepiej 'adCmdUnspecified', czyli np.
Kod:
rs.Open strSQL, cn, adOpenDynamic, adLockPessimistic, adCmdUnspecified
ID posta: 358503 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe