VBA und Access - Furchtbar langsam zusammen?

  • Excel

Es gibt 11 Antworten in diesem Thema. Der letzte Beitrag () ist von Servinator.

    VBA und Access - Furchtbar langsam zusammen?

    Hallo Leute,
    ich soll derzeit für meine Firma in Excel ein Makro mit Anbindung an eine DB schreiben, bei welchem Verträge eingepflegt, betrachtet und bearbeitet werden können.
    Ein Vertrag enthält Grunddaten und einige Verweise auf andere Tabellen (Sachbearbeiter, Kunde, Produkte).

    In der Umsetzung habe ich eine entsprechendes Vertragsklasse erstellt, welches sich automatisch alle Daten aus der Datenbank zieht, wenn ihm die jeweilige ID zugewiesen wird. So arbeiten auch die anderen Klassen Sachbearbeiter, Kunde und Produkte.

    Das ist jedoch wirklich unausstehlich langsam. Pro Vertrag brauch das ca 8 Sekunden, hängt von der Anzahl der Produkte ab. Da in diesem System später mal ca 10.000 Verträge sein sollen, dauert das einfach viel zu lange.
    Allgemein habe ich das Gefühl, dass VBA im Vergleich zu VB.NET doch recht lahm erscheint. Auch dämmert mir so langsam, dass VBA für die geplante Sache nicht das Geeigneteste war.

    Könnt ihr mir vielleicht Tipps geben, wie ich das ganze beschleunigen könnte? Bin leider etwas ratlos...

    Viele Grüße
    Servinator

    Servinator schrieb:

    Allgemein habe ich das Gefühl, dass VBA im Vergleich zu VB.NET doch recht lahm erscheint.
    Ein wenig, aber nicht um Größenordnungen.
    Eine Tabelle mit 10.000 Datensätzen zu laden kann ein paar Sekunden dauern, aber in beiden Umgebungen.

    Es kommt halt immer darauf an, wie du darauf zugreifst.
    Wenn du für jeden einzelnen Datensatz die komplette Tabelle reinziehst, dauert das natürlich.

    Ohne deinen Code zu kennen, kann keine Aussage getroffen werden, warum es so langsam ist.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Es läuft wie folgt ab:

    Eine Abfrage zieht sich alle IDs der Verträge, erstellt ein neues Objekt "Vertrag" und gibt diesem Vertrag die gezogene ID. Dann setzt sich folgende Prozedur in Bewegung:

    Visual Basic-Quellcode

    1. Sub Werteladen()
    2. If ID <> 0 Then
    3. Dim strSQL As String
    4. Set acc = CreateObject("Access.Application")
    5. acc.Visible = False
    6. Set DB = acc.DBEngine.OpenDatabase(strDbName, False, False, PW)
    7. acc.OpenCurrentDatabase strDbName
    8. strSQL = "SELECT * FROM vertrag WHERE ID = " & ID
    9. Set rs = DB.OpenRecordset(strSQL)
    11. datStart.Tag = CInt(rs("tag"))
    12. datStart.Monat = CInt(rs("monat"))
    13. datStart.Jahr = CInt(rs("jahr"))
    14. intLaufzeit = CInt(rs("laufzeit"))
    15. intVertragsart = CInt(rs("vertragsart"))
    16. intKuendigungsfrist = CInt(rs("kuendigungsfrist"))
    17. dblLeasingrate = CDbl(rs("leasingrate"))
    18. dblLeasingvolumen = CDbl(rs("leasingvolumen"))
    19. booVersicherung = rs("versicherung")
    20. booService = rs("service")
    21. lngVertragsnr = rs("vertragsnr")
    22. Set Bearbeiter = New Sachbearbeiter
    23. Bearbeiter.ID = CInt(rs("[#SB_ID]"))
    24. Set Vertragspartner = New kunde
    25. Vertragspartner.ID = CInt(rs("[#K_ID]"))
    26. anzahl_positionen = 0
    27. rs.Close
    28. Set rs = Nothing
    30. strSQL = "SELECT vertrag.id, produkt.id, anzahl FROM (vertrag INNER JOIN P_in_V on vertrag.ID = P_in_V.[#V_ID]) INNER JOIN Produkt ON P_in_V.[#P_ID] = Produkt.ID WHERE vertrag.id = " & ID
    31. Dim i As Integer
    32. i = 0
    33. acc.Visible = False
    34. Set rs = DB.OpenRecordset(strSQL)
    36. Do Until rs.EOF
    37. ReDim Preserve Position(anzahl_positionen)
    38. Set Position(anzahl_positionen) = New Posten
    39. Set Position(anzahl_positionen).product = New Produkt
    40. Position(anzahl_positionen).product.ID = CInt(rs("produkt.id"))
    41. Position(anzahl_positionen).anzahl = CInt(rs("anzahl"))
    42. anzahl_positionen = anzahl_positionen + 1
    43. rs.MoveNext
    44. Loop
    46. rs.Close
    47. Set rs = Nothing
    48. DB.Close
    49. Set DB = Nothing
    50. acc.CloseCurrentDatabase
    51. Set acc = Nothing
    52. End If
    53. End Sub


    Bei den Klassen Kunde, Sachbearbeiter und Produkt gibt es auch jeweils eine WerteLaden()-Methode, die aufgerufen wird.
    Es dauert für einen Vertrag von 3,5 Sekunden (keine Produkte) bis 7 Sekunden (5 Produkte).

    Hallo Servinator,
    ReDim Preserve ist langsam.
    Das könnte einen leichten Zeitgewinn bringen:

    Visual Basic-Quellcode

    1. Do Until rs.EOF
    3. If UBound(Position) < anzahl_positionen Then
    4. ReDim Preserve Position(anzahl_positionen + 1000)
    5. End If
    7. Set Position(anzahl_positionen) = New Posten
    8. Set Position(anzahl_positionen).product = New Produkt
    9. Position(anzahl_positionen).product.ID = CInt(rs("produkt.id"))
    10. Position(anzahl_positionen).anzahl = CInt(rs("anzahl"))
    11. anzahl_positionen = anzahl_positionen + 1
    12. rs.MoveNext
    14. Loop
    16. ReDim Preserve Position(anzahl_positionen - 1)
    Gruss,

    Neptun

    Set acc = CreateObject("Access.Application")
    Wieso sprichst du Access als Anwendung und nicht als Datenbank an?

    Stichwort: ADODB
    Beispiel:
    mrexcel.com/forum/excel-questi…n-access.html#post3476665

    Mögliche Connectionstrings:
    connectionstrings.com/access/
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    An Neptun: Vielen Dank, dass stimmt, denn gerade das Produkte laden hat viel Zeit in Anspruch genommen.

    An petaod: Weil ich es nicht besser wusste! Habe mir das ergooglet. Bringt das denn einen Zeitgewinn? Habe leider nicht so sauber gearbeitet, so dass es etwas länger als 5 Minuten dauern würde, das zu ersetzen.

    Ich würd's auf jeden Fall umstellen und einen Vergleichstest veranstalten.
    Letztendlich ist es ja nur der Verbindungsaufbau, der anders ist.
    Aber das System kann darauf verzichten, die Access-Anwendung zu laden.
    Das dürfte schon ein ordentlicher Overhead sein.

    Ausserdem läuft es dann auch auf Systemen ohne Access-Vollversion.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Ahoi,

    ich hätte dazu mal ein/zwei Fragen.
    In Excel werden via VBA Funktionen gebaut, welche sich Daten aus der Access - "DB" holen und verarbeiten, vielleicht auch Formulare aufgesetzt um die Daten in einer ordentlichen Form anzuzeigen ?
    Was ich daran nicht verstehe ist, warum nicht gleich in Access und Excel außen vor lassen?
    Access hat doch eigentlich alles da was man brauch um Daten zu Verarbeiten und ordentlich anzeigen zu lassen.
    VBA, Formular, SQL ....
    Grüße Manu

    Was Gott dem Menschen erspart hat, kann der Computer.
    Billy ©, (*1932), Schweizer Aphoristiker
    Quelle: www.Aphorismen.de

    Manü schrieb:

    warum nicht gleich in Access und Excel außen vor lassen?
    Wenn es nur um die Anzeige geht, magst du recht haben.
    Sobald die User die Daten selbst verarbeiten wollen, bist du mit Excel auf der sichereren Seite.

    - Es können wesentlich mehr Leute mit Excel umgehen als mit Access
    - Auf vielen Rechnern ist keine Access installiert.
    - Excel hat mehr Möglichkeiten und Freiheitsgrade für Datennachverarbeitung (Pivot, Chart, PowerQuery ...)
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    petaod schrieb:

    Ich würd's auf jeden Fall umstellen und einen Vergleichstest veranstalten.
    Letztendlich ist es ja nur der Verbindungsaufbau, der anders ist.
    Aber das System kann darauf verzichten, die Access-Anwendung zu laden.
    Das dürfte schon ein ordentlicher Overhead sein.

    Ausserdem läuft es dann auch auf Systemen ohne Access-Vollversion.



    Stimmt, ich werde das bei Gelegenheit umändern.

    petaod schrieb:

    Es können wesentlich mehr Leute mit Excel umgehen als mit Access

    Deswegen werden Nutzerfreundliche Formulare gebaut.

    petaod schrieb:

    Auf vielen Rechnern ist keine Access installiert.

    Dann liegt eine Access - DB auf einem Server und bekommt Zugriffe von einer Multiuseranwendung ? Dann lieber auf den Server eine MySQL-DB legen und darauf von excel aus zugreifen. Ihr wisst selbst das Access nix von mehren Zugriffen hält.

    petaod schrieb:

    Excel hat mehr Möglichkeiten und Freiheitsgrade für Datennachverarbeitung (Pivot, Chart, PowerQuery ...)

    Das lass ich gelten ;)
    Grüße Manu

    Was Gott dem Menschen erspart hat, kann der Computer.
    Billy ©, (*1932), Schweizer Aphoristiker
    Quelle: www.Aphorismen.de

    Ich würd's auf jeden Fall umstellen und einen Vergleichstest veranstalten.
    Letztendlich ist es ja nur der Verbindungsaufbau, der anders ist.
    Aber das System kann darauf verzichten, die Access-Anwendung zu laden.
    Das dürfte schon ein ordentlicher Overhead sein.

    Ausserdem läuft es dann auch auf Systemen ohne Access-Vollversion.

    Hallo Petatod,

    danke für den Hinweis, genau daran hat es gelegen.

    Ich musste jedoch etwas länger suchen, bis ich es passend hatte, deswegen poste ich noch die Methode mit Access 2010. (Info: Bei verschlüsselter Datenbank müsst ihr einstellen, dass diese abwärtskompatibel sein muss, andernfalls kommt es zu Fehlern.)
    Verbindung aufbauen

    Visual Basic-Quellcode

    1. Dim con As New ADODB.Connection
    2. With con
    3. .Provider = "Microsoft.ACE.OLEDB.12.0"
    4. .Properties("Jet OLEDB:Database Password") = "PASSWORT"
    5. .Open "DATENBANKPFAD"
    6. End With

    SQL-Befehl ausführen

    Visual Basic-Quellcode

    1. con.Execute "SQL-Befehl"

    SQL-Abfrage

    Visual Basic-Quellcode

    1. Dim rs As New ADODB.Recordset
    2. rs.Open "SQL-Query", con, adOpenDynamic, adLockReadOnly
    3. Do Until rs.EOF
    4. Msgbox rs("Feld1")
    5. rs.MoveNext
    6. Loop
    7. rs.close

Facebook
Telefonspende