Programmieren - alles kontrollieren 4.941 Themen, 20.708 Beiträge

Verfolgung starten Optionen

VBA Verlinkte Excel-Tabellen: Access lahmt wenn Excel offen

Neo3000 / 6 Antworten / Baumansicht Nickles

Hallo VBA-Gurus & co. :-)

Ich bin kein besonders mächtiger VBA-Experte aber einige Ahnung hab ich schon.
Dennoch hab hab ich ein kleines aber lästiges Problem mit verknüpften Excel 2007 Tabellen in Access 2007 und ich hab keinen Ansatz wo ich was drehen kann. Das sieht folgendermaßen aus:

Mit der Auswahlabfrage "SELECT * FROM [excel 12.0;imex=0;DATABASE=" & strDISPO & "].[Januar$] UNION ALL ..." erstelle ich eine Verknüpfung zu einer in der Variablen "strDISPO" enthaltenen Excel-Datei bzw. den 12 Sheets in der Datei. Das klappt einwandfrei. Alle Datensätze aus allen Excelsheets in einer Abfrage.
Weiterhin habe ich Listefelder auf Formualren mit denen ich Werte aus dieser Abfrage weiter Filtere mit neuen Abfragen die dann die WHERE Klausel enthalten. Mal mehr mal weniger Kriterien.
Sachen wie "Dim blabla as Excel.Worksheet" und dazugehörige Dinge kann ich nicht verwenden da ich Daten auch aus Access raus, in Excel rein ändern muss/können sollte und das wohl bei 2007 so einfach nicht mehr geht. Aber mit den Abfragen schon. Geht auch bis auf folgendes nerv-Problem.

Jetzt Kommts:
Ist die Exceldatei geschlossen (Excel selbst oder eine andere Datei kann ruhig geöffnet sein) geht alles recht flott und annehmbar, aber ist DIE Datei geöffnet die ich mit den Abfragen verlinke kotzt sich Access bei den Abfragen einer ab. Es dauert ewig bis sie ausgeführt werden. Manche schneller manche ewig, aber alle braucht um einiges länger. Die längste war bei 45 Sekunden, die bei geschlossener Exceldatei nur ca. 3 Sekunden brauch. Wäre Klasse wenn jemand nen Tip hätte was zur Hölle da stört, bzw warum bei geschlossener Exceldatei alle flutscht und sobald sie offen ist das so extrem bremst. Ich dreh bald am Rad *g*. Evtl kennt ja jemand das Problem
Vielen Dank schon mal

Greetz
Kai

bei Antwort benachrichtigen
ChrE Neo3000 „VBA Verlinkte Excel-Tabellen: Access lahmt wenn Excel offen“
Optionen

Hallo,

Ich habe keine Ahnung, schiesse mal ins Blaue.
Evtl. handelt es sich bei mehrfacher Nutzung um ein Locking-Problem.
Mehrere Anwender greifen gleichzeitig auf den selben Datenbestand zu.

Wie z.B. hier:

http://www.office-loesung.de/ftopic249981_0_0_asc.php

Vielleicht kann man was am Access einstellen.

Gruss

ChrE

bei Antwort benachrichtigen
neanderix Neo3000 „VBA Verlinkte Excel-Tabellen: Access lahmt wenn Excel offen“
Optionen
Mit der Auswahlabfrage "SELECT * FROM [excel 12.0;imex=0;DATABASE=" & strDISPO & "].[Januar$] UNION ALL ..." erstelle ich eine Verknüpfung zu einer in der Variablen "strDISPO" enthaltenen Excel-Datei bzw. den 12 Sheets in der Datei.

Nein. du erstellst damit keine Verknüpfung, sondern fragst die Daten direkt ab. Noch dazu nutzt du "SELECT * FROM" was *immer* zeitintensiver ist, als wenn du nach dem SELECT alle Tabellenfelder aufführen würdest (bei "*" muss sich Access die Felder selber suchen, gibst du sie an, kann es direkt zugreifen).

Dazu kommt dann noch das von ChrE gesagte, sowie die Tatsache, dass UNION Abfragen erstens meist recht langsam sind (umso langsamer, je mehr Tabellen miteinander verknüpft sind und je mehr Daten diese Tabellen enthalten - denn Access muss hier einen Full Table Scan über alle 12 Tabellen machen.)

Ich würde hier anders vorgehen; es gibt 2 Möglichkeiten:

1. die Tabellen in die Access-DB verlinken (im DB-Fenster erscheinen die dann wie verlinkte Tabellen aus einem Backend; also mit -> vor dem Tabellennamen).

Dazu musst du wahrscheinlich die 12 Worksheets einzeln verlinken - aber das musst du auch nur einmal machen.

2. über Office-Automation Excel aus Access heraus ansprechen und die 12 Tabellen eportieren, anschliessend in je eine Access-Tabelle importieren - und erst über diese Tabellen den UNION machen.

Volker


Computers are like airconditioners - they stop working properly when you open Windows Ich bin unschuldig, ich habe sie nicht gewählt!
bei Antwort benachrichtigen
Neo3000 neanderix „ Nein. du erstellst damit keine Verknüpfung, sondern fragst die Daten direkt...“
Optionen

Hi all,

@ChrE:

Merci für deine Antwort
Dies kann ich ausschließen da ich noch in der Entwicklungsphase bin und das ganze lokal abläuft. Zudem bin ich der Einzige der damit arbeitet. Dennoch danke für deinen Link, sobald das Ding mal auf nem Netz liegt und mehrere daran rumspielen, hab ich bei Problemen schon mal ne kleine Anlaufstelle :-).

@neanderix:

Merci für deine Antwort
Du hast natürlich recht (mein Fehler), SELECT frägt direkt ab und verlinkt nicht (hatt vorher zuviel mit aclink usw. zu tun das sich dass ich grad zu allem "verlinken" sage *g*)
richtig "Verlinken" kann ich die Tabellen auch nicht, hab ich schon versucht, denn ich muss aus den Tabellen lesen, aber auch reinschreiben aus Access. Microsoft hatte wohl aus nem Rechtsstreit diese Funktion rausnehmen müssen (steht auf der website von Donkarl) und daher wird dort meine verwendete Methode als Ausweichmöglichkeit angegeben. Geht ja auch alles klasse bis auf die Geschwindigkeit wenn die Exceldatei geöffnet wird, dann wirds essig und man kann zugucken wie man älter wird.
Wegen der UNION-Sache: da sich die Abfragen variabel aus allen Tabellenblättern zusammensetzen (z.B. zeig mir alle Datensätze von Jan bis Dez wo der Auftraggeber "Kunz" heißt) oder (zeig mir alle Datensätze von Jan bis Dez wo der Auftraggeber "Kunz" heißt und noch keine Rechnungsnummer vergeben ist) usw...
Da kann ich wohl nur dies verwenden (UNION ALL). Da werd ich dann wohl mit leben müssen hm?

Greetz
Kai

bei Antwort benachrichtigen
ChrE Neo3000 „Hi all, @ChrE: Merci für deine Antwort Dies kann ich ausschließen da ich noch...“
Optionen

Hallo,

> Zudem bin ich der Einzige der damit arbeitet.

Das ist egal. Wichtig ist, wie viele Programme (oder mehrere Instanzen eines Programms)
die Exceltabellen öffnen. Die Anzahl der User interessiert da nicht.
Es schein ja so zu sein, dass Excel und Access laufen, wenn es langsam wird.

Abhilfe: Replikation der Daten in eine "richtige" Datenbank, mit ordentlichem record locking.
Zugriff dann per Access über ado.net oder odbc.
Gab es nicht mal einen kostenlosen MS-SQL Server?
Fang nicht damit an, deine Datenbank an Excel anzupassen.

Gruss

ChrE

bei Antwort benachrichtigen
neanderix Neo3000 „VBA Verlinkte Excel-Tabellen: Access lahmt wenn Excel offen“
Optionen
Wegen der UNION-Sache: da sich die Abfragen variabel aus allen Tabellenblättern zusammensetzen (z.B. zeig mir alle Datensätze von Jan bis Dez wo der Auftraggeber "Kunz" heißt) oder (zeig mir alle Datensätze von Jan bis Dez wo der Auftraggeber "Kunz" heißt und noch keine Rechnungsnummer vergeben ist) usw...
Da kann ich wohl nur dies verwenden (UNION ALL). Da werd ich dann wohl mit leben müssen hm?

Tja - das ist eine "Krankheit" unter Excelianern, erstens für jeden Monat ein Tabellenblat aufzumachen und dann zweitens diese Struktur mit Mitteln einer relationalen DB wie Access oder Oracle oder... abfragen zu wollen ;))

In einer DB lägen alle diese Daten in *einer* Tabelle - aber das weisst du ja, da du ja den UNION nutzt, um die Daten zusammenzuführen.

Mein Vorschlag ist daher, auf die Office-Automation zurückzugreifen und über VBA Excel direkt zu öffnen und einen Export der fraglichen Daten anzustoßen (am besten in eine einzige Arbeitsmappe schreiben lassen), anschliessend die Daten automatisiert nach Access importieren.

Andere Idee:

Ersetze doch mal die UNION-Abfrage (ein UNION über 12 Tabellen *kann* nicht performant sein)
durch 12 Anfügeabfragen, die du per VBA nacheinander aufrufst - und die die Daten in *eine* Accesstabelle laden, in der auch der jeweilige Auftragsmonat mitgeführt wird.



Computers are like airconditioners - they stop working properly when you open Windows Ich bin unschuldig, ich habe sie nicht gewählt!
bei Antwort benachrichtigen
Neo3000 Nachtrag zu: „VBA Verlinkte Excel-Tabellen: Access lahmt wenn Excel offen“
Optionen

Hi all und eine frohes neues Jahr...auch wenns schon ne weile läuft :-)

war lange nicht hier, sorry...vieeeeel unterwegs gewesen (weihnachten, silvester, geburtstage, etc...) Nervig aber notwendig gewesen...

@ChrE
Ja, Excel und Access laufen dann beide zu dem Zeitpunkt aber andere Programme greifen dann nicht auf Excel zu, nur Access halt.
Das mit der "echten" DB wäre mir auch lieb, aber ich erstelle das Proggi für jemand der eigentlich die Excel-Tabelle so weiterverwenden will, auch wegen farblichen Markierungen der Datensätez usw. und für die bisher benötigten Funktionen reicht das erstmal völlig aus...wenn das Speedproblem halt nicht wär.
VB express und SQL Server 2008 express ist dafür ein Thema aber da muss ich mich erst noch richtig reinschaffen.

@neanderix
Wie ich bei ChrE schon geschrieben hab, mach ich das Proggi für jemand der die Excel-Tabelle weiternutzen will und die Aufteilung in 12 Tabellenblätter usw. auch. Momentan zumindest noch. Ich würde das auch gern eher alles in eine SQL DB stopfen und dann damit arbeiten aber dafür muss ich noch mehr knowhow ergattern.
Hatte ich auch schon im Visier, aber wenn ich die Anfügeabfragen benutze und dann Daten ändere bleibt die Exceltabelle aber unberührt, ausser ich exportiere wieder, aber ich denke das würde eher noch länger dauern, oder?

Greetz
Kai

bei Antwort benachrichtigen