Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - feraz - 07/02/2020
(07/02/2020, 07:33)accessman yazdı: Beynimi yakacaksın bir gün
Anladığımı zannedip yapmaya çalışıyorum ama sana yetişemiyorum
Evet abey @berduş hocamız gerçekten çok zeki birisi maşAllah.Biz güncelleme,veri ekle deyince klasik yöntemleri kullanıyoruz hocamız klasını konuşturmuş.Yinede ben bu şekilde kullanmazdım tercihen fakat toplu aktarmada kullanırdım.
Alttaki gibi düşünmek bile marifet.Yani in ile ile tablodaki ve exceldeki aynı olanları buldurup sildirmek herkes düşünemez
Özetle berduş hocamız şöyle yapmış.
Excele koodaki yöntemle bağlantı kurup ayrı bir tabloya aldırmış ve Tablo1 deki verileride Excel ile tablo1 de aynı olmayanları karşılaştırıp aynı olanları silmiş.Sonrada bağlantı ie oluşan tablodaki verileri insert into ile tablo1 e aktarıp geçici oluşan tabloyuda silivermiş.insert into nun bu şekilde topluca aktarmasınıda öğrenmiş olduk
Gerçi ben önceden kullanmazdım into olayını.
Berduş hocamızın sadece kullandığı yöntemde hoşuma gitmeyen olay mükerrer kayıtlar olunca onlarıda siliyor çünkü Excel ile karşılaşmasında aynı veriler olduğu için onlarıda siliyor.Yani 10 tane a1 varsa 10unuda güncellemesi gerekti.
where [KOD] in (select [KOD] from TmpTablo)
Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - berduş - 07/02/2020
Aşağıdaki resimde test sonuçları var ne kadar güvenilir emin değilim kendiniz karar verirsiniz ama bana süreyi daha kısa gösteriyor gibi geldi
Testin nasıl yapıldığını anlatayım
ekleme kodları dışında her şeyiyle özdeş 4 çalışma var
testin ilk kısmında boş tablolara excelden 1500 kayıt ekleniyor
(bu 1500 kaydın 750 si asıl exceldeki -3000 kayıtlık - kayıtlarla birebir aynıyken
750 tanesinde sadece 1 alana fazladan a harfi eklenmiş)
2 kısımda ise bu sefer 3000 kayıtlı asıl dosyadan veri almaya çalışıyoruz
yani 2. testte hem ekleme hem de güncelleme yapıyoruz
Transfer Yöntemleri
1. Yöntemde normal ilk kullaılan kod var
Do While Not rs.EOF And Not rs.BOF 'Tablo güncelleme icin(Exceldeki recordseti icine alinan veriler icin döngü)
If DCount("[kod]", "tesisler", "[kod] = '" & rs(0) & "'") > 0 Then 'Eger tesisler deki kod sütununda excelden alinan recordset icindeki veri mevcutsa
CurrentDb.Execute _
"UPDATE tesisler SET [kod] = """ & rs(0) & """," & _
"[Kaynak] = """ & rs(1) & """," & _
"[Tarih] = '" & rs(2) & "'," & _
"[tesis] = """ & rs(3) & """," & _
"[bolum] = """ & rs(4) & """," & _
"[Tespit_eden] = """ & rs(5) & """," & _
"[gozlem] = """ & rs(6) & """," & _
"[oneriler] = """ & rs(7) & """," & _
"[sorumlu] = """ & rs(8) & """," & _
"[termin_tarihi] = '" & rs(9) & "'," & _
"[sorumlu_gorusu] = """ & rs(10) & """," & _
"[tamamlama_tarihi] = '" & rs(11) & "'," & _
"[durum] = '" & rs(12) & "'" & _
"WHERE [kod] = """ & rs(0) & """"
say = say + 1
ElseIf DCount("[kod]", "tesisler", "[kod] = """ & rs(0) & """") = 0 Then 'Eger tesisler deki kod sütununda excelden alinan recordset icindeki veri mevcut degilse
CurrentDb.Execute _
"INSERT INTO tesisler" _
& " ([kod], [kaynak], [tarih], [tesis], [bolum],[Tespit_eden],[gozlem],[oneriler],[sorumlu],[termin_tarihi],[sorumlu_gorusu],[tamamlama_tarihi],[durum])" _
& " VALUES ( """ & rs(0) & """ , """ & rs(1) & """ , '" & Format(rs(2), "dd.mm.yyyy") & "', """ & rs(3) & """, """ & rs(4) & """, """ & rs(5) & """, """ & rs(6) & """, """ & rs(7) & """, """ & rs(8) & """, '" & Format(rs(9), "dd.mm.yyyy") & "', """ & rs(10) & """, '" & Format(rs(11), "dd.mm.yyyy") & "', """ & rs(12) & """ );" 'Kayit ekle exceldekinde olup accesste mevcut olmayan veri
say1 = say1 + 1
End If
rs.MoveNext
Loop
2. Yöntemde
Do While Not rs.EOF And Not rs.BOF 'Tablo güncelleme icin(Exceldeki recordseti icine alinan veriler icin döngü)
SrgK = " select * from tesisler where [kod]='" & Replace(rs(0), "'", "''") & "'"
rsK.Open SrgK, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsK.RecordCount = 0 Then 'rsK.MoveFirst
rsK.AddNew
rsK(1) = rs(0)
rsK(2) = rs(1)
rsK(3) = rs(2)
rsK(4) = rs(3)
rsK(5) = rs(4)
rsK(6) = rs(5)
rsK(7) = rs(6)
rsK(8) = rs(7)
rsK(9) = rs(8)
rsK(10) = rs(9)
rsK(11) = rs(10)
rsK(12) = rs(11)
rsK(13) = rs(12)
rsK.Update
say1 = say1 + 1
Else
' rsK.EditMode
' rsK(1) = rs(0)
rsK(2) = rs(1)
rsK(3) = rs(2)
rsK(4) = rs(3)
rsK(5) = rs(4)
rsK(6) = rs(5)
rsK(7) = rs(6)
rsK(8) = rs(7)
rsK(9) = rs(8)
rsK(10) = rs(9)
rsK(11) = rs(10)
rsK(12) = rs(11)
rsK(13) = rs(12)
rsK.Update
say = say + 1
End If
rsK.Close
rs.MoveNext
Loop
3. ve 4. yöntem temelde aynı 3. yöntemde ortak kayıtlar silini exceldekiler tekrar alınırken
4.de ortaklardan farklı olan güncellenip tabloda olmayanlar ekleniyor
3. Yöntem
BasZmn = Now
'hy_Geçici_tablo_var_mı_varsa_sil_____
If Not IsNull(DLookup("Name", "MSysObjects", "Name='TmpTablo'")) Then DoCmd.DeleteObject acTable, "TmpTablo"
'hy_tablo_kontrol____________________Bitti
TestBil = ""
DoCmd.TransferSpreadsheet TransferType:=acLink, _
TableName:="TmpTablo", _
SpreadsheetType:=10, _
FileName:=yahya, _
HasfieldNames:=True, _
Range:="Sayfa1$B2:N" '"Hy bin 2!B3:E"
'hy Tablo Boş Mu_____________
Dim SayRS As New ADODB.Recordset
Dim SaySql As String
SaySql = "select * from TmpTablo" 'öğretmenler tablosundan öğretmen seç
SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
KytSay = SayRS(0)
Krt = " where [" & SayRS(0).Name & "] Is Not Null"
SayRS.Close
SayRS.Open SaySql & Krt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If SayRS.RecordCount = 0 Then
DoCmd.DeleteObject acTable, "TmpTablo"
MsgBox "Tabloda veri yok"
Exit Sub
End If
SayRS.Close
'hy Excel Boş Mu_____________Bitti
KytSy1 = DCount("*", "TmpTablo")
CurrentDb.Execute " delete from tesisler where [KOD] in (select [KOD] from TmpTablo)"
KytSy1Gncl = KytSy1 - DCount("*", "tesisler")
CurrentDb.Execute " INSERT INTO tesisler " & _
" ( [kod], [kaynak], [tarih], [tesis], [bolum],[Tespit_eden],[gozlem],[oneriler],[sorumlu],[termin_tarihi],[sorumlu_gorusu],[tamamlama_tarihi],[durum] ) " & _
" SELECT [KOD],[Kaynak],[Tarih],[Tesis],[Yer/Bölüm],[Tespit Yapan],[Uygunsuzluk/Ramak Kala/Gözlem],[Önerilen Aksiyon],[sorumlu],[Termin tarihi],[Sorumlu Görüşü/Kararı],[Tamamlama Tarihi],[Durum] " & _
" FROM TmpTablo where [KOD] Is Not Null"
KytSyEk = DCount("*", "tesisler") - KytSy1
Me.tesisler.Requery
CurrentDb.TableDefs.Refresh
4. Yöntem
BasZmn = Now
'hy_Geçici_tablo_var_mı_varsa_sil_____
If Not IsNull(DLookup("Name", "MSysObjects", "Name='TmpTablo'")) Then DoCmd.DeleteObject acTable, "TmpTablo"
'hy_tablo_kontrol____________________Bitti
TestBil = ""
DoCmd.TransferSpreadsheet TransferType:=acLink, _
TableName:="TmpTablo", _
SpreadsheetType:=10, _
FileName:=yahya, _
HasfieldNames:=True, _
Range:="Sayfa1$B2:N" '"Hy bin 2!B3:E"
'hy Tablo Boş Mu_____________
Dim SayRS As New ADODB.Recordset
Dim SaySql As String
SaySql = "select * from TmpTablo"
SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
KytSay = SayRS(0)
Krt = " where [" & SayRS(0).Name & "] Is Not Null"
SayRS.Close
SayRS.Open SaySql & Krt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If SayRS.RecordCount = 0 Then
DoCmd.DeleteObject acTable, "TmpTablo"
MsgBox "Tabloda veri yok"
Exit Sub
End If
SayRS.Close
'hy Excel Boş Mu_____________Bitti
SaySql = " SELECT Count([TmpTablo]![KOD]) AS GnclSay " & _
" FROM TmpTablo INNER JOIN tesisler ON TmpTablo.KOD = tesisler.kod " & _
" WHERE ((([tesisler]![kaynak] & [tesisler]![tarih] & [tesisler]![tesis] & [tesisler]![bolum] & " & _
" [tesisler]![tespit_eden] & [tesisler]![gozlem] & [tesisler]![oneriler] & [tesisler]![sorumlu] & " & _
" [tesisler]![termin_tarihi] & [tesisler]![sorumlu_gorusu] & [tesisler]![tamamlama_tarihi] & " & _
" [tesisler]![durum])<>([TmpTablo]![Kaynak] & [TmpTablo]![Tarih] & [TmpTablo]![Tesis] & " & _
" [TmpTablo]![Yer/Bölüm] & [TmpTablo]![Tespit Yapan] & [TmpTablo]![Uygunsuzluk/Ramak Kala/Gözlem] & " & _
" [TmpTablo]![Önerilen Aksiyon] & [TmpTablo]![Sorumlu] & [TmpTablo]![Termin Tarihi] & " & _
" [TmpTablo]![Sorumlu Görüşü/Kararı] & [TmpTablo]![Tamamlama Tarihi] & [TmpTablo]![Durum])));"
SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
GuncellenecekKyt = SayRS(0)
SayRS.Close
SqlGuncelle = " UPDATE TmpTablo INNER JOIN tesisler ON TmpTablo.KOD = tesisler.kod " & _
" SET " & _
" tesisler.kaynak = [TmpTablo]![Kaynak], " & _
" tesisler.Tarih = [TmpTablo]![Tarih], " & _
" tesisler.tesis = [TmpTablo]![Tesis], " & _
" tesisler.bolum = [TmpTablo]![Yer/Bölüm], " & _
" tesisler.tespit_eden = [TmpTablo]![Tespit Yapan], " & _
" tesisler.gozlem = [TmpTablo]![Uygunsuzluk/Ramak Kala/Gözlem], " & _
" tesisler.oneriler = [TmpTablo]![Önerilen Aksiyon], " & _
" tesisler.sorumlu = [TmpTablo]![sorumlu], " & _
" tesisler.termin_tarihi = [TmpTablo]![Termin tarihi], " & _
" tesisler.sorumlu_gorusu = [TmpTablo]![Sorumlu Görüşü/Kararı], " & _
" tesisler.tamamlama_tarihi = [TmpTablo]![Tamamlama Tarihi], " & _
" tesisler.durum = [TmpTablo]![Durum] " & _
" WHERE (((TmpTablo.KOD) In (SELECT tesisler.kod " & _
" FROM TmpTablo INNER JOIN tesisler ON TmpTablo.KOD = tesisler.kod " & _
" WHERE ((([tesisler]![kaynak] & [tesisler]![tarih] & [tesisler]![tesis] & [tesisler]![bolum] & " & _
" [tesisler]![tespit_eden] & [tesisler]![gozlem] & [tesisler]![oneriler] & [tesisler]![sorumlu] & " & _
" [tesisler]![termin_tarihi] & [tesisler]![sorumlu_gorusu] & [tesisler]![tamamlama_tarihi] & " & _
" [tesisler]![durum])<> " & _
" ([TmpTablo]![Kaynak] & [TmpTablo]![Tarih] & [TmpTablo]![Tesis] & [TmpTablo]![Yer/Bölüm] & " & _
" [TmpTablo]![Tespit Yapan] & [TmpTablo]![Uygunsuzluk/Ramak Kala/Gözlem] & " & _
" [TmpTablo]![Önerilen Aksiyon] & [TmpTablo]![Sorumlu] & [TmpTablo]![Termin Tarihi] & " & _
" [TmpTablo]![Sorumlu Görüşü/Kararı] & [TmpTablo]![Tamamlama Tarihi] & [TmpTablo]![Durum]))) )));"
CurrentDb.Execute SqlGuncelle
SaySql = " SELECT Count(TmpTablo.KOD) AS SayKOD " & _
" FROM tesisler RIGHT JOIN TmpTablo ON tesisler.kod = TmpTablo.KOD " & _
" GROUP BY tesisler.kod " & _
" HAVING (((Count(TmpTablo.KOD)) Is Not Null) AND ((tesisler.kod) Is Null))"
SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
EklenecekKyt = SayRS(0)
SayRS.Close
SqlEkle = " INSERT INTO tesisler ( kod, kaynak, tarih, tesis, bolum, Tespit_eden, gozlem, oneriler, sorumlu, termin_tarihi, sorumlu_gorusu, tamamlama_tarihi, durum )" & _
" SELECT TmpTablo.KOD, TmpTablo.Kaynak, TmpTablo.Tarih, TmpTablo.Tesis, TmpTablo.[Yer/Bölüm], TmpTablo.[Tespit Yapan], TmpTablo.[Uygunsuzluk/Ramak Kala/Gözlem], TmpTablo.[Önerilen Aksiyon], TmpTablo.sorumlu, TmpTablo.[Termin tarihi], TmpTablo.[Sorumlu Görüşü/Kararı], TmpTablo.[Tamamlama Tarihi], TmpTablo.Durum" & _
" FROM tesisler RIGHT JOIN TmpTablo ON tesisler.kod = TmpTablo.KOD" & _
" WHERE (((TmpTablo.KOD) Is Not Null) AND ((tesisler.kod) Is Null))"
CurrentDb.Execute SqlEkle
Me.tesisler.Requery
CurrentDb.TableDefs.Refresh
'DoCmd.DeleteObject acTable, "TmpTablo"
BitZmn = Now
TransferKarsilastirma_hy.rar
(Dosya Boyutu: 607,83 KB | İndirme Sayısı: 12)
Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - adnan - 07/02/2020
Merhaba,
Sn.beduş hocam elinize sağlık çok güzel örnek çok emek vermişiniz teşekkür ederim.
Bir şey kafama takıldı 5 tablolu Access dosyasında tabloların hepsi birbiriyle ilişkili Access tablosuna veri çekilebilirim ilişkiler sorun çıkarırmı?
Saygılarımla
Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - berduş - 07/02/2020
ilişkileri görmeden bir şey diyemem isterseniz yeni konu açarak dosyanızı paylaşın çözmeye çalışalım
Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - accessman - 02/03/2020
(06/02/2020, 14:04)berduş yazdı: TransferSpreadsheet yöntemiyle ilgili genel bilgiler dilerim işinize yarar
DoCmd.TransferSpreadsheet acLink, "Dosya türü excel/versiyonu", "hangi isimle bağlanacak", "tamAdresveAd", "Başlık var mı", "Hangi aralık"
DoCmd.TransferSpreadsheet _
TransferType:=acLink, _
TableName:="TmpTablo", _
SpreadsheetType:=10, _
FileName:=yahya, _
HasfieldNames:=True, _
Range:="B3:E" '"Hy bin 2!B3:E"
TransferType:= transfer türü
İsim - Sayısal değer - Tanım
acImport 0 Varsayılan değerdir, içeri aktatrır
acExport 1 Başka dosyaya gönderir
acLink 2 Başka dosyadaki veriye bağlantı sağlar
TableName:="Hangi İsimle bağlanacak-acLink İse/hangi tabloya aktarılacak"
SpreadsheetType:= tablo türü
İsim Sayısal Değer Tanım
acSpreadsheetTypeExcel3 0 Microsoft Excel 3.0 format
acSpreadsheetTypeExcel4 6 Microsoft Excel 4.0 format
acSpreadsheetTypeExcel5 5 Microsoft Excel 5.0 format
acSpreadsheetTypeExcel7 5 Microsoft Excel 95 format
acSpreadsheetTypeExcel8 8 Microsoft Excel 97 format
acSpreadsheetTypeExcel9 8 Microsoft Excel 2000 format
acSpreadsheetTypeExcel12 9 Microsoft Excel 2010 format
acSpreadsheetTypeExcel12Xml 10 Microsoft Excel 2010/2013/2016 XML format (.xlsx, .xlsm, .xlsb)
FileName:=Dosya yaolu ve adı "d:\deneme\erzak.xlsm" gibi
HasfieldNames:=Satır başlığı var mı True(var)/False(yok)
Range:=veri alınacak bölge "Hy bin 2!B3:E" --> "Hy bin 2" sayfasındaki "B3" ve "E" stunları arasındaki verileri al teşekkürler
Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - accessman - 02/03/2020
Baştan sona tekrar gezdim bir çok problem ve hepsinin çözümü yazılmış kafam karıştı
son hali için hangisini indireceğim bilemedim yapılmak istenen galiba şuydu
excel tablosu alınırken tablo var mı bakacak
eğer tablo var ve aynı alanlar var ise seçilen kritere göre aynı olan kayıtlar yenilenecek
eğer excelde var ama tabloda yoksa eklenecek
eğer excelde yok ama tabloda varsa tablodaki kayıt pasife alınacak veya silineneler tablosuna taşınacak
bunları yapan eklenen hangi örnekti
|