Microsoft Access VBA'da SQL sorgusundan veri nasıl alınır? VBA'da veritabanına sorgu nasıl gönderilir VBA'da INSERT, UPDATE, DELETE veritabanına erişim sorguları

Bu ders hakkında SQL sorgularıüzerindeki veri tabanına VBA Erişimi. Veritabanına INSERT, UPDATE, DELETE VBA sorgularının nasıl yapıldığına bakacağız ve ayrıca bir SELECT sorgusundan belirli bir değerin nasıl alınacağını öğreneceğiz.

program yapanlar VBA Erişimi ve bir SQL sunucu veritabanıyla çalışırken, INSERT, UPDATE veya basit bir SQL SELECT sorgusu olsun, genellikle veritabanına bir SQL sorgusu göndermek gibi basit ve gerekli bir görevle karşı karşıya kalırlar. Acemi programcılar olduğumuz için bunu da yapabilmeliyiz, o yüzden bugün tam da bunu yapacağız.

Bu verileri elde etmek için VBA'da kod yazdığımız bir SQL sunucusundan veri alma konusuna zaten değinmiştik, örneğin MSSql 2008'den bir metin dosyasına veri yükleme hakkındaki bir makalede veya ayrıca malzemeye biraz değindik. Access'ten bir Word ve Excel şablonuna veri yükleme, ancak öyle ya da böyle orada bunu yüzeysel olarak değerlendirdik ve bugün bunun hakkında biraz daha ayrıntılı konuşmayı öneriyorum.

Not! Aşağıdaki tüm örnekler, bir Access 2003 ADP projesi ve bir MSSql 2008 veritabanı kullanılarak ele alınmıştır.

Örnekler için başlangıç ​​verileri

Diyelim ki bir yıldaki ayların sayılarını ve adlarını içeren bir test_table tablomuz var (sorgular kullanılarak yapılır) yönetim stüdyosu)

TABLO OLUŞTUR .( NULL DEĞİL, (50) NULL) ON GO

Dediğim gibi, MS SQL 2008 ile çalışacak şekilde yapılandırılmış bir ADP projesi kullanacağız, bu projede bir test formu oluşturdum ve bir resim yazısı ile bir başlat düğmesi ekledim. "Koşmak", yani kodumuzu test etmemiz gerekecek. tüm kodu olay işleyicisine yazacağız " Düğmeye basın».

Veritabanı sorguları VBA'da INSERT, UPDATE, DELETE

Uzun süre uzatmamak için başlayalım, test tablomuza bir satır eklememiz gerekiyor diyelim ( kod yorumlandı)/

Private Sub start_Click() "Dim sql_query As String sorgu dizesini saklamak için bir değişken tanımlayın "İçine ihtiyacımız olan sorguyu yazın sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Yürüt DoCmd.RunSQL sql_query End Sub ile

Bu durumda sorgu, geçerli veritabanı bağlantı ayarları kullanılarak yürütülür. Verilerin eklenip eklenmediğini kontrol edebiliriz.

Gördüğünüz gibi, veriler eklendi.

Bir satırı silmek için aşağıdaki kodu yazıyoruz.

Private Sub start_Click() "Dim sql_query As String sorgu dizesini tutacak bir değişken tanımlayın "İçine bir silme sorgusu yazın sql_query = "DELETE test_table WHERE id = 6" "Yürüt DoCmd.RunSQL sql_query End Sub

Kontrol edersek istenilen satırın silindiğini görürüz.

Verileri güncellemek için sql_query değişkenine bir güncelleme sorgusu yazıyoruz, umarım anlamı anlaşılmıştır.

VBA'da veritabanına sorgu SEÇ

Burada işler diğer SQL yapılarından biraz daha ilginç.

Öncelikle tablodan tüm verileri almamız gerektiğini varsayalım ve örneğin onu işleyeceğiz ve bir mesajda göstereceğiz ve tabii ki başka amaçlar için kullanabilirsiniz, bunun için aşağıdakileri yazıyoruz kod

Private Sub start_Click() "Değişkenlerin bildirilmesi "veritabanından bir kayıt kümesi için Dim RS As ADODB.Recordset "Query string Dim sql_query As String "Mesajdaki toplam verileri görüntülemek için string Dim str As String "Kayıt seti için yeni bir nesne oluşturma RS = Yeni ADODB .Recordset "Sorgu dizisi sql_query = "test_table FROM id, name_mon SEÇ" "Bir mesaj görüntülemek için geçerli proje bağlantı ayarlarını kullanarak sorguyu yürütün str = str & RS.Fields("id") & "-" & RS. Alanlar("name_mon") & vbnewline "sonraki kayda git RS.MoveNext Wend "Çıkış mesajı msgbox str End Sub

Burada, kayıt kümemizdeki tüm değerler arasında geçiş yapmak için zaten VBA Erişim Döngülerini kullanıyoruz.

Ancak, çoğu zaman bir dizi kayıttan tüm değerleri değil, yalnızca bir tanesini, örneğin koduna göre ayın adını almak gerekir. Ve bunun için, bir döngü kullanmak bir şekilde pahalıdır, bu nedenle yalnızca bir değer döndürecek bir sorgu yazıp ona başvurabiliriz, örneğin, ayın adını kod 5 ile alacağız

Private Sub start_Click() "Değişkenlerin bildirilmesi "Veritabanından bir kayıt kümesi için Dim RS As ADODB.Recordset "Query string Dim sql_query As String "Son değeri görüntülemek için string Dim str As String "Kayıt seti için yeni bir nesne oluşturma RS = Yeni ADODB.Recordset "Query string sql_query =" test_table FROM WHERE id = 5 name_mon SEÇİN" "Mevcut proje bağlantı ayarlarını kullanarak sorguyu yürütün RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Değerimizi alın str = RS.Fields(0 ) msgbox str end sub

Evrensellik için, burada zaten hücre adına değil, indeksine göre hitap ettik, yani. 0, ilk değer olan Kayıt Kümesi, sonunda değeri aldık "Mayıs".

Gördüğünüz gibi, her şey oldukça basit. Sık sık veritabanından belirli bir değer almanız gerekiyorsa ( son örnekte olduğu gibi), o zaman tüm kodu ayrı bir işlevde (VBA Access 2003'te bir işlev nasıl yazılır) tek bir giriş parametresiyle, örneğin ay koduyla ( örneğimizi dikkate alarak) ve basitçe, bu değeri görüntülemek gerektiğinde, ihtiyacımız olan işlevi gerekli parametre ile çağırın ve işte bu kadar, VBA kodunu önemli ölçüde azaltacağız ve programımızın algısını iyileştireceğiz.

Hepsi bugün için. İyi şanlar!

Hey, az önce SQL ifadelerimi VBA'ya nasıl koyacağım (veya en azından onları yazacağım) hakkında bir şey öğrendim, ancak döndürülen verileri nasıl alacağımı bilmiyorum?

Sadece zaman çerçevelerini değiştirmeye karşı oldukça düzenli olarak çalıştırdığım sorgulara dayalı birkaç formum (form grafiği) var (örneğin, ayda en iyi 10 satış türü). Ardından, grafik nesnesini otomatik olarak bir PowerPoint sunumuna geçiren prosedürlerim var. Bu yüzden tüm bu sorguları önceden oluşturdum (ör. 63) ve sığdırmak için bir grafik şeklim var (evet evet... 63... Bunun kötü olduğunu biliyorum) ve sonra tüm bunlar "aç/kapat" »an bir sonraki olaya giden olay (hack olmak için en iyi girişimim... veya bir domino; hangisini tercih ederseniz edin).

Bu yüzden, sonunda hepsini orada yapabilmek için VBA'da SQL deyimlerini nasıl kullanacağımı öğrenmeye çalışıyorum (tüm bu grafik şekillerini kaydetmem gerekebilir ama bilmiyorum çünkü açıkçası anlayışı kaçırıyorum) .

Yukarıda sorduğum soru dışında kim tavsiye verebilir? Teşekkür ederim

6 yanıt

10

Biraz eski, bu yüzden konuyla ilgili bir kitap almak isteyebilirsiniz. Ancak burada kaynaklara tonlarca erişim ve ayrıca biraz eğitim ve örnek var. Ama temelde...

Dim dbs Veritabanı Olarak Dim rs Kayıt Kümesi Olarak Dim strSQL As String Set dbs = CurrentDb strSQL = "sorgunuz burada Set rs = dbs.OpenRecordset(strSQL) Değilse (rs.EOF Ve rs.BOF) Sonra rs.MoveFirst" kullanarak sonuçları alın rs.Fields() Aksi takdirde "Sonuçları kullan

Yorumun arkasında: Records sınıfına bir göz atın. Sorgunuzdan döndürülen sütunlar olan alanlar adı verilen bir koleksiyon içerir. Devrenizi bilmeden söylemek zor ama şöyle bir şey...

Rs.MoveFirst rs.EOF Değilken Yap "rs("SomeFieldName") rs.MoveNext Loop gibi bir şey yap

Dediğim gibi, konuyla ilgili bir kitap almak en iyisi, tonlarca örneği var.

Parametreli QueryDef'i kullanın ve onu VBA'dan çağırın.
Sorgunun tasarımı daha kolaydır... test edilmesi kolaydır... ve VBA veya bir form kullanılarak kolayca erişilebilir.

qd'yi sorgu tanımlı olarak ayarlayın qd = currentdb.querydefs!myquerydef qd.parameters!parm1=val1

veya qd.execute

rs'yi kayıt kümesi kümesi olarak dim rs = qd.openrecordset()

İşte satır içi almak için yeniden düzenlemeyi düşünebileceğiniz ve kodunuzun herhangi bir yerinde yeniden kullanabileceğiniz bir özellik.

Bu nedenle, SQL ifadeniz için bir dize oluşturun veya oluşturun ve sanitize, NON SQL INJECTION dizenizi bir argüman olarak girin :)

StrSQL = "Müşteriden WHERE ID = " SEÇİN * &sureParamIsNotSQLInjection(müşteriID)

Ardından, verileri/kayıt kümesini almanız/talimatları yürütmeniz gereken yerden işlevi/alt işlevi çağırın. Yalnızca bir UPDATE deyimi çalıştırabileceğiniz veya tek bir değer alabileceğiniz veya tam gelişmiş kayıtları alabileceğiniz birden çok veri erişim işlevi/alt öğesi oluşturarak.

Buradaki anahtar, bu özelliklerin hepsini tek bir yerde canlı tutmak ve her yerde kullanmaktır. İşte VBScript'te bir örnek.

Alt DoStuff(strSQL) Set adoCon = Server.CreateObject("ADODB.Connection") strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db\Database.mdb") "strConnString = "SÜRÜCÜ=(Microsoft Access Sürücüsü (*.mdb)); DBQ=" & Server.MapPath("db\Database.mdb") adoCon.Open strConnString Set rsMain = Server.CreateObject("ADODB.Recordset") rsMain.Open strSQL, adoCon Yaparken rsMain.EOF müşteriAdı = rsMain(" MüşteriAdı") "aptalca örnek RsMain.MoveNext Döngü rsMain.Close Set adoCon = Nothing End Sub

Bunu yapmanın başka bir yolu, görünüşe göre kimsenin bahsetmemiş olduğu gibi, planınızı depolanmış tek bir QueryDef'e bağlamak ve ardından çalışma zamanında QueryDef'in üzerine yazmaktır. Şimdi, çoğu bağlam için depolanan QueryDef'leri değiştirmenizi önermiyorum çünkü bu, frontal şişkinliğe neden olur ve genellikle gerekli değildir (depolanmış bir QueryDef kullandığınız çoğu bağlamda, içinde bulundukları bağlamda bir şekilde veya başka bir şekilde filtrelenebilir. örneğin, RecordSource formlarından biri olarak kullanıldığında, DoCmd.OpenForm'a bir bağımsız değişken iletmeniz yeterlidir).

Grafikler farklıdır çünkü SQL sürüş grafikleri çalışma zamanında değiştirilemez.

Bazıları seçenekler önerdi, ancak üzerinde seçenekler içeren bir SQL dizesi kullanan bir grafik içeren bir form açmak, varsayılan seçenekler iletişim kutularını açacaktır. Bundan kaçınmanın bir yolu, ölçütleri toplamak için bir iletişim kutusu formu kullanmak ve ardından iletişim kutusundaki kontrollere parametre vb. olarak referanslar ayarlamaktır:

PARAMETRELER!! uzun;

Form bağlantılarını kullanıyorsanız, bunu yapmanız önemlidir, çünkü Access 2002'den itibaren, denetimler boş olduğunda Jet İfade Hizmeti bunları her zaman doğru şekilde işlemez. Bunların parametre olarak belirtilmesi bu sorunu (Access XP'den önce mevcut olmayan) düzeltir.

İsterseniz bir grafik için QueryDef'i yeniden yazmanız gereken durumlardan biri, kullanıcının TOP N SQL deyiminde N'yi seçmesine izin vermektir. Başka bir deyişle, İLK 5 veya İLK 10 veya İLK 20'yi seçebilmek istiyorsanız, N parametrelendirilemeyeceği için saklanan QueryDef'i değiştirmeniz gerekecektir.

Erişim"myQuery" adlı bir sorgu oluşturucu ile tasarlanmış bir sorgu kaydetti. Veritabanı sisteme bir ODBC bağlantısı üzerinden bağlanır. Makroların hepsi dahildir.

mükemmel aracılığıyla veritabanına bağlanmak için bir ADODB bağlantısına sahiptir.

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = Yeni ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" ."MyDatabase.accdb"yi Aç Sonu

Genellikle devam edin ve sadece SQL'inizi yazın, ki bu gayet iyi ve sonra sadece şöyle bir şey yapın:

Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...

Ancak erişim veritabanında sakladığım sorguya erişmek istiyorum. Öyleyse, yeni bağlandığım veritabanındaki saklı sorguyu nasıl arayabilirim.

Zaten denedim

1. con.Execute("EXEC myQuery"), ancak bana bunun myQuery'yi bulamayacağını söyledi.
2. rs.Open "myQuery", con ancak bu geçersiz ve ondan SELECT /etc deyimleri istiyor

5 yanıt

6

Saklı bir prosedür olarak düşünebileceğinizi düşünüyorum.

Dim sqlQuery As String'den hemen önce başlarsak

Cmd'yi yeni olarak kıs ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()

Ardından, kayıt işi işinizi ondan sonra alın.

1

neredeyse oradaydın

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "z:\docs\MyDatabase.accdb" Con.Execute ile Bitir "Sorgum"

Exek'i bir kenara bırakın.

Seçenekler de ekleyebilirsiniz, bu biraz eski ama yardımcı olacaktır:

0

Bu bir tür hack'tir, ancak bir istekte bulunabilirsiniz. Yani, sql satırını aşağıdakiyle değiştirin:

SqlQuery = "SorguAdı'ndan * SEÇİN;"

Bu programı çalıştırmadan önce, Access veritabanının kaydedildiğinden emin olmanız gerekir, yani. Ctrl+S tuşlarına basın (sorguyu Access'te çalıştırmak yeterli değildir).

0

Şunları kullanarak Access'te zaten depolanmış olan bir güncelleme sorgusu çalıştırabildim:

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

Sorgu adındaki boşlukları hem Access veritabanında hem de yürütme deyiminde alt çizgilerle değiştirene kadar bu bana hatalar verdi.

0

Bu konu oluşturulalı uzun zaman oldu. Her şeyi doğru anlarsam, yararlı bir şeyler ekleyebilirim. OP'nin tanımladığı şeye, ACCDB'de saklanan bir sorgudan SQL'i DAO veya ADOBD aracılığıyla VBA'da çalıştırmak için kullanma süreci olan bir isim verdim. Notlarımda OPP kısaltması ve nesne adı için önek/sonek olmasına rağmen ona "nesne özelliği sağlayıcısı" adını verdim.

Buradaki fikir, ACCDB'de var olan bir nesnenin (genellikle bir sorgu), VBA'da kullanılması gereken bir özelliği (genellikle SQL) ortaya çıkarmasıdır. Bunun için SQL'i sorgulardan çıkarmak için bir işlev oluşturdum; aşağıya bakınız. Uyarı: üzgünüm ama bunların hepsi DAO'da, ADODB'yi pek kullanmıyorum. Umarım bu fikirleri hâlâ faydalı bulursunuz.

Hatta bu OPP sorgularından gelen SQL'de değiştirme parametrelerini kullanmak/eklemek için bir yöntem geliştirecek kadar ileri gittim. Daha sonra VBA'da SQL kullanmadan önce değiştirmeyi yapmak için VBA.Replace() kullanıyorum.

DAO nesnesinin ACCDB'deki SQL sorgusuna giden yolu aşağıdaki gibidir:

MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL

Neyin değiştirilmesi gerektiğini değerlendirerek ve gerçek veritabanında bulunamayan parametre için alışılmadık bir ad seçerek değiştirilebilir parametreler kullanıyorum. Çoğunlukla, yaptığım ikameler yalnızca alan veya tablo adları veya WHERE ve HAVING yan tümce ifadeleridir. Bu yüzden onlara "(ReplaceMe00000001)" gibi şeyler diyorum ve sonra işi yapmak için Değiştir() işlevini kullanıyorum...

SqlText = VBA.Replace(sqlText, "(ReplaceMe00000001)", "SomeActualParameter") ...

ve sonra VBA'da sqlText kullanın. İşte çalışan bir örnek:

Genel İşlev MySqlThing() Dim sqlText as String As myParamater Dim Dize olarak myExpression Dim "Her şeyi ayarla. . sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Sonra SQL'i kullanın. db.Execute sqlText, dbFailOnError End Function Function getSqlTextFromQuery(ByVal oppName As String) String Olarak Dim uygulaması Access.Application Olarak Dim db DAO.Database Olarak Dim qdefs DAO.QueryDefs Olarak Dim qdef As DAO.QueryDef Dim sqlText As String Set app = Access.Application Set db = app.CurrentDb Set qdefs = db.QueryDefs Set qdef = qdefs(oppName) oppGetSqlText = qdef.SQL End Function

Otomatikleştirmem gereken bir Excel dosyam var. Kullanıcı bir Excel raporu açtığında, verileri yenilemesi istenir. Evet derlerse, sorguyu çalıştırmam gerekiyor...

JS ActiveX ADO'dan MS Access 2013'te görünüm sorgusu aracılığıyla VBA makrosu nasıl çalıştırılır? VBA işlevi, geçerli kullanıcının şu şekilde oturum açmasını sağlamaktır: Public Declare...

Excel vba'dan bir erişim veritabanında saklanan bir sorguyu çalıştırmaya çalışıyorum. Sorguyu erişim veritabanında açıp çalıştırırsam iyi çalışıyor, ancak bir modülden çalıştırdığımda bir hata veriyor...

VBA'da MS Access 2007'de kaydedilmiş bir sorgu nasıl yürütülür? SQL'i kopyalayıp VBA'ya yapıştırmak istemiyorum. Ben sadece sorgu adını yapmayı tercih ederim. Bu işe yaramaz... VBA sorguyu bulamıyor...

VBA kodunu kullanarak ms-access veritabanındaki kayıtları döndürmek için nasıl sorgulama yapabilirim?

Excel VBA kodundan veya makrodan MS erişim db'sinde sorgu nasıl yürütülür. MS-Access isteği, Excel'den iletilmesi gereken bazı parametreleri alır. Teşekkür ederim

Aşağıdaki gönderiye çok benzer bir durumum var: vba aracılığıyla bir grafik oluşturmak için excel 2010'a erişim talep ediyorum Benim durumumda, bir tabloyu dışa aktarıyorum ama dosya için çok daha fazlasını yapmak istiyorum...

Access 2007'de kaydedilmiş bir UPDATE geçiş sorgum var. Geçiş sorgusuna çift tıkladığımda başarıyla çalışıyor. Bu sorgunun VBA'dan yürütülmesini nasıl sağlayabilirim? İsterim...

Excel'den Access'e aktarmam gereken çok büyük bir veri kümem var (~800 bin satır). Ancak, toplamı %90'a varan belirli bir sütun değerine sahip satırları yok sayabilirim...

Excel VBA içinde bir MDX sorgusu yürütmenin bir yolu var mı? ADO aracılığıyla yapılabileceğini düşündüm, tıpkı SQL'de olduğu gibi (evet, SQL'in MDX'ten farklı olduğunu biliyorum - çoğu zaman bir sorun...

Bir makro ile OpenRequest Access veritabanlarında, seçme sorgularını ve çapraz sorguları Veri Sayfası görünümünde, Tasarım görünümünde veya Önizleme görünümünde açabilirsiniz. Bu eylem bir değişiklik isteği başlatır. Sorgu için veri giriş modunu da seçebilirsiniz.

Not: Bu makro yalnızca bir Access veritabanı ortamında (MDB veya ACCDB) kullanılabilir. Access Project Environment (ADP) kullanıyorsanız, makrolara bakın Açık Görünüm, Bir saklı yordam açın Ve AçıkFonksiyon. makro OpenRequest Access web uygulamalarında mevcut değildir.

Ayar

makro OpenRequest aşağıdaki argümanlara sahiptir:

notlar

Argüman için ise Görüş set değeri Masa, Access, seçme sorgusu, çapraz sorgu, birleşik sorgu veya sunucu sorgusu kullanıldığında sonuç kümesini görüntüler, özellik İade Kayıtları hangisi önemli Evet. Bir değişiklik talebi, bir veri tanımı talebi veya sunucuya yapılan bir talep ise, özellik İade Kayıtları hangi ayarlandı HAYIR, istek yürütülür.

makro OpenRequest gezinti bölmesinde bir sorguyu çift tıklatmakla veya gezinti bölmesinde sorguya sağ tıklayıp bir görünüm seçmekle aynıdır. Makro kullanırken ek seçenekler belirleyebilirsiniz.

tavsiye

Gezinti bölmesinden bir sorguyu makro tasarımcı penceresine sürükleyebilirsiniz. Bu otomatik olarak bir makro oluşturacaktır. OpenRequest sorguyu veri sayfası görünümünde açar.

Sorgu açıkken yapıcıya geçerseniz, bağımsız değişkenin değeri veri modu kaldırıldı. Kullanıcı Veri Sayfası görünümüne dönse bile bu ayarın hiçbir etkisi olmayacaktır.

Değişiklik isteklerini çalıştırdığınızda tipik olarak görünen sistem mesajlarını görüntülemek istemiyorsanız (bunun bir değişiklik isteği olduğunu ve etkilediği kayıt sayısını söylerler), bunları bir makro ile kapatabilirsiniz. Uyarı Ayarla.

Makro çalıştırmak için OpenRequest Visual Basic for Applications (VBA) modülünde, yöntemi kullanın OpenRequest nesne DoCmd.