<\/p>\n
Im Beitrag „RDBMS-Zugriff per VBA: Verbindungen“ haben wir die Grundlage für den Zugriff auf SQL Server-Datenbanken geschaffen. Nun gehen wir einen Schritt weiter: Wir wollen mit den dort beschriebenen Methoden etwa zum Zusammenstellen einer Verbindungszeichenfolge auf die Daten einer SQL Server-Datenbank zugreifen. Dabei lernen Sie eine Reihe interessanter Funktionen kennen, die den Zugriff deutlich vereinfachen und die auch noch überaus performant sind.<\/b><\/p>\n
Der eingangs erwähnte Beitrag RDBMS-Zugriff per VBA: Verbindungen <\/b>(www.access-im-unternehmen.de\/1054<\/b>) hat die Werkzeuge dafür geliefert, dass Sie Verbindungszeichenfolgen aus einer Tabelle zusammenstellen und diese zum Aufbau einer Verbindung nutzen können. Im vorliegenden Beitrag nutzen wir vor allem die mit der Funktion Standardverbindungszeichenfolge <\/b>ermittelte Verbindungszeichenfolge, um auf die Tabellen der SQL Server-Datenbank zuzugreifen. Sie können aber natürlich auch eine manuell als String zusammengestellte Verbindungszeichenfolge nutzen.<\/p>\nBeispieldatenbank<\/h2>\n
Wir verwenden weiterhin die im ersten Teil der Beitragsreihe vorgestellte Datenbank Suedsturm<\/b> auf Basis des LocalDB-Datenbanksystems. Sie können aber natürlich auch eine Datenbank über den SQL Server nutzen (s. Bild 1).<\/p>\n
![\"Die](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_001.png\")
<\/p>\n
Bild 1: Die in diesem Beitrag verwendete Verbindungszeichenfolge im Formular<\/span><\/b><\/p>\n Die Verknüpfung zu den Tabellen der SQL Server-Datenbank erstellen Sie am einfachsten mit dem Formular frmTabellenVerknuepfen<\/b>, das wir im Beitrag SQL Server-Tools <\/b>(www.access-im-unternehmen.de\/1061<\/b>) vorstellen.<\/p>\n Recordsets sind unter Access ein häufig genutztes Mittel für den Zugriff auf die Daten einer Tabelle oder Abfrage. Natürlich werden Sie diese auch füllen wollen, wenn die Daten Ihrer Datenbank längst in einer SQL Server-Datenbank gelandet sind. Daher schauen wir uns nun zunächst an, wie Sie Recordsets auf der Basis verschiedener Zugriffsarten auf die Daten der SQL Server-Datenbank erstellen und füllen können.<\/p>\n Dabei greifen wir auf einfachem Wege auf die Daten zu, nämlich über eine verknüpfte Tabelle, aber auch auf subtilere Weise – etwa über eine Pass-Through-Abfrage, die sich einer gespeicherten Prozedur als Datenquelle bedient.<\/p>\n Die einfachste Möglichkeit, per VBA auf die Daten einer verknüpften Tabelle zuzugreifen, ist das DAO-Recordset. Dieses erhalten Sie mit der OpenRecordset<\/b>-Methode, der Sie als ersten Parameter den Namen der verknüpften Tabelle, als zweiten den Wert dbOpenSnapshot <\/b>(Daten ändern wollen wir nicht per verknüpfter Tabelle, da zu unperformant – ein rein lesender Zugriff sorgt außerdem für wesentlich weniger Sperren auf dem Server) und als dritten Parameter den Wert dbSeeChanges<\/b>, um Fehler in Zusammenhang mit Autowerten in der zugrunde liegenden Tabelle zu vermeiden.<\/p>\n Die folgende Beispielprozedur erstellt ein Recordset auf Basis der Tabelle tblArtikel <\/b>und gibt die Werte der Felder ArtikelID <\/b>und Artikelname <\/b>aller Datensätze dieser Tabelle im Direktfenster aus:<\/p>\n Wenn Sie die Daten aus der SQL Server-Tabelle nicht nur lesen, sondern auch ändern wollen, verwenden Sie db-OpenDynaset <\/b>statt dbOpenSnapshot<\/b>.<\/p>\n Gespeicherte Prozeduren sind prinzipiell Abfragen, die Auswahl- oder Aktionsabfragen enthalten und direkt auf dem SQL Server gespeichert sind und dort ausgeführt werden. Sie liefern nur die benötigten Daten zurück und sind daher meist viel schneller, als wenn Sie etwa mit verknüpften Tabellen arbeiten.<\/p>\n Hinweis: Die ab jetzt vorgestellten Techniken erlauben lediglich den lesenden Zugriff auf die ermittelten Daten.<\/p>\n Wenn Sie ein Recordset auf Basis des Ergebnisses einer gespeicherten Prozedur verwenden möchten, benötigen Sie folgende Dinge:<\/p>\n Im einfachsten Fall handelt es sich um eine gespeicherte Prozedur, die keine Parameter erwartet. Warum ist dies so unkompliziert Dies liegt in der Natur des Aufrufs einer gespeicherten Prozedur über eine Pass-Through-Abfrage begründet.<\/p>\n Die gespeicherte Prozedur liegt auf dem SQL Server und wird über eine Pass-Through-Abfrage mit der EXEC<\/b>-Anweisung aufgerufen:<\/p>\n Kein Problem – die Pass-Through-Abfrage kann wie gesehen verwendet werden. Wenn Sie jedoch einen Parameter übergeben müssen, gehört dieser in den SQL-Text der Pass-Through-Abfrage – also beispielsweise so:<\/p>\n Beispielparameter <\/b>ist aber nicht bei jedem Aufruf gleich, sonst brauchten Sie ja keinen Parameter. Es wird also immer ein anderer Wert übergeben – die ID <\/b>eines zu löschenden Datensatzes, das Vergleichskriterium für eine SELECT<\/b>-Abfrage et cetera.<\/p>\n Das bedeutet, dass Sie den SQL-Text der Abfrage mit jedem Aufruf neu erstellen müssen.<\/p>\n Als weiteres Kriterium kommt hinzu, dass sich die Verbindungszeichenfolge bei der Arbeit mit einer Kombination aus Access-Frontend und SQL Server-Backend ändern kann – sei es, weil sich der Name des Servers, der Name der Datenbank, die Authentifizierungsmethode oder der zu verwendende Treiber ändert. Die Verbindungszeichenfolge wird an vielen Stellen verwendet, vor allem aber als Eigenschaft der Pass-Through-Abfragen, die sich im Laufe der Entwicklung einer Access-Anwendung mit SQL Server-Backend ansammeln werden.<\/p>\n Aber brauchen Sie all diese Pass-Through-Abfragen überhaupt Letztlich müssen die meisten ohnehin jeweils mit einem neuen SQL-Ausdruck gefüllt werden, da diese etwa verschiedene Parameter verwenden. Warum also den Navigationsbereich mit hunderten von Pass-Through-Abfragen füllen, wenn man diese auch jeweils temporär erzeugen kann Die Antwort ist: Irgendwo müssen wir ja den Code für den Zugriff auf die gespeicherten Abfragen speichern. Dazu bietet sich jeweils eine Pass-Through-Abfrage je gespeicherter Abfrage durchaus an.<\/p>\n Wir verwenden aber später dennoch einen Satz von VBA-Funktionen und -Prozeduren, welche die jeweiligen Pass-Through-Abfragen untersuchen, den enthaltenen T-SQL-Code zum Aufruf der gespeicherten Prozedur entnehmen und die Pass-Through-Abfrage auf Basis der zu übergebenden Parameterwerte und der Verbindungszeichenfolge neu erzeugen und das gewünschte Objekt zurückliefern. Denn: Für den Zugriff auf eine gespeicherte Prozedur benötigen wir gar keine spezielle gespeicherte Pass-Through-Abfrage, sondern lediglich eine zur Laufzeit erstellte Abfrage, die wir mit dem Schlüsselwort EXEC<\/b>, dem Namen der gespeicherten Prozedur sowie den gegebenenfalls benötigten Parameterwerten füllen.<\/p>\n In den folgenden Abschnitten sehen wir uns an, wie Sie den Zugriff auf gespeicherte Prozeduren per VBA vereinfachen können. Insgesamt stellen wir dort die folgenden Prozeduren vor, die sich allesamt im Modul mdlToolsSQLServer <\/b>befinden:<\/p>\n Die nächste Variante, per Recordset auf die Daten einer SQL Server-Tabelle zuzugreifen, ist der Zugriff auf eine gespeicherte Prozedur (Stored Procedure<\/b>) über eine Pass-Through-Abfrage.<\/p>\n Eine solche Pass-Through-Abfrage müssen Sie zunächst einmal erstellen. Da wir davon ausgehen, dass Sie neben LocalDB und\/oder SQL Server auch das SQL Server Managment Studio installiert haben, erstellen wir die neue gespeicherte Prozedur einfach von dort aus.<\/p>\n Dazu starten Sie das SQL Server Management Studio, verbinden sich mit der entsprechenden Instanz (in unserem Fall mit (localdb)\\MSSQLLocalDB<\/b>) und wechseln zur Datenbank Suedsturm<\/b>.<\/p>\n Hier öffnen Sie das Datenbank-Element Suedsturm <\/b>und darunter die Elemente Programmierbarkeit <\/b>und Gespeicherte Prozeduren<\/b>. Letzteres Element bietet im Kontextmenü den Eintrag Gespeicherte Prozedur <\/b>an, mit der Sie eine neue Prozedur erstellen können (s. Bild 2).<\/p>\n Bild 2: Anlegen einer gespeicherten Prozedur<\/span><\/b><\/p>\n Sie können dies allerdings auch direkt über eine neue Abfrage erledigen, die Sie über den Kontextmenü-Eintrag Neue Abfrage <\/b>des Suedsturm<\/b>-Elements öffnen. Hier tragen Sie die T-SQL-Anweisung ein, mit der Sie die gespeicherte Abfrage erstellen und die wie folgt aussieht:<\/p>\n Danach führen Sie die Abfrage mit der Taste F5 <\/b>aus (s. Bild 3). Sie finden nun nach einer Aktualisierung einen neuen Eintrag namens pAlleArtikel <\/b>unter dem Element Suedsturm|Pro-gram-mierbarkeit|Gespeicherte Prozeduren<\/b>.<\/p>\n Bild 3: Testen einer gespeicherten Prozedur<\/span><\/b><\/p>\n Sie können diese testen, indem Sie in einem Abfragefenster die Anweisung EXEC pAlleArtikel; <\/b>eingeben und diese mit F5 <\/b>ausführen.<\/p>\n Nun erstellen wir in der Access-Datenbank eine Pass-Through-Abfrage, mit der wir über die gespeicherte Prozedur auf die Daten der Tabelle tblArtikel <\/b>zugreifen wollen.<\/p>\n Legen Sie dazu eine neue, leere Abfrage in der Entwurfsansicht an und schließen Sie den automatisch erscheinenden Dialog Tabelle anzeigen<\/b>. Nun klicken Sie auf den Ribbon-Eintrag Entwurf|Abfragetyp|Pass-Through <\/b>(s. Bild 4).<\/p>\n Bild 4: Umwandeln einer Abfrage in eine Pass-Through-Abfrage<\/span><\/b><\/p>\n Dies ändert vor allem die Ansicht der Abfrage, nämlich in die SQL-Ansicht. Hier sind nun zwei Schritte zu erledigen: Als Erstes geben Sie die Anweisung ein, mit der Sie zuvor schon die gespeicherte Prozedur im SQL Server Management Studio getestet haben, also EXEC dbo.pAlleArtikel<\/b>. Außerdem müssen Sie der Abfrage noch mitteilen, woher sie ihre Daten beziehen soll. Dies erledigen wir per Hand über die Eigenschaft ODBC-Verbindung, der wir in unserem Beispiel die folgende Zeichenkette übergeben:<\/p>\n Schließlich muss noch die Eigenschaft Liefert Datensätze <\/b>auf den Wert Ja <\/b>eingestellt sein (s. Bild 5).<\/p>\n Bild 5: Eigenschaften der Pass-Through-Abfrage<\/span><\/b><\/p>\n Anschließend können Sie die Datenblattansicht dieser Abfrage aktivieren und erhalten das gewünschte Ergebnis (s. Bild 6).<\/p>\n Bild 6: Ergebnis der Pass-Through-Abfrage<\/span><\/b><\/p>\n Nun wollen wir auch per VBA über die Pass-Through-Abfrage auf die von der gespeicherten Prozedur gelieferten Daten zugreifen.<\/p>\n Auf diese Pass-Through-Abfrage greifen Sie wie folgt zu und durchlaufen dann ihre Datensätze über das zuvor gefüllte Recordset<\/b>-Objekt:<\/p>\n Hier geschieht rein oberflächlich nicht viel anderes, als wenn Sie wie weiter oben auf eine verknüpfte Tabelle zugreifen – mit dem Unterschied, dass wir hier den Namen der Pass-Through-Abfrage der OpenRecordset<\/b>-Methode verwenden.<\/p>\n Vorbereitend auf weitere Varianten, in denen wir beispielsweise Parameter verwenden wollen, die in den Entwurf der Abfragen einfließen müssen, schauen wir uns nun eine Alternative an, bei der wir zunächst ein QueryDef<\/b>-Objekt auf Basis der Pass-Through-Abfrage ptSELECTAlleArtikel <\/b>erstellen und erst dann mit der OpenRecordset<\/b>-Methode auf diesem Objekt ein Recordset erstellen:<\/p>\n Wenn Sie die Anwendung auf Ihrem Rechner entwickeln und diese dann beispielsweise an den Kunden weitergeben, werden Sie voraussichtlich eine andere Verbindungszeichenfolge nutzen müssen. <\/p>\n In diesem Fall haben Sie ein Problem: Wir haben ja weiter oben beschrieben, wie Sie einer Pass-Through-Abfrage die Verbindungszeichenfolge mitteilen – und zwar durch direktes Eintragen dieser Zeichenfolge in die Eigenschaft ODBC-Verbindung<\/b>. Wenn Sie nun eine ganze Reihe dieser Pass-Through-Abfragen nutzen, müssten Sie diese vor jeder Weitergabe an andere Anwender ändern. Diese Arbeit wollen Sie nicht wirklich erledigen, also automatisieren wir dies. Dazu erstellen wir eine Prozedur namens PTAbfrageAktualisieren<\/b>, die wie in Listing 1 aussieht.<\/p>\n Listing 1: Prozedur zum Aktualisieren von Pass-Through-Abfragen<\/span><\/b><\/p>\n Die Funktion erwartet den Namen der Pass-Through-Abfrage und die Verbindungszeichenfolge als Parameter. Da diese den weiter oben vorgestellten Techniken zufolge mit der Funktion VerbindungszeichenfolgeNachID <\/b>oder Standardverbindungszeichenfolge <\/b>zusammengestellt wird, enthält sie bei Benutzung der SQL Server-Authentifizierung gegebenenfalls das Kennwort des aktuellen Be-nut-zers.<\/p>\n Daher enthält die Funktion einige Zeilen Code, welche die Verbindungszeichenfolge auf das Auf-treten des Ausdrucks ;PWD= <\/b>untersuchen. Ist dieser vorhanden, wird eine neue Ver-bin-dungs-zeichenfolge namens strVerbindungOhne-PWD <\/b>zusammengestellt, die alle Zeichen bis zum Auf-tre-ten von ;PWD= <\/b>enthält und gegebenenfalls alle Zeichen hinter dem nachfolgenden Semi-ko-lon.<\/p>\n <\/p>\n Dazu ermittelt die Prozedur zunächst mit der InStr<\/b>-Funktion die Position des ersten Auftretens der Zeichenfolge ;PWD<\/b>. Ist diese ungleich 0<\/b>, enthält die Zeichenfolge dieses Element. Eine neue String<\/b>-Variable namens strVerbindungOhnePWD <\/b>nimmt dann zunächst den Inhalt der Verbindungszeichenfolge bis zum Beginn von ;PWD <\/b>auf. Dann ermittelt sie die Position des ersten Semikolons hinter der Zeichenfolge ;PWD<\/b>, also das Ende des entsprechenden Name-Wert-Paares.<\/p>\n Hat das Ergebnis des entsprechenden Aufrufs der InStr<\/b>-Funktion den Wert 0<\/b>, ist die Angabe von PWD <\/b>das letzte Element der Verbindungszeichenfolge und es sind keine weiteren Schritte nötig – die Verbindungszeichenfolge exklusive PWD<\/b>-Element befindet sich nun in der Variablen strVerbindungOhnePWD<\/b>. Anderenfalls gibt es noch mindestens ein weiteres Element hinter dem Name-Wert-Paar mit dem Kennwort – also wird der komplette Rest hinter der ermittelten Position an den bisherigen Inhalt von strVerbindungOhnePWD <\/b>angehängt.<\/p>\n Nachdem das PWD-Element aus der Verbindungszeichenfolge entfernt und in der Variablen strVerbindungOhnePWD <\/b>gespeichert wurde, referenziert die Prozedur PTAbfrageAktualisieren <\/b>die zugrunde liegende Pass-Through-Abfrage und stellt die Verbindungszeichenfolge mit der Connect<\/b>-Eigenschaft auf den neuen Wert ein.<\/p>\n Die Beispieldatenbank enthält beispielsweise in der Tabelle tblVerbindungszeichenfolgen <\/b>zwei Beispielverbindungen mit den ID-Werten 9 <\/b>und 10<\/b>. Wenn Sie die erste davon verwenden wollen, rufen Sie die Prozedur wie folgt auf:<\/p>\n Wollen Sie dann wechseln, nutzen Sie die andere ID:<\/p>\n Sie können natürlich auch direkt die als Standardverbindungszeichenfolge deklarierte Verbindungszeichenfolge als Parameter angeben:<\/p>\n Sie können sich zwischendurch den Entwurf der Pass-Through-Abfrage ansehen – die Eigenschaft ODBC-Verbindung <\/b>enthält jeweils die entsprechende Verbindungszeichenfolge.<\/p>\n Wenn Sie mit dieser Prozedur alle Pass-Through-Abfragen aktualisieren wollen, durchlaufen Sie in einer weiteren Prozedur alle Einträge der Tabelle MSysObjects<\/b>, deren Feld Connect <\/b>einen Wert enthält, der mit ODBC= <\/b>beginnt, und rufen von dort für jeden Eintrag die Prozedur PT-Ab-frageAktualisieren <\/b>auf (s. Listing 2).<\/p>\n Listing 2: Prozedur zum Aktualisieren aller Pass-Through-Abfragen mit der aktuellen Standardverbindungszeichenfolge<\/span><\/b><\/p>\n Es stellt sich die Frage, wann man etwa die Verbindungszeichenfolge von Pass-Through-Abfragen aktualisiert. Die performanteste Lösung wäre wohl, wenn man dies nur dann durchführt, wenn sich die Standardverbindungszeichenfolge der Anwendung ändert. Wenn Sie also etwa die Instanz der Anwendung auf Ihrem Rechner auf die Weitergabe zum Kunden vorbereiten, könnten Sie einfach die Standardverbindungszeichenfolge ändern (etwa mithilfe des Formulars frmVerbindungszeichenfolgen<\/b>, das wir im Beitrag SQL Server-Tools<\/b>, www.access-im-unternehmen.de\/1061<\/b>, vorstellen) und danach die Prozedur AlleAbfragenAktualisieren <\/b>aufrufen.<\/p>\n Gegebenenfalls fügen Sie den Aufruf dieser Prozedur sogar zu der Prozedur hinzu, die im Formular die aktuelle Verbindung als Standardverbindung einstellt.<\/p>\n Sie könnten die jeweilige Pass-Through-Abfrage auch immer dann aktualisieren, wenn Sie überhaupt ein Recordset auf Basis einer solchen Abfrage erstellen. Wenn Sie gespeicherte Prozeduren mit Parametern verwenden, müssen Sie die Abfrage ohnehin aktualisieren – dann können Sie auch gleich die Verbindungszeichenfolge mit hinzu nehmen.<\/p>\n Da Sie gegebenenfalls regelmäßig Recordsets für die verschiedenen Anforderungen benötigen, stellen wir dazu geeignete Funktionen zusammen. Diese können Sie dann beispielsweise einsetzen, wenn Sie Daten per VBA durchlaufen wollen, aber Sie können diese auch der Recordset-Eigenschaft von Formularen, Berichten, Kombinationsfeldern oder Listenfeldern zuweisen. Wir stellen zwei verschiedene Funktionen vor:<\/p>\n Die Funktion SPRecordset <\/b>soll ein Recordset basierend auf der per Funktionsparameter angegebenen gespeicherten Prozedur für eine ebenfalls per Funktionsparameter übergebene Verbindung zurückliefern.<\/p>\n Um das Ergebnis an ein Recordset<\/b>-Objekt zu übergeben, ruft die Prozedur die OpenRecordset<\/b>-Methode für die temporäre Pass-Through-Abfrage mit der gespeicherten Prozedur auf. Diese Variante arbeitet mit gespeicherten Prozeduren, die keine Parameter erwarten (s. Listing 3).<\/p>\n Listing 3: Diese Funktion liefert ein Recordset basierend auf einer gespeicherten Prozedur und einer entsprechenden Verbindungszeichenfolge.<\/span><\/b><\/p>\n Wenn Sie die Funktion ausprobieren und das zurückgelieferte Recordset<\/b>-Objekt weiterverwenden möchten, können Sie dies mit einer Prozedur wie der aus Listing 4 erledigen. Diese deklariert ihrerseits ein Recordset<\/b>-Objekt und weist diesem das Ergebnis der Funktion SPRecordset <\/b>zu. Anschließend durchläuft die Prozedur noch die Datensätze des zurückgelieferten Recordsets.<\/p>\n Listing 4: Test der Funktion zum Holen eines Recordsets auf Basis einer gespeicherten Prozedur<\/span><\/b><\/p>\n Sie können die Funktion auch innerhalb eines Formulars testen. Dazu versehen Sie ein leeres Formular zunächst mit der verknüpften Tabelle als Datenherkunft. Damit können Sie dann die gebundenen Felder ganz einfach aus der Feldliste in das Formular ziehen (s. Bild 7). Das Formular würde nun beim Wechseln in die Formularansicht die Daten der Tabelle tblArtikel <\/b>anzeigen – allerdings aus der verknüpften Tabelle. Wir wollen aber die Daten aus der gespeicherten Prozedur liefern. Dazu fügen wir dem Formular eine Ereignisprozedur hinzu, die durch das Ereignis Beim Laden <\/b>ausgelöst wird:<\/p>\n Bild 7: Ausstatten des Formulars mit gebundenen Feldern<\/span><\/b><\/p>\n Die Prozedur ruft die Funktion SPRecordset <\/b>mit den gewünschten Parametern auf und übergibt das gefüllte Recordset der gleichnamigen Eigenschaft des Formulars (siehe Beispielanwendung, Formular frmAlleArtikel_Recordset<\/b>). Dieses zeigt dann beim öffnen gleich die Daten aus der zugrunde liegenden gespeicherten Prozedur an (s. Bild 8).<\/p>\n Bild 8: Formular mit Daten aus einer gespeicherten Prozedur<\/span><\/b><\/p>\n Die zweite Variante dieser Prozedur erwartet zusätzlich die übergabe eines oder mehrerer Pa-ra-meter in einem ParamArray<\/b>. Dieses soll wiederum die für die gespeicherte Prozedur definierten Parameter abdecken. Bevor wir uns die Funktion ansehen, wollen wir erst einmal eine gespeicherte Prozedur mit Parametern erstellen. Wir wollen damit alle Artikel einer bestimmten Kategorie zurückliefern. Das erledigen wir mit der folgenden T-SQL-Anweisung:<\/p>\n Der Unterschied zur vorherigen gespeicherten Prozedur pAlleArtikel <\/b>liegt erstens in dem in Klammern angegebenen Parameter @KategorieID <\/b>und zweitens in der damit erstellten WHERE<\/b>-Bedingung dbo.tblArtikel.KategorieID = @KategorieID<\/b>. Ein beispielhafter Aufruf im SQL Server Management Studio könnte wie in Bild 9 aussehen – hier werden nur die Artikel der Kategorie mit der ID 1 <\/b>zurückgeliefert.<\/p>\n Bild 9: Testen einer gespeicherten Prozedur mit Parameter<\/span><\/b><\/p>\n Zurück zur Funktion SPRecordsetMitParameter<\/b>, die drei Parameter erwartet (s. Listing 5):<\/p>\n Listing 5: Funktion zum Holen eines Recordsets auf Basis einer gespeicherten Prozedur mit Parametern<\/span><\/b><\/p>\nRecordsets auf Basis von SQL Server-Daten<\/h2>\n
Recordset auf Basis einer verknüpften Tabelle<\/h2>\n
Public Sub <\/span>VerknuepfteTabellePerRecordset()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT * FROM tblArtikel\", _\r\n dbOpenSnapshot, dbSeeChanges)\r\n Do While<\/span> Not<\/span> rst.EOF\r\n Debug.Print<\/span> rst!ArtikelID, rst!Artikelname\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n rst.Close\r\n Set<\/span> rst = Nothing\r\n Set<\/span> db = Nothing\r\nEnd Sub<\/span><\/pre>\nVereinfachungen für den Zugriff auf gespeicherte Prozeduren<\/h2>\n
\n
EXEC cbo.spBeispielprozedur<\/pre>\n
EXEC dbo.spBeispielProzedurMitParameter ''''Beispielparameter''''<\/pre>\n
\n
Recordset aus gespeicherter Prozedur<\/h2>\n
Gespeicherte Prozedur erstellen<\/h2>\n
![\"Anlegen](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_002.png\")
<\/p>\nCREATE PROC dbo.pAlleArtikel\r\nAS\r\nSELECT dbo.tblArtikel.ArtikelID, \r\n dbo.tblArtikel.Artikelname, \r\n dbo.tblArtikel.AufgenommenAm, \r\n dbo.tblArtikel.Auslaufartikel, \r\n dbo.tblArtikel.BestellteEinheiten, \r\n dbo.tblArtikel.Einzelpreis, \r\n dbo.tblArtikel.KategorieID, \r\n dbo.tblArtikel.Lagerbestand, \r\n dbo.tblArtikel.LieferantID, \r\n dbo.tblArtikel.Liefereinheit, \r\n dbo.tblArtikel.Mindestbestand \r\nFROM dbo.tblArtikel;<\/pre>\n
![\"Testen](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_003.png\")
<\/p>\nPass-Through-Abfrage erstellen<\/h2>\n
![\"Umwandeln](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_004.png\")
<\/p>\nODBC;DRIVER={ODBC Driver 11 for SQL Server};SERVER=(localdb)\\MSSQLLocalDB;DATABASE=Suedsturm;Trusted_Connection=Yes<\/pre>\n![\"Eigenschaften](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_006.png\")
<\/p>\n![\"Ergebnis](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_005.png\")
<\/p>\nPer Recordset auf gespeicherte Prozedur zugreifen<\/h2>\n
Public Sub <\/span>GespeicherteProzedurPerRecordset()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"ptSELECTAlleArtikel\")\r\n Do While<\/span> Not<\/span> rst.EOF\r\n Debug.Print<\/span> rst!ArtikelID, rst!Artikelname\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n rst.Close\r\n Set<\/span> rst = Nothing\r\n Set<\/span> db = Nothing\r\nEnd Sub<\/span><\/pre>\nPublic Sub <\/span>GespeicherteProzedurPerQueryDef()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> qdf = db.QueryDefs(\"ptSELECTAlleArtikel\")\r\n Set<\/span> rst = qdf.OpenRecordset()\r\n Do While<\/span> Not<\/span> rst.EOF\r\n Debug.Print<\/span> rst!ArtikelID, rst!Artikelname\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n rst.Close\r\n Set<\/span> rst = Nothing\r\n Set<\/span> qdf = Nothing\r\n Set<\/span> db = Nothing\r\nEnd Sub<\/span><\/pre>\nWas geschieht bei geänderter Verbindungszeichenfolge<\/h2>\n
Public Sub <\/span>PTAbfrageAktualisieren(strPTAbfrage As String<\/span>, strVerbindungszeichenfolge As String<\/span>)\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Dim <\/span>intStart As Integer<\/span>\r\n Dim <\/span>intEnde As Integer<\/span>\r\n Dim <\/span>strVerbindungOhnePWD As String<\/span>\r\n intStart = InStr<\/span>(1, strVerbindungszeichenfolge, \";PWD\")\r\n If <\/span>intStart > 0 Then<\/span>\r\n strVerbindungOhnePWD = Left<\/span>(strVerbindungszeichenfolge, intStart)\r\n intEnde = InStr<\/span>(intStart + 1, strVerbindungszeichenfolge, \";\")\r\n If <\/span>Not<\/span> intEnde = 0 Then<\/span>\r\n strVerbindungOhnePWD = strVerbindungOhnePWD & Mid<\/span>(strVerbindungszeichenfolge, intEnde + 1)\r\n End If<\/span>\r\n Else<\/span>\r\n strVerbindungOhnePWD = strVerbindungszeichenfolge\r\n End If<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> qdf = db.QueryDefs(strPTAbfrage)\r\n qdf.Connect = strVerbindungOhnePWD\r\nEnd Sub<\/span><\/pre>\nPTAbfrageAktualisieren \"PTSELECTAlleArtikel\", VerbindungszeichenfolgeNachID(9)<\/pre>\n
PTAbfrageAktualisieren \"PTSELECTAlleArtikel\", VerbindungszeichenfolgeNachID(10)<\/pre>\n
PTAbfrageAktualisieren \"PTSELECTAlleArtikel\", Standardverbindungszeichenfolge<\/pre>\n
Alle Pass-Through-Abfragen aktualisieren<\/h2>\n
Public Sub <\/span>AlleAbfragenAktualisieren()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Set<\/span> db = CurrentDb\r\n For Each qdf In db.QueryDefs\r\n If <\/span>InStr<\/span>(Nz(qdf.Connect,\"\"), \"ODBC;\") > 0 Then<\/span>\r\n PTAbfrageAktualisieren qdf.Name, Standardverbindungszeichenfolge\r\n End If<\/span>\r\n Next<\/span> qdf\r\nEnd Sub<\/span><\/pre>\nWann aktualisieren<\/h2>\n
Recordset-Funktionen<\/h2>\n
\n
Recordset aus gespeicherter Prozedur ohne Parameter<\/h2>\n
Public Function <\/span>SPRecordset(strStoredProcedure As String<\/span>, strVerbindungszeichenfolge As String<\/span>) As <\/span>DAO.Recordset\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> qdf = db.CreateQueryDef(\"\")\r\n With<\/span> qdf\r\n .Connect = strVerbindungszeichenfolge\r\n .SQL = \"EXEC \" & strStoredProcedure\r\n Set<\/span> SPRecordset = .OpenRecordset\r\n End With<\/span>\r\n Set<\/span> db = Nothing\r\nEnd Function<\/span><\/pre>\nPublic Sub <\/span>TestSPRecordset()\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> rst = SPRecordset(\"dbo.spSELECTAlleWarengruppen\", Standardverbindungszeichenfolge)\r\n Do While<\/span> Not<\/span> rst.EOF\r\n Debug.Print<\/span> rst!WarengruppeID\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\nEnd Sub<\/span><\/pre>\n![\"Ausstatten](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_007.png\")
<\/p>\nPrivate Sub <\/span>Form_Load()\r\n Set<\/span> Me.Recordset = _\r\n SPRecordset(\"pAlleArtikel\", _\r\n Standardverbindungszeichenfolge)\r\nEnd Sub<\/span><\/pre>\n![\"Formular](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_008.png\")
<\/p>\nRecordset aus gespeicherter Prozedur mit Parameter<\/h2>\n
CREATE PROC dbo.pArtikelNachKategorieID (@KategorieID int) AS\r\nSELECT dbo.tblArtikel.ArtikelID, \r\n dbo.tblArtikel.Artikelname, \r\n dbo.tblArtikel.AufgenommenAm, \r\n dbo.tblArtikel.Auslaufartikel, \r\n dbo.tblArtikel.BestellteEinheiten, \r\n dbo.tblArtikel.Einzelpreis, \r\n dbo.tblArtikel.KategorieID, \r\n dbo.tblArtikel.Lagerbestand, \r\n dbo.tblArtikel.LieferantID, \r\n dbo.tblArtikel.Liefereinheit, \r\n dbo.tblArtikel.Mindestbestand \r\nFROM dbo.tblArtikel\r\nWHERE dbo.tblArtikel.KategorieID = @KategorieID;<\/pre>\n
![\"Testen](\"..\/fileadmin\/_temp_\/2016_06\/pic_1062_009.png\")
<\/p>\nPublic Function <\/span>SPRecordsetMitParameter(strStoredProcedure As String<\/span>, strVerbindungszeichenfolge As String<\/span>, _\r\n ParamArray varParameter() As Variant<\/span>) As <\/span>DAO.Recordset\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Dim <\/span>strParameter As String<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> qdf = db.CreateQueryDef(\"\")\r\n strParameter = Parameterliste(varParameter)\r\n With<\/span> qdf\r\n .Connect = strVerbindungszeichenfolge\r\n .SQL = \"EXEC \" & strStoredProcedure & \" \" & strParameter\r\n Set<\/span> SPRecordsetMitParameter = .OpenRecordset\r\n On Error GoTo<\/span> 0\r\n End With<\/span>\r\n Set<\/span> db = Nothing\r\nEnd Function<\/span><\/pre>\n\n