Connection String zu Access 2013/2016 ODBC-OLEDB -Jet

Question

Wenn ich aus vb.Net eine Verbindung zu einer Access DB aufbauen möchte um zb. eine Aktionsabfrage abzusetzen

was ist die "richtige" und Zukunftsweisende Verbindung

ODBC

OLEDB

Jet

oder etwas anderes

Answer 1

Hi,
da Access als eine Ein-Platz-Lösung konzipiert ist, ist es praktisch belanglos, was Du nimmst. Ich habe die besten Erfahrungen mit der Jet.

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks

Answer 2

da Access als eine Ein-Platz-Lösung konzipiert ist, ...

Hallo Peter, 

So ganz richtig ist das nicht, siehe auch den entsprechenden FAQ-Artikel.

Ich hab mal eine Access-Anwendung mit > 80 Benutzern betrieben, auf einem Jet-Backend. Das ist möglich, kommt halt auf die Anwendung an und vor allem auf den Entwickler. ;-) Es liegt sicher im Grenzbereich, daher nicht empfehlenswert, aber möglich.

Ansonsten:

• ACEDAO ist aktuell (früher Jet/DAO) und kann beides (mdb/accdb) lesen.
• ODBC ist ebenfalls möglich und wird aktuell wieder weiterentwickelt. 

Gruss - Peter (MVP f. Access)

Answer 3

Hi Peter,
möglich ist der gemeinsame Zugriff auf die Access-Datei aus unterschiedlichen Anwendungen bzw. Threads.

Ich habe aber die leidvolle Erfahrung gemacht, dass bei Intensivierung der Zugriffe auf die Access-Datei aus mehreren threads die Jet den Dienst versagt. Konkret ging es bei mir um eine Web Anwendung, die lediglich die Verbindung geöffnet hat, gelesen und geschrieben hat. Alles in Using gekapselt und mit und auch ohne ConnectionPooling getestet. Der Fehler äußerte sich im Absturz der Jet, die dann keine weiteren Zugriffe auf die Access-Datei zuließ. Zerstört war im Ergebnis immer die ldb. Die Access-Datei lag auf dem gleichen Rechner wie der IIS. Eine Nachfrage bei microsoft ergab als Antwort, dass Access eine Ein-Platz-Lösung ist und für einen Mehrfachzugriff auf die Access-Datei es keine Garantie gibt. Es mag sein, dass, wie in dem von Dir verlinkten FAQ-Artikel bemerkt, eine geringe Anzahl von Clients mit separaten Access-Anwendungen als Front- und Backend problemlos funktionieren. Auch in meiner Web Anwendung gab es keine Probleme, solange es wenige Zugriffe auf die Access-Datei waren. Mit der Übertragung der Access-Tabellen auf einen SQL Server ohne weitere Änderung des Programmes (lediglich OleDB-Klassen gegen SQL-Klassen und ConnectionString ausgetauscht), hat die Anwendung problemlos auch bei hoher Last funktioniert.

Diese meine Erfahrung ist aber schon etwas älter. Möglich, dass Microsoft die aktuelle Version der Jet so weit verbessert hat, dass es die Probleme heut nicht mehr gibt. Getestet habe ich das mit der aktuellen AccessDatabaseEngine nicht wieder.

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks

Answer 4

Hi Peter,

Nach meinem Wissensstand gab es noch nie irgendwelche Access-Spezialisten im Support, das wurde immer stiefmütterlich von anderen Bereichen mit abgedeckt. Insofern ist die Info nachvollziehbar, wenn auch falsch.

Was dein Problem war, kann ich so nicht sagen, das müsste man versionsgetreu nachstellen und ich kann auch nicht ausschließen, dass es tatsächlich ein Problem im Mehrbenutzerumfeld gab, aber die Verallgemeinerung ist schlicht falsch, da gibt es zu viele positive Beispiele, in denen es seit Jahrzehnten (genau 2,5 Jahrzehnte seit diesem Jahr ;-) ) funktioniert. 

Typische "Probleme" in der Mehrbenutzerumgebung sind fehlende Rechte auf das Backend-Verzeichnis. Denn jeder Benutzer muss die ldb/laccdb erstellen können, wenn er als erster auf die mdb/accdb zugreift, er muss aktualisieren können, und wenn er seinen Zugriff als letzter beendet, muss er die ldb/laccdb löschen können. Das wird von Admins oft nicht zugelassen. Des Weiteren gibt es Prozesse wie Virenchecker und Backup, die gerne mal exklusiv auf Dateien zugreifen, was Access, das wegen Mehrbenutzerfähigkeit ;-) nicht sperrt (es sei denn, man öffnet gewollt exklusiv), wiederum gar nicht gefällt. Das waren nur 2 Beispiele. Würde dein Problem noch bestehen, könnte man tiefer graben, aber das ist in diesem Thread nur ein Nebenkriegsschauplatz. 

Gruss - Peter

Answer 5

Da kann ich dir nur recht geben - auch ich habe eine Anwendungen die sehr gut und schnell laufen mit >20 User

Auch wenn ich diese mittlerweile an MSSQL angebunden habe.

Aber das wird wohl immer ein leidiges Thema bleiben was nicht tot zu kriegen ist

Answer 6

Hi Peter,
bis ich in einer produktiven Anwendung auf die Nase gefallen bin und der MS Support mir erklärte, dass die Jet mit einer Access-Datei nur als Ein-Platz-Anwendung konzipiert ist, war ich auch genau Deiner Meinung.

Der wesentliche Unterschied einer Access Anwendung, die auf eine andere Access-Datei mit nur Tabellen zugreift und einer Windows Anwendung ist vor allem der Verbindungsaufbau. Eine Access Anwendung baut am Anfang die Verbindung auf, öffnet sie und hält sie bis Programmende offen. Da ist der Aufwand für die Zugriffe auf die ldb gering. In einer Web Anwendung wird bei jedem Request die Verbindung erneut geöffnet und spätestens nach dem Response wieder geschlossen. Und da diese Prozesse bei höherem Aufkommen von Anfragen auch parallel (multi-threading) laufen, entsteht ein Stress bei den Zugriffen auf die ldb. Und das hat die Jet in der Vergangenheit nicht verkraftet.

Und da die Ausgangsfrage zwar im Access-Forum stand, aber VB.NET betraf, habe ich geantwortet, da ich diese leidvollen Erfahrungen gemacht hatte (übrigens auch mit VB.NET). Wenn es sich also bei der Ausgangsfrage um eine einfache Windows Anwendung handelt, die auf eine Access-Datei zugreifen soll, dann sollte die Anwendung zu Beginn die Verbindung öffnen und zum Programmende wieder schließen. Da würde dann der Stress mit der ldb auf ein Minimum reduziert. Die Empfehlung vom Microsoft zur Lokalisierung der Zugriffe und der Nutzung des ConnectionPoolings sollte beim Zugriff auf Access-Dateien mit Vorsicht betrachtet werden.

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks

Answer 7

Hi Peter,

Soweit ich dich verstanden habe, hast du dich jeweils nur kurz in der mdb aufgehalten, oder? Hast du mal probiert, die mdb exklusiv anzusprechen? Dann wird sie zwar gesperrt (nur kurz, wie ich hoffe), aber es wird keine ldb angelegt, was deinem Ablauf zugute kommen könnte, und die nächsten müssten kurz warten.

Gruss - Peter

Answer 8

Hi Peter,
in einer Web Anwendung ist der Zugriff auf die Daten nur recht kurz, da mit dem Request der Aufbau der Seite (Instanziierung der Klasse) beginnt und direkt nach dem Rendern wieder beendet wird (Freigabe der Ressourcen). Den exklusiven Zugriff hatte ich in diesem Projekt nicht getestet. Ob das dann beim multi-threading etwas bringt und auch funktioniert, kann ich nicht sagen. Vielleicht wäre es aber mal eine Simulation wert.

Answer 9

Hi Peter, 

In der Theorie würde der 2. Zugriff erst ausgeführt, wenn der erste wieder freigegeben hat. Es würde also funktionieren, könnte aber zu Performanceproblemen führen. Ich weiss es allerdings auch nicht, aber dein Projekt hat mich neugierig gemacht. ;-) Wenn du's mal testen willst und Unterstützung brauchst, lass es mich wissen (PM siehe MVP-Profil).

Gruss - Peter

Answer 10

Hi Peter,
vielen Dank für das Angebot. Auf meinem Surface Pro 4 mit 16 GB RAM und i7, aktuelles Windows 10, Version 18093 (Build 17134.228) habe ich es mal getestet und der Fehler kommt recht schnell. Hier das VB.NET-Programm mit einer Datenbank mit der Tabelle Tab1 mit einem Autowert-Feld (ID) und einem Textfeld (Feld1). Vielleicht ist die Testumgebung falsche. Dann würde ich mich um eine Rückmeldung freuen.

Imports System.Data.OleDb
Imports System.Threading

Module Module1
  Sub Main()
    Dim c As New Demo
    c.Execute()
    Console.WriteLine("weiter mit Taste")
    Console.ReadKey()
  End Sub

  Friend Class Demo
    Friend Sub Execute()
      For i = 1 To 100
        Dim th As New Thread(New ThreadStart(AddressOf DBZugriff))
        th.Start()
      Next i
    End Sub

    Private cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"
    Private rnd As New Random

    Private Sub DBZugriff()
      Try
        For i = 1 To 1000
          Using cn As New OleDbConnection(cnString)
            cn.Open()
            ' Anzahl der Datensätze holen
            Dim sql = "SELECT count(*) FROM Tab1"
            Using cmd As New OleDbCommand(sql, cn)
              Dim n = CType(cmd.ExecuteScalar, Integer)
              ' Datensatz hinzufügen
              sql = "INSERT INTO Tab1(Feld1) VALUES(@p1)"
              cmd.CommandText = sql
              cmd.Parameters.Add("@p1", OleDbType.VarWChar).Value = $"Zeile {Now.ToString()}"
              Dim ret = cmd.ExecuteNonQuery
              If ret <> 1 Then Throw New Exception("Fehler bim Hinzufügen")
              ' Datensatz zufällig löschen
              If rnd.NextDouble > 0.9 Then
                sql = $"DELETE From Tab1 WHERE ID = {rnd.Next(1, n + 1)}"
                cmd.CommandText = sql
                cmd.Parameters.Add("@p1", OleDbType.VarWChar).Value = $"Zeile {Now.ToString()}"
                ret = cmd.ExecuteNonQuery
              End If
            End Using
          End Using
        Next
      Catch ex As Exception
        Dim ex1 = ex
        Dim msg As String = ""
        While ex1 IsNot Nothing
          msg &= ex1.Message & vbNewLine
          ex1 = ex1.InnerException
        End While
        Console.WriteLine(msg)
      End Try
    End Sub

  End Class

End Module

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks

Answer 11

Hallo Peter,

Mach mal in den Connection-String den Mode-Parameter rein:

Private cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;Mode=Share Exclusive;"

Gruss - Peter

Answer 12

Hi Peter,
der vorherige Fehler (kein exklusiver Zugriff):

System.Data.OleDb.OleDbException (0x80004005): Unbekannter Fehler

tritt bei exklusivem Zugriff nicht mehr auf. Es kommt dagegen viel häufiger beim Verbindung Öffnen der Fehler:

System.Data.OleDb.OleDbException (0x80004005): Could not use ' … \bin\Debug\Database1.accdb'; file already in use.

Komisch ist nur, dass eine Serialisierung des Connection.Open mit Synclock keine Verbesserung bringt. Vermutlich laufen da in der Jet asynchrone Prozesse, die bei Parallelarbeit Probleme bringen.

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks

Answer 13

der vorherige Fehler (kein exklusiver Zugriff):

System.Data.OleDb.OleDbException (0x80004005): Unbekannter Fehler

tritt bei exklusivem Zugriff nicht mehr auf. Es kommt dagegen viel häufiger beim Verbindung Öffnen der Fehler:

System.Data.OleDb.OleDbException (0x80004005): Could not use ' … \bin\Debug\Database1.accdb'; file already in use.

Komisch ist nur, dass eine Serialisierung des Connection.Open mit Synclock keine Verbesserung bringt. Vermutlich laufen da in der Jet asynchrone Prozesse, die bei Parallelarbeit Probleme bringen.

Schade, aber nicht ganz unerwartet.

Und es liegt nicht an ACE, dem neuen Jet, sondern am Provider. Durch den Parameter wird ein Share-Lock vom OS auf Database1.accdb gesetzt und der Provider reagiert beim nächsten Zugriff auf die Sperre mit Abbruch und "file already in use", statt zu warten, bis sie wieder frei ist, oder öfters zu probieren.

Wie gesagt: schade.

Gruss - Peter

Answer 14

Hi Peter,
was ist der Unterschied zwischen Jet und Provider, wenn ich im ConnectionString angebe:

Provider=Microsoft.ACE.OLEDB.12.0 ?

Die vollständige Fehlernachricht (im Fall Mode=Share Exclusive) ist:

System.Data.OleDb.OleDbException (0x80004005): Could not use 'C:\Me\DEV2017\Projects\ConsoleApp1VB\ConsoleApp1\bin\Debug\Database1.accdb'; file already in use.
   bei System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   bei System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   bei System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   bei System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   bei System.Data.OleDb.OleDbConnection.Open()
   bei ConsoleApp1.Module31.Demo.DBZugriff() in C:\Me\DEV2017\Projects\ConsoleApp1VB\ConsoleApp1\Module31.vb:Zeile 39.

Ohne Mode=Share Exclusive kommt der Fehler an der gleichen Stelle (Connection.Open) und nach kurzer Zeit ist die Jet-Engine blockiert und weitere Zugriffe sind erst nach Beenden und Neustart des Prozesses (Projektes) möglich. Bei einer Web Anwendung ist dazu ein Restart des Pools (bzw. IISReset) auszuführen.

System.Data.OleDb.OleDbException (0x80004005): Unbekannter Fehler
   bei System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   bei System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   bei System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   bei System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   bei System.Data.OleDb.OleDbConnection.Open()
   bei ConsoleApp1.Module31.Demo.DBZugriff() in C:\Me\DEV2017\Projects\ConsoleApp1VB\ConsoleApp1\Module31.vb:Zeile 39.

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks

Answer 15

Hallo Peter,

was ist der Unterschied zwischen Jet und Provider, wenn ich im ConnectionString angebe:

Provider=Microsoft.ACE.OLEDB.12.0 ?

Microsoft.ACE.OLEDB.12.0 ist der ADO-basierte Provider, der die Korrespondenz zwischen deiner Anwendung und der Jet-Engine bzw. ACE betreibt, die wiederum die Datei *.mdb bzw. *.accdb bearbeiten, in deinem Fall ACE (ACcess data Engine ;-)) und accdb (Access selbst verwendet übrigens DAO bzw ACEDAO für den Zugriff).

Die Engine bekommt vom Provider über den Parameter Share Exclusive den Auftrag, die Datei zu sperren, solange der Zugriff dauert, und nichts anderes macht sie. 

Der Performancegewinn, den ich mir durch die weggefallenen laccdb-Anlage und -Löschung versprochen hatte, wird durch den zusätzlichen Sharelock wieder kaputt gemacht, vielleicht sogar schlimmer, wenn die Latenz des Servers dazukommt. 

Um auf die Anfangs-Aussage, wegen der ich angesprungen bin, zurück zukommen: Ok, Access bzw. Jet/ACE sind zwar mehrplatzfähig, aber für ein Szenario als Web-Backend mit Heavy Traffic ungeeignet. ;-)  

Gruss - Peter 

 

Answer 16

Hi Peter,

Um auf die Anfangs-Aussage, wegen der ich angesprungen bin, zurück zukommen: Ok, Access bzw. Jet/ACE sind zwar mehrplatzfähig, aber für ein Szenario als Web-Backend mit Heavy Traffic ungeeignet. ;-)  

ich würde das anders formulieren: je häufiger Connection.Open/Close parallel zur Arbeit in anderen Prozessen/Clients ausgeführt wird, desto höher die Wahrscheinlichkeit, dass Access bzw. Jet/ACE damit nicht zurecht kommt. Das heißt, dass ein Open zu Programmanfang und ein Close zu Programmende und dazwischen alle Zugriffe über längere Zeit ausgeführt werden, die Sicherheit gibt, dass mehrere Prozesse/Clients parallel die Daten ohne Schwierigkeiten verarbeiten können. Die Messungen unter meinen Bedingungen haben ergeben, dass ab ca. 35 paralleler Zugriffe die Probleme beginnen.

Da in einer WebPage üblicherweise mit jedem Request der Zugriff neu aufgebaut wird, ist da die Wahrscheinlichkeit eines Problems viel höher.

Ich habe außerdem bemerkt, wenn ein neues Command-Objekt erzeugt und die Connection zugewiesen wird, dann gibt es auch Probleme, wenn dieser Prozess zu schnell hintereinander parallel ausgeführt wird. Das sieht aus, als wäre eine OleDbConnection bzw. die Connection-Eigenschaft beim OleDbCommand thread-affin bzw. nicht threadsicher.

---

--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks