ADODB Connection über VBA

[JayCole]

Guten Abend in die Runde,
bin ganz neu hier und sehr gespannt auf den Austausch.
An sich bin ich recht erfahren mit VBA sowie Access-Datenbanken, die ich in den letzten Jahren immer über DAO angesprochen habe.
Nun habe ich mich weiterentwickelt, steige in SQL Server ein. Eine Datenbank habe ich soweit erstellt und versuche diese über VBA (in dem Fall von Excel aus) mit ADODB anzusprechen.
Es handelt sich um einen SQL Server 2019, die DB läuft mit Windows-Authentifizierung, in Excel habe ich die Bibliothek "Microsoft ActiveX Data Objects 6.1 Library" geladen.
Mein Code sieht wie folgt aus:

Public p_cnnADO As ADODB.Connection
Public p_rcsADO As ADODB.Recordset
Sub SQL_connect()
Set p_cnnADO = New ADODB.Connection
With p_cnnADO
.Provider = "SQLOLEDB" 'SQLOLEDB.1 geht auch nicht
.ConnectionString = "X:\Pfad\MeineDB.mdf"
.Properties("Integrated Security").Value = "SSPI"
.Open
End With
End Sub

Ich habe gelesen, dass man für den Zugriff mit Windows-Authentifizierung diese Zeile setzen muss:
.Properties("Integrated Security").Value = "SSPI"
hingegen "User ID" und "Password" nicht setzen darf.

Es scheint mehrere unterschiedliche Möglichkeiten zu geben, einzeilig alles über Properties, ... oder so, wie ich es gemacht habe, was mir an sich am besten gefällt.
Oft sehe ich noch weitere Properties Angaben, halte sie nach meinen Recherchen aber eher für überflüssig. Und alle Varianten und mal mit mehr oder weniger Properties, alle führen zu dem gleichen Fehler:
Laufzeitfehler '-2147467259 (80004005)'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server existiert nicht oder Zugriff verweigert.
Abgesehen davon, dass ich hier den Pfad verfälscht habe, bei mir ist er richtig und wirklich existent.
Woran kann die Fehlermeldung noch liegen? Bitte glaubt mir, ich habe wirklich viel gegoogelt, in deutsch- und englischsprachigen Foren, nach der Connection als auch dem Fehler gesucht, nun bin ich ratlos und hoffe hier auf Unterstützung.
Für Eure Zeit bedanke ich mich schon im Voraus
Gruß
Jay

 

[Dukel]

Wenn du auf einen SQL Server zugreifst, dann machst du das nicht mit dem Pfad zur Datenbank Datei sondern auf den Server per IP/DNS Name oder Shared Memory.

 

[JayCole]

Hallo Dukel,
vielen Dank für Deine schnelle Antwort
Ich habe dem Server nun eine statische IP gegeben, damit es da künftig keine Probleme gibt.
Aber dennoch, ist mir noch nicht alles ganz klar. Ich habe einen Ordner, also wenn man so will, einen Teilpfad zum Server im Netzwerk freigegeben, auf den mein Client zugreift. Wie muss denn nun der ConnectionString aussehen? "\\MeineIP\Pfad\MeineDB.mdf"? Hänge da noch etwas in der Luft.
Vielen Dank noch mal!

 

[Dukel]

Der Client hat mit der Datenbank Datei nichts zu tun. Der SQL Server hat zugriff auf die MDF Datei und gibt die Inhalte via Netzwerk heraus.
SQL Server connection strings - ConnectionStrings.com

Using ADO - SQL Server Native Client
Beispiel:

con.ConnectionString = "Provider=SQLNCLI11;" _ 
         & "Server=(local);" _ 
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _ 
         & "DataTypeCompatibility=80;" _ 
         & "MARS Connection=True;"

(local) ist in dem Fall der selbe Rechner, wenn der SQL Server ein anderer ist, dann muss der entsprechende Servername aufgenommen werden.

 

[dabadepdu]

Es wurde schon beantwortet, nur noch mal zur Deutlichkeit:

Wenn man den Zugriff ala Access oder bestimmte Firebird Zugriffsvarianten oder von der Apple Mac Welt (filemaker) kennt und auf eine "richtige" DB wechselt, muss man sich von der Datei, dem Zugriff darauf (via Freigabe) und allem damit zusammenhängenden verabschieden.
Das fällt nicht schwer, weil es eine Menge technische Nachteile bedeutet, im Grunde eine weitere(unnötige) Zwischenschicht.

Der Client Zugriff erfolgt nur noch auf den Server (IP oder DNS) und eine der dort verfügbaren, namentlich bekannten Datenbanken. Zu denen muss dann auch ein auf dem Server oder in der Domäne (bei MS Systemen) definiertes Zugriffsrecht bestehen. Gerade bei Windows muss man auch hier wieder sehr aufpassen, welche Art von berechtigem User/Zugriff man haben möchte oder will. Entweder eine Berechtigung über Domänenrechte oder über SQL Privilegien (Domaine User versus SQL User). Ein SQL User existiert nur im Kontext eines (gestarteten) Datenbanksystems, innerhalb dieses Servers, Datenbank übergreifend, unabhängig von Betriebssystem Usern. Dieses Konzept gilt in mehr oder weniger ähnlicher Form auch für viele andere RDBMS und ist am meisten verbreitet. (Stichwort Kompatibilität, Migration auf verschiedene DB)
Diese Konstellation mit den Angaben zu User, [Passwort], Datenbank muss man minimal für die "Konstruktion" eines Connection Strings parat haben.
Hier gibt es viele Beispiele:
ConnectionStrings.com - Forgot that connection string? Get it here!

 

[JayCole]

Hi Dukel,

vielleicht bin ich gedanklich noch zu sehr in der Access-Welt unterwegs und kriege deswegen die Kurve nicht. Wenn ich ein Frontend auf dem Client starte, dann schickt dieser doch eine Anfrage an die Datenbank. Warum hat er also nichts mit der Datenbank zu tun? Gut, Dein zweiter Satz sagt, dass der Server die Inhalte via Netzwerk heraus gibt, aber einer muss ihm doch sagen, was er rausgeben soll. Auf dem Server liegen idR mehrere Datenbanken, irgendwie muss das Frontend doch die richtige ansprechen. Sorry, wie gesagt, vielleicht ist meine grundsätzliche, gedankliche Herangehensweise noch gänzlich verkehrt.

Nichtsdestotrotz habe ich Deinen Code natürlich ausprobiert. Da der Server nicht lokal ist, habe ich den Servernamen (ohne Klammern) genommen und AdventureWorks auch testweise durch den Datenbanknamen ersetzt. Haut nicht hin, aber möglicherweise liegt es an einer fehlenden Bibliothek für den Provider SQLNCLI11? Habe die "Microsoft ActiveX Data Objects 6.1 Library" aktiviert, bekomme dennoch den Laufzeitfehler 3706, "Der Provider kann nicht gefunden werden. Möglicherweise ist er nicht richtig installiert worden."

Vielen Dank für Deine Geduld mit mir!

 

[JayCole]

Hallo dabadepdu,
hatte Deine Nachricht vorm Abschicken meiner letzten noch gar nicht gesehen, weil ich meine schon vor ner Std geschrieben, aber wegen Ablenkung jetzt erst gesendet hatte ;-)
Auch das hilft mir natürlich sehr, neue Synapsen zu erstellen ;-) Danke dafür!
Ja, ich glaube es liegt noch viel Arbeit vor mir, muss mir einiges an neuem Grundwissen aneignen.

 

[JayCole]

Hey, für den Fall, dass das jetzt zwischen den vielen Zeilen unterging. Könnte mir bitte noch jemand sagen, warum ich wohl einen Laufzeitfehler 3706 habe? "Der Provider kann nicht gefunden werden. Möglicherweise ist er nicht richtig installiert worden."
Fehlt mir eine Bibliothek / ein Verweis für den Provider SQLNCLI11? Habe u. a. die "Microsoft ActiveX Data Objects 6.1 Library" aktiviert.
Dankeschön schon im Voraus!

 

[dabadepdu]

Windows funktioniert mit OLE DB Providern (auch ADO..) ähnlich wie ODBC. Das System reagiert dabei auf bestimmte File Extensions. Bei ODBC ist es glaub ich .DSN für eine dateibasierte ODBC Verbindungsdefinition und bei OLE DB Providern ist es .UDL.
Falls Du über den Link von mir versucht hast, einfach eine dort als Beispiel gegebene Connection zu starten/adaptieren, bleibt noch die Hürde des Treibers.
Aber Du kannst den String einfach interaktiv aufbauen.
Erzeuge eine leere Textdatei, deren Extension du auf .udl änderst. Ein Doppelklick startet dann den Betriebtssystemassistenten dazu. Damit kannst Du das interaktiv zusammenklicken. Das beinhaltet die Auswahl / Anzeige der verfügbaren Treiber. Ich nutze Windows schon lange nicht mehr, daher habe ich keine Ahnung, was da gerade in der MS Welt angesagt ist (welche Treiber Variante). MS macht ja gerne mal alle 2 Jahre alles neu, um dann nach 20 Jahren wieder bei ODBC anzufangen...
Alternativ kannst Du die Datei auch mit einem Editor ändern, anpassen.
Als weiterer Fallstrick steht dann noch 32 versus 64 bit im Raum. Du musst passend zu Deiner Anwendung den entsprechenden Treiber mit passender Bit-ness wählen, genau wie auch bei ODBC mit dem ODBC Manager für 32 oder 64 bit.
Die Doppelklick Methode ist da glaub ich etwas wahllos, weiß ich nicht genau. Voraussetzung ist sowieso, dass Du über die Serverinstallation oder die Clientinstallation überhaupt einen Treiber installiert hast, in der gewünschten Bitness für Dein Clientprogramm.
Man kann die UDL Assistenten auch über Kommandozeile starten, dann eben genau die, die man möchte. Musst Du mal googlen.

Die wichtigste Frage bei MS ist dann, welchen Treiber man nimmt. Da musst Du Dir Roadmaps o.ä. anschauen, damit Du nicht nach 6 Monaten wieder umsteigen musst.

 

[JayCole]

Guten Morgen dabadepdu,

ich hatte auf eine einfachere Antwort gehofft ;-) Aber besten Dank für Deine ausführliche Beschreibung. Dann mach ich mich mal ran, neue Woche, neues Glück.