Frontend mit MS-SQL-Server-Backend verbinden

Moderator: ModerationP

Re: Frontend mit MS-SQL-Server-Backend verbinden

Beitragvon indscho1 » 07. Mai 2021, 14:37

Hallo Christian,
ich habe jetzt den aktuellsten Treiber eingebaut ({ODBC Driver 17 for SQL Server}) und natürlich funktioniert das besser im Betrieb. Aber es ist immer noch unerträglich langsam beim Verbindungsaufbau - für leicht zu frustrierende User nicht zumutbar. Ähnliches ging aus einem Post http://www.office-loesung.de/ftopic506006_0_0_asc.php vor vielen Jahren ja auch hervor.

Ich selber habe auf meinem Rechner für die Verbindung einen DSN-Eintrag (ODBC-Data-Source 32 bit) und die Geschwindigkeit darüber ist beeindruckend nach all den Verbesserungen der letzten 14 Tage. Jetzt möchte ich den Zugriff so gestalten, dass jeder User euch so eine DSN bekommt. Meine Idee ist wie folgt:

1. Beim Programmstart nachschauen, ob die DSN existiert.
2. Wenn nicht, programmtechnisch die DSN erzeugen
3. Nach der Überprüfung bzw. Installation der DSN dieselbe DSN auswählen/verwenden
4. Falls sich etwas verändert (Server etc.) wird eine neue DSN erstell und alles geht wieder bei 1. los

Als ersten Schritt möchte ich jetzt bei Test-Usern ausprobieren, ob sie auf diese Weise auch meine Performance hinbekommen. Dazu möchte zunächst den Punkt 3 programmiertechnisch umsetzen. Ich finde aber nichts darüber, wie das geht.

Hast du vielleicht etwas zur Hand?

Vielen Dank mal wieder im Voraus für deine Bemühungen.

Gruß Ingo
indscho1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 107
Registriert: 16. Nov 2016, 11:14

Re: Frontend mit MS-SQL-Server-Backend verbinden

Beitragvon indscho1 » 07. Mai 2021, 15:13

Ich glaube ich habe gestern eine Überdosis Astrazeneca bekommen ...
Natürlich hatte ich den Code schon mal erstellt, als ich meine DSN erstellt und als Verbindung zum SQL-Server definiert habe.

Code: Alles auswählen
'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
   
    Dim stConnect As String
   
    On Error GoTo CreateDSNConnection_Err

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "UID=" & stUsername & vbCr & "Password=" & stPassword & vbCr
    End If

    DBEngine.RegisterDatabase "myDSN", "ODBC Driver 17 for SQL Server", True, stConnect

'// Add error checking.
    CreateDSNConnection = True
    Exit Function

CreateDSNConnection_Err:

CreateDSNConnection = False
    MsgBox messLang("CreateDSNConnection erzeugt unerwarteten Fehler: ", "CreateDSNConnection encountered an unexpected error: ") & Err.Description

End Function

In diesem Sinne
Gruß Ingo
indscho1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 107
Registriert: 16. Nov 2016, 11:14

Re: Frontend mit MS-SQL-Server-Backend verbinden

Beitragvon Bitsqueezer » 07. Mai 2021, 18:12

Hallo Ingo,

seufz...ich hatte doch extra geschrieben, NICHT die Methode 2 mit RegisterDatabase und externen DSN...

DSNlose Verbindung heißt das Zauberwort. Es ist absolut unnötig, die Verlinkungen bei JEDEM Programmstart zu überprüfen und neu zu setzen. Wenn sich der Server ändert - woher soll das Frontend das mitbekommen? Es kann dann ja nicht mehr auf den bisherigen zugreifen. Also brauchst Du in jedem Fall dann eine neue Version des Frontends und das sollte die richtigen Links bereits enthalten.

Der Link beschreibt eine andere Situation, die Userin hatte mit hoher Wahrscheinlichkeit noch keine Tabellenindizierung vorgenommen.

Der ODBC-Treiber ist gut, denn im Gegensatz zu den früheren können die neueren Treiber Daten "verdichten", bevor sie über's Netz gehen und verringern somit die Datenmenge enorm.

Wenn Du genau wissen willst, was im Hintergrund so alles passiert, dann laß den SQL Server Profiler laufen, der ist das Wireshark des SQL Servers. Damit bekommst Du ALLE Operationen zwischen jedem Frontend und allen Datenbanken des Backends zu sehen, mit ein paar Filtereinstellungen dann genau den Verkehr zwischen Deinem Access und Deiner Backenddatenbank.

Und beim Thema Performance gilt halt immer, man muß selbst für Optimierung sorgen, SQL Server macht hier nichts von alleine. Wer Tabellen verlinkt und in Access verknüpft, ist selbst schuld. Stattdessen eine View auf dem Server, fertig.
Eine Verlinkung ist auch nicht immer notwendig, es geht auch einfach per Code oder PT-Query (die allerdings immer Read Only sind). Statt Dynaset kann man auch einfach mal über Snapshot nachdenken, wenn man etwa nur eine Liste anzeigen, aber nicht bearbeiten will.

Auch die geschickte Formulierung von Views kann wieder Performance sparen. Hier sollte man vor allem das Thema CTE kennen. Auch kann man mit einer Function, die Parameter entgegennehmen kann und damit Daten schon vorfiltern, viel Zeit und Datenmenge sparen.

Aber wie gesagt, die höchste Priorität hat: Ordentliche Indizes erstellen. Der Execution Plan gibt schon kleine Hinweise, wenn er ein Problem feststellt. Und wenn da haufenweise Table Scans angezeigt werden (und es nicht sehr kleine Tabellen sind), ist schon was nicht in Ordnung. Mit rechter Maustaste auf dem Datenbanknamen in SSMS kann man sich auch mal die Reports zum Thema Index anzeigen, damit kann man auch sehen, ob die erstellten Indizes tatsächlich verwendet werden und wie oft.

Wenn es um Verbindungsaufbau geht: Hier hat Access bei langsamen Verbindungen das Nachsehen, weil gerade bei verlinkten Objekten jedes einzelne seine eigene Verbindung verwendet. Wenn die Access-Clients weit weg vom Server sind, dauert es eben länger, dagegen kannst Du so nichts machen. Du könntest höchstens ein lustiges Katzenvideo einblenden...:D
Drum sagte ich ja, für großräumig verteilte Clients führt langfristig kein Weg an einem Terminalserver vorbei, und schon ist das Problem gelöst.

Die Performance im Frontend kann ebenfalls gesteigert werden, wenn man nicht sofort alle Datensätze lädt, sondern nur den Teil, den man braucht und in einem Suchformular u.U. auch erst mal gar nichts, bis mind. das erste Filterkriterium eingegeben wurde. Google lädt auch nicht erst das ganze Internet und fragt dann, was Du sehen willst.

Gruß

Christian
Bitsqueezer
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8276
Registriert: 21. Jun 2007, 12:17

Re: Frontend mit MS-SQL-Server-Backend verbinden

Beitragvon indscho1 » 10. Mai 2021, 10:26

Hallo Christian,
ich stehe nach wie vor auf dem Schlauch und die Lösung ist wahrscheinlich trivial. Ich möchte doch eigentlich nur meine User beim Programmstart beim SQL-Server anmelden. Und ich weiß auch, dass die Links zum Server bereits alle vorliegen, demnach brauche ich doch eigentlich auch Methode 1 nicht, die ja eine Tabellendefinition hinzufügt. Aber ich kriege ohne Methode 1 meine User nicht beim SQL-Server angemeldet.
Ich habe versucht mit folgende Code Zugang zu bekommen, aber leider erfolglos (weil fehlerhaft):
Code: Alles auswählen
Function connectODBC() As String
   
    Dim db As DAO.Database
    Dim con As DAO.Connection
    Dim strConnection As String
 
    Set db = CurrentDb
    strConnection = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerName;DATABASE=DatabaseName;UID=USerName;PWD=Password"

    Set con = db.Connection
    con.Database.connect strConnection
   
    Set con = Nothing
    Set db = Nothing
   
End Function


Wo ist mein Denkfehler?

Gruß Ingo
indscho1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 107
Registriert: 16. Nov 2016, 11:14

Re: Frontend mit MS-SQL-Server-Backend verbinden

Beitragvon indscho1 » 10. Mai 2021, 13:59

Ich habe auch schon sowas ausprobiert:
Code: Alles auswählen
Function connectODBC() As String
   
    Dim db As DAO.Database
    Dim strConnection As String
 
    strConnection = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=myServerName;DATABASE=myDataBaseName;Trusted_Connection=Yes"
    Set db = OpenDatabase("", False, False, strConnection)
   
End Function

Bei mir läuft das problemlos durch, bei meinen Usern kommt der Run-time error 3146.
indscho1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 107
Registriert: 16. Nov 2016, 11:14

Re: Frontend mit MS-SQL-Server-Backend verbinden

Beitragvon Bitsqueezer » 10. Mai 2021, 16:13

Hallo Ingo,

beide Methoden sind mir suspekt...was soll das bringen, die Verbindung zu öffnen und gleich wieder zu schließen?

Um den Treiber verwenden zu können, muß dieser natürlich auch bei jedem einzelnen User installiert werden, der ist nicht in Windows integriert.

Außerdem muß das Netzwerkprotokoll freigegeben und eingerichtet sein, also TCP/IP üblicherweise. Im SQL Server Configuration Manager auf dem Server.

Der entsprechende Port muß natürlich ebenso freigegeben sein, wenn da eine oder mehrere Firewalls zwischenhängen.

Minimum wäre mal, einen Ping vom User aus zu probieren.

Du kannst ein Frontend auch komplett ohne Verknüpfung erstellen, wenn Du in jedem Formular die Verbindung selbst aufbaust, ein Recordset erstellst und es dann dem Formular-Recordset zuweist, was aber mehr Aufwand ist und manche der Access-Automatismen gehen dann nicht mehr.
Daher ist der übliche Weg, eine Verknüpfung zu erstellen (was Du "Tabellendefinition" nennst). Hier werden üblicherweise aus Sicherheitsgründen nur Views verknüpft, keine Tabellen.

Du kannst nicht "Access beim SQL Server anmelden". Du hast eine Verbindung für einen Zweck und wenn diese beendet wird, bist Du wieder "raus". Access kann ja leider keine ADPs mehr, da war das noch der Fall, da gab es genau eine Verbindung zum SQL Server und man hatte dann Zugriff auf alle Ressourcen, also Tabellen, Views, SPs uns UDFs, alles, was der verbundene Benutzer halt sehen durfte.

Und: Jede Verknüpfung erstellt seine eigene Verbindung. Ob Access im Hintergrund Connection Pooling beherrscht, weiß ich nicht, glaube ich aber nicht.

Gruß

Christian
Bitsqueezer
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8276
Registriert: 21. Jun 2007, 12:17

Vorherige

Zurück zu Access Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 4 Gäste