Klicanje poizvedbe sql iz dostopne kode vba. Ustvarjanje poizvedbenega niza SQL v VBA. Poizvedbe v zbirko podatkov INSERT, UPDATE, DELETE v VBA
Dostop shranil poizvedbo, razvito z graditeljem poizvedb z imenom "myQuery". Baza podatkov je s sistemom povezana preko ODBC povezave. Vsi makri so vključeni.
Excel ima povezavo ADODB za povezavo z bazo podatkov
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "MyDatabase.accdb" End With
Običajno preprosto napišete svoj SQL, kar je popolnoma v redu, nato pa naredite nekaj podobnega
Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...
Vendar želim dostopati do poizvedbe, ki sem jo shranil v podatkovni zbirki za dostop. Torej, kako lahko pokličem shranjeno poizvedbo v bazi podatkov, ki sem jo pravkar povezal.
Že poskusil
- con.Execute("EXEC myQuery") ampak ta mi je rekel, da ne more najti myQuery.
- rs.Odpri "myQuery", con vendar je ta neveljaven in želi iz njega stavke SELECT /etc
5 odgovorov
6
Mislim, da si lahko predstavljate to kot shranjeno proceduro.
Če začnemo tik pred Dim sqlQuery As String
Zatemni cmd kot nov ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()
Nato prevzamete svojo službo za zapise.
1
Skoraj ste bili tam:
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" End With con.Execute "Moja poizvedba"
Pusti Exec ob strani.
Dodate lahko tudi parametre, to je nekoliko zastarelo, vendar bi moralo pomagati:
0
To je neke vrste kramp, vendar ga lahko zahtevate. To pomeni, zamenjajte vrstico sql z naslednjim:
SqlQuery = "SELECT * FROM ime poizvedbe;"
Preden zaženete ta program, se morate prepričati, da baza podatkov Dostop do podatkov je bil shranjen, tj. pritisnite Ctrl+S (ni dovolj, da se poizvedba izvaja v Accessu).
0
Uspelo mi je zagnati poizvedbo za posodobitev, ki je bila že shranjena v Accessu, z uporabo:
Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc
To mi je povzročalo napake, dokler nisem presledkov v imenu poizvedbe zamenjal s podčrtaji tako v Accessovi bazi podatkov kot v stavku za izvedbo.
0
Že dolgo je minilo, odkar je bila ta tema ustvarjena. Če vse prav razumem, lahko še kaj uporabnega dodam. Poimenoval sem tisto, kar opisuje OP, to je postopek uporabe SQL iz poizvedbe, shranjene v ACCDB, za izvajanje v VBA prek DAO ali ADOBD. Dal sem mu ime "ponudnik lastnosti objekta", tudi z akronimom OPP v svojih zapiskih in predpono/pripono za ime objekta.
Ideja je, da obstoječi objekt v ACCDB (običajno poizvedba) izpostavi lastnost (običajno SQL), ki jo je treba uporabiti v VBA. Sestavil sem funkcijo samo zato, da posrkam SQL iz poizvedb za to; glej spodaj. Opozorilo: Oprostite, toda vse je v DAO, ADODB ne uporabljam veliko. Upam, da vam bodo te ideje še vedno koristile.
Šel sem celo tako daleč, da sem razvil metodo za uporabo/vstavljanje zamenljivih parametrov v SQL, ki izhaja iz teh poizvedb OPP. Nato uporabim VBA.Replace(), da opravim zamenjavo, preden uporabim SQL v VBA.
Pot objekta DAO do poizvedbe SQL v ACCDB je naslednja:
MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL
Zamenljive parametre uporabljam tako, da ocenim, kaj je treba zamenjati, in izberem nenavadno ime za parameter, ki morda ne obstaja v pravi bazi podatkov. Večinoma so bile edine zamenjave imen polj ali tabel ali stavkov WHERE in HAVING. Zato jih imenujem kot "(ReplaceMe00000001)" in nato uporabim funkcijo Replace(), da opravim delo ...
SqlText = VBA.Replace(sqlText, "(ReplaceMe00000001)", "SomeActualParameter") ...
in nato uporabite sqlText v VBA. Tukaj je delujoč primer:
Javna funkcija MySqlThing() Dim sqlText as String Dim myParamater as String Dim myExpression as String "Nastavite vse. sqlText = getSqlTextFromQuery("myQuery") myParameter = "(ReplaceMe00000001)" myExpression = "SomeDateOrSomething12/31/2017" "Naredi zamenjava . sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Nato uporabite SQL. db.Execute sqlText, dbFailOnError End Function Function getSqlTextFromQuery(ByVal oppName As String) As String Dim app As Access.Application Dim db As DAO.Database Dim qdefs As DAO.QueryDefs 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
Zaženite poizvedbo v programu Access MakeTable iz Excela
Imam datoteko Excel, ki jo moram avtomatizirati. Ko uporabnik odpre Excelovo poročilo, bo pozvan k osvežitvi podatkov. Če rečejo da, potem moram zagnati poizvedbo ...
Izvedite funkcijo VBA prek poizvedbe pogleda v MS Access 2013 iz JS ActiveX ADO
Kako izvesti makro VBA prek poizvedbe pogleda v MS Access 2013 iz JS ActiveX ADO? funkcija VBA namenjen prejemu Trenutni uporabnik, prijavljen z: Public Declare...
Izvedite shranjeno poizvedbo, ki vsebuje "funkcijo" v bazi podatkov dostopa iz excela
Poskušam zagnati poizvedbo, shranjeno v zbirki podatkov Access iz excel vba. Poizvedba deluje dobro, če jo odprem in zaženem v zbirki podatkov access, vendar vrže napako, ko jo zaženem iz modula ...
MS Access - Izvedite shranjeno poizvedbo po imenu v VBA
Kako izvesti shranjeno poizvedbo v MS Access 2007 v VBA? Ne želim kopirati in prilepiti SQL v VBA. Raje bi samo izvršil ime zahteve. To ne bo delovalo... VBA ne najde poizvedbe....
Kako izvesti zahtevo v ms-access v kodi VBA?
Kako lahko zaženem poizvedbo za vrnitev zapisov v bazi podatkov podatki za ms-dostop z uporabo kode VBA?
Zaženite zahtevo za dostop iz excela in ji posredujte parametre
Kako zagnati poizvedbo v MS access db iz Excelove kode VBA ali makra. Zahteva MS-Access sprejme nekatere parametre, ki jih je treba posredovati iz Excela. Hvala vam
Upravljanje Excelovega delovnega zvezka iz programa Access 2010 VBA
Imam situacijo, ki je zelo podobna naslednji objavi: Zahtevam dostop do excela 2010 za ustvarjanje grafa prek vba. V mojem primeru izvažam tabelo, vendar želim narediti veliko več z datoteko ...
Izvedite prehodno poizvedbo SQL Server iz Access VBA
V Accessu 2007 imam shranjeno prehodno poizvedbo UPDATE. Ko dvakrat kliknem na prehodno poizvedbo, se uspešno izvede. Kako lahko dosežem, da se ta poizvedba izvede iz VBA? Jaz bi...
Uvoz velikega nabora podatkov v Access iz Excela prek VBA
Imam ogromen nabor podatkov, ki jih moram uvoziti iz Excela v Access (~800.000 vrstic). Vendar pa lahko prezrem vrstice z določeno vrednostjo stolpca, ki so približno 90 % ...
Kakšna poizvedba MDX v Excelu vba?
ali obstaja način za izvedbo poizvedbe MDX v Excelu VBA? Mislil sem, da bi to lahko naredili prek ADO, tako kot pri SQL (da, vem, da se SQL razlikuje od MDX - težava, ki velikokrat ...
Access je shranil poizvedbo, ki je bila razvita z graditeljem poizvedb myQuery. Baza podatkov je s sistemom povezana preko ODBC povezave. Vključeni so vsi makri.
Excel Has vzpostavi povezavo ADODB za povezavo z bazo podatkov
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "MyDatabase.accdb" End With
Običajno preprosto napišete svoj SQL, ki deluje odlično, nato pa naredite nekaj podobnega
Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...
Vendar želim dostopati do poizvedbe, ki sem jo shranil v podatkovni zbirki za dostop. Torej, kako naj pokličem shranjeno poizvedbo v bazi podatkov, ki sem jo pravkar povezal.
Sem že poskusil
- con.Execute("EXEC myQuery"), vendar mi je to povedalo, da myQuery ni bilo mogoče najti.
- rs.Odpri "myQuery", vendar je neveljaven in zahteva izjave SELECT/etc iz njega
5 odgovorov
Mislim, da si lahko to predstavljate kot shranjeno proceduro.
Če začnemo tik pred Dim sqlQuery As String
Zatemni cmd kot nov ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()
Nato nadaljujte s svojim delom z naborom zapisov.
Skoraj ste bili tam
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" End With con.Execute "Moja poizvedba"
Samo zapusti Exec.
Dodate lahko tudi parametre, to je nekoliko zastarelo, vendar bi moralo pomagati: Posodobite 2 polji v Accessovi zbirki podatkov z Excelovimi podatki in morda z makrom
Uspelo mi je zagnati poizvedbo za posodobitev, ki je bila že shranjena v Accessu, z uporabo:
Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc
To mi je povzročalo napake, dokler nisem presledkov v imenu poizvedbe zamenjal s podčrtaji tako v Accessovi bazi podatkov kot v stavku za izvedbo.
To je neke vrste hekersko delo, vendar lahko zahtevate zahtevo. To pomeni, zamenjajte vrstico SQL z naslednjim:
SqlQuery = "SELECT * FROM ime poizvedbe;"
Preden začnete, se morate prepričati, da je Accessova zbirka podatkov shranjena, tj. pritisnite Ctrl + S (ni dovolj, da se poizvedba izvede v Accessu).
Že dolgo je minilo od nastanka te teme. Če prav razumem, bi morda lahko kaj uporabnega dodal. Poimenoval sem tisto, kar opisuje OP: to je postopek uporabe SQL iz poizvedbe, shranjene v ACCDB, za izvajanje v VBA prek DAO ali ADOBD. Poimenoval sem ga "ponudnik lastnosti objekta", tudi z akronimom OPP v mojih zapiskih in za predpono/pripono imena objekta.
Ideja je, da obstoječi objekt v ACCDB (običajno poizvedba) izpostavi lastnost (običajno SQL), ki jo je treba uporabiti v VBA. Sestavil sem funkcijo samo zato, da iz poizvedb za to posrkam SQL; Glej spodaj. Opozorilo: Oprostite, toda vse je v DAO, ADODB ne uporabljam veliko. Upam, da vam bodo ideje še vedno koristile.
Šel sem celo tako daleč, da sem razvil metodo za uporabo/vstavljanje zamenljivih parametrov v SQL, ki izhaja iz teh poizvedb OPP. Nato uporabim VBA.Replace(), da opravim zamenjavo, preden uporabim SQL v VBA.
Pot objekta DAO do poizvedbe SQL v ACCDB je naslednja:
MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL
Zamenljive parametre uporabljam tako, da ocenim, kaj je treba zamenjati, in izberem nenavadno ime za parameter, ki morda ne obstaja v dejanski bazi podatkov. Večinoma so edine zamenjave, ki sem jih naredil, imena polj ali tabel ali izrazi stavkov WHERE in HAVING. Zato jih imenujem kot "(ReplaceMe00000001)" in nato uporabim funkcijo Replace(), da opravim delo ...
SqlText = VBA.Replace(sqlText, "(ReplaceMe00000001)", "SomeActualParameter")
Nato uporabite sqlText v VBA. Tukaj je delujoč primer:
Javna funkcija MySqlThing() Dim sqlText as String Dim myParamater as String Dim myExpression as String "Nastavite vse. sqlText = getSqlTextFromQuery("myQuery") myParameter = "(ReplaceMe00000001)" myExpression = "SomeDateOrSomething12/31/2017" "Naredi zamenjava . sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Nato uporabite SQL. db.Execute sqlText, dbFailOnError End Function Function getSqlTextFromQuery(ByVal oppName As String) As String Dim app As Access.Application Dim db As DAO.Database Dim qdefs As DAO.QueryDefs 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
Ta lekcija je posvečena SQL poizvedbe v bazo podatkov na VBA dostop. Ogledali si bomo, kako se izvedejo poizvedbe INSERT, UPDATE, DELETE v bazi podatkov v VBA, naučili pa se bomo tudi, kako pridobiti določeno vrednost iz poizvedbe SELECT.
Tisti, ki programirajo v VBA dostop in med delom s podlago Podatki SQL strežniki se zelo pogosto srečujejo s tako preprosto in potrebno nalogo, kot je pošiljanje SQL poizvedbe v podatkovno bazo, pa naj bo to INSERT, UPDATE ali preprosta SQL SELECT poizvedba. In ker smo programerji začetniki, bi morali znati tudi to, zato bomo danes storili prav to.
Teme pridobivanja podatkov iz smo se že dotaknili SQL strežnik, kjer so napisali kodo v VBA za pridobivanje teh podatkov, na primer v članku o Nalaganju podatkov v besedilno datoteko iz MSSql 2008 ali pa so se tega malo dotaknili tudi v gradivu Nalaganje podatkov iz Accessa v Wordovo in Excelovo predlogo, vendar Tako ali drugače smo ga pogledali površno, danes pa predlagam, da o tem spregovorimo malo podrobneje.
Opomba! Vsi spodnji primeri so obravnavani z uporabo projekta Access 2003 ADP in zbirke podatkov MSSql 2008. Če ne veste, kaj je projekt ADP, smo si to ogledali v gradivu Kako ustvariti in konfigurirati projekt Access ADP
Izvorni podatki za primere
Recimo, da imamo tabelo test_table, ki bo vsebovala številke in imena mesecev v letu (poizvedbe se izvajajo z Management Studio)
USTVARI TABELO .( NI NULL, (50) NULL) NA POT
Kot sem že rekel, bomo uporabili projekt ADP, konfiguriran za delo z MS SQL 2008, v katerem sem ustvaril testni obrazec in dodal gumb za zagon s podpisom "teči", ki ga bomo potrebovali za testiranje naše kode, tj. Vso kodo bomo zapisali v obdelovalniku dogodkov " Pritisk na gumb».
Poizvedbe v zbirko podatkov INSERT, UPDATE, DELETE v VBA
Da ne bomo predolgo odlašali, začnimo takoj, recimo, da moramo v našo testno tabelo dodati vrstico ( koda komentirala)/
Private Sub start_Click() "Razglasite spremenljivko za shranjevanje poizvedbenega niza Dim sql_query As String "Vanjo zapišite poizvedbo, ki jo potrebujemo sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Izvedi it DoCmd RunSQL sql_query End Sub
V tem primeru se poizvedba izvede z uporabo trenutnih parametrov povezave z bazo podatkov. Lahko preverimo, ali so bili podatki dodani ali ne.
Kot lahko vidite, so bili podatki vstavljeni.
Če želite izbrisati eno vrstico, napišemo naslednjo kodo.
Private Sub start_Click() "Razglasi spremenljivko za shranjevanje poizvedbenega niza Dim sql_query As String "Vanjo zapiši poizvedbo za brisanje sql_query = "DELETE test_table WHERE id = 6" "Zaženi DoCmd.RunSQL sql_query End Sub
Če preverimo, bomo to videli zahtevana vrstica levo.
Če želite posodobiti podatke, napišite zahtevo za posodobitev v spremenljivko sql_query, upam, da je pomen jasen.
SELECT poizvedba za bazo podatkov v VBA
Tukaj so stvari nekoliko bolj zanimive kot pri drugih konstruktih SQL.
Najprej, recimo, da moramo pridobiti vse podatke iz tabele in jih bomo na primer obdelali in prikazali v sporočilu, vi pa jih seveda lahko uporabite za druge namene, za to napišemo naslednje Koda
Private Sub start_Click() "Razglasi spremenljivke "Za nabor zapisov iz baze podatkov Dim RS As ADODB.Recordset "Poizvedbeni niz Dim sql_query As String "Niz za prikaz povzetkov podatkov v sporočilu Dim str As String "Ustvari nov objekt za zapise set RS = New ADODB .Recordset "Vrstica poizvedbe sql_query = "SELECT id, name_mon FROM test_table" "Zaženi poizvedbo z uporabo trenutnih nastavitev povezave projekta RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Prelistajte zapise Medtem ko ne ( RS.EOF) "Izpolnite spremenljivko za prikaz sporočila str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "pojdi na naslednji zapis RS.MoveNext Wend " Izpiši sporočilo msgbox str End Sub
Tukaj že uporabljamo zanke VBA Access za ponavljanje vseh vrednosti v našem nizu zapisov.
Toda precej pogosto je treba pridobiti ne vse vrednosti iz nabora zapisov, ampak samo eno, na primer ime meseca po njegovi kodi. Da bi to naredili, je uporaba zanke precej draga, zato lahko preprosto napišemo poizvedbo, ki bo vrnila samo eno vrednost, in dostopamo do nje, na primer, dobili bomo ime meseca s kodo 5
Private Sub start_Click() "Razglasi spremenljivke" Za nabor zapisov iz baze podatkov Dim RS As ADODB.Recordset "Poizvedbeni niz Dim sql_query As String "Niz za prikaz končne vrednosti Dim str As String "Ustvari nov objekt za nabor zapisov RS = New ADODB.Recordset "Vrstica poizvedbe sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Zaženi poizvedbo z uporabo trenutnih nastavitev povezave projekta RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Pridobi našo vrednost str = RS. Polja(0) msgbox str End Sub
Zaradi univerzalnosti smo tukaj že obravnavali ne ime celice, temveč njen indeks, tj. 0 in to je prva vrednost v Recordset, na koncu smo dobili vrednost "maj".
Kot lahko vidite, je vse precej preprosto. Če morate pogosto pridobiti določeno vrednost iz baze podatkov ( kot v zadnjem primeru), potem priporočam, da vso kodo izpišete v ločeno funkcijo (Kako napisati funkcijo v VBA Access 2003) z enim vhodnim parametrom, na primer kodo meseca ( če upoštevamo naš primer) in preprosto, kjer je potrebno prikazati to vrednost, pokličemo funkcijo, ki jo potrebujemo z zahtevanim parametrom in to je to, s tem bomo bistveno zmanjšali kodo VBA in izboljšali percepcijo našega programa.
To je vse za danes. Vso srečo!
Access vam omogoča programsko ustvarjanje nizov poizvedb SQL, pa tudi nizov, ki služijo kot vrednosti za lastnosti obrazcev, poročil itd., funkcijskih parametrov itd., ki morajo prav tako ustrezati Sintaksa SQL. Hkrati se moramo zavedati, da morajo biti tudi konstante, vključene v takšno vrstico, oblikovane v skladu s pravili SQL, in sicer:
Če je to kršeno, so možni naslednji učinki:
Spodaj v vsakem razdelku je primer niza SQL, ki naj bi bil izdelan, ko ustvarjanje programske opreme in nato kodo VBA, ki jo ustvari. Spodaj je zelo koristno priporočilo.
1. Uporaba številk
IZBERITE * IZ tabele WHERE (((Tabela .Količina)= 12
.197
));
VBA v1
Dim q kot enojni q = 12
.197
strSQL = "SELECT * " _ & "FROM Table " _ & "WHERE (((Table.Quanty)=" & q & "));"
VBA v2
Dim q As String q = "12,197" strSQL = "SELECT * " _ & "FROM Table " _ & "WHERE (((Table.Quanty)=" & Str (q) & "));"
Opomba:
Funkcija GetDecimalSeparator() Kot niz GetDecimalSeparator = Format ( 0 #, "." ) Končna funkcija Upoštevajte tudi, da nekatera standardna Accessova dejanja v tem primeru morda ne bodo delovala.
2. Uporaba nizov
SELECT * FROM Table WHERE (((Table .Name)="All" ));
VBA v1
Dim q Kot niz q = "Vse" strSQL = "SELECT * " _ & "FROM Table " _ & "WHERE (((Table.Quanty)=" "" & DoubleQuote(q) & "" "));"
VBA v2
Dim q Kot niz q = "Vse" strSQL = "SELECT * " _ & "FROM Table" _ & "WHERE (((Table.Quanty)="" & DoubleApostrophe(q) & "" ));"
Opomba:
primer:
pogoj vzorčenja:
a"a"s SQL:
polje WHERE=" a""a"s"VBA:
strWhere = " polje WHERE=""" & "a""""a"s" & """
"
primer:
pogoj vzorčenja:
a"a"s SQL:
polje WHERE=" a"a""s"VBA:
strWhere = " polje WHERE="" & "a""a""s" & ""
"
Javna funkcija Replace97(StrMain As String, StrFind As String, StrZam As String) As String On Error GoTo err Dim pos As Long If StrFind = "" Then GoTo err If StrMain = "" Then Replace97 = StrZam: Exit Function Do Until InStr( 1
, StrMain, StrFind) = 0
pos = InStr( 1
, StrMain, StrFind) StrMain = sredina (StrMain, 1
,pos- 1
) & StrZam & mid (StrMain, pos + Len(StrFind), Len(StrMain)) Loop Replace97 = StrMain Exit Function err: Replace97 = StrMain End Function
3. Uporaba datljev
IZBERI * IZ tabele WHERE (((Tabela .TimeOpen)=# 3
/31
/2003
11
:17
:19
#));
VBA
Dim q As Date q = Now strSQL = "SELECT * " _ & "FROM Table" _ & "WHERE (((Table.TimeOpen)=#" & Format (q, "mm\/dd\/yy hh\:mm \:ss") & "#));"
Opomba:
Ko sem sestavil tak niz, ki vsebuje SQL ukaz, in pošiljanje v izvedbo lahko povzroči napako. V tem primeru natisnite ta niz v okno za odpravljanje napak in ga poglejte z očmi. Morda vam bo napaka takoj padla v oči. Če ne deluje, ustvarite novo poizvedbo v oblikovalniku poizvedb, preklopite v način SQL, vstavite besedilo poizvedbe in jo zaženite. Če pride do napake, bo bolj jasno prikazana.