Office: Daten übertragung von EXCEL nach ACCESS , löschen der Tabelle in ACCESS vor der Über.

Hallo Leute,
Ich möchte gerne eine Access Datenbank von Excel aus per Makro mit Daten füllen.
Nach dem verbinden mit der Datenbank sollen die neu einzuladenden Tabellen erst gelöscht (gelehrt) werden, bevor die neuen Datenübertragen werden!

Geht das überhaupt?

Ich weiss man kann Excel Tabellen auch verlinken, das möchte ich aber nicht.

Vielen Dank für eure Hilfe.

Gruß

JBO73

:)

 

Moin, JBO73,

eine Möglichkeit besteht z.B. durch den Aufruf einer gespeicherten Löschabfrage in Access (oder eines Makros), bevor dann die Daten geschrieben werden. Access-Datenbank öffnen und deren Makro starten bzw. Tabellenbearbeitung eines Access- Backends aus Excel (unter Office 2000) oder ein Blick in die DB-Wiki könnten helfen...

 

Anmerkungen:
(1) Ein Löschen von Access-Tabellen ist bedenklich, da dadurch Beziehungen zwischen verschiedenen Tabellen zerstört werden können - eine richtige DB hat Beziehungen zwischen Tabellen. Sinnvoller ist das Löschen und Neuanlegen der enthaltenen Datensätze.

(2) Durch das Löschen und Neuanlegen von Datensätzen bläht sich die Datenbank auf, da der belegte Speicherplatz der alten Datensätze nicht automatisch freigegeben wird. Hier wird (bei größeren Mengen) ein nachträgliches Komprimieren der DB sinnvoll sein, was u.a. auch diese Speicherfreigabe bewirkt.

(3) Hinweis zum zweiten Link: Ein Recordset hat eine Feldauflistung. Diese kann in Schleifen ebenso genutzt werden, wenn die Reihenfolge der Felder mit der der zugehörigen Excelzellen übereinstimmt:
Code:

 

Moin, Eberhard,

woher weißt Du, dass alle Felder übernommen werden sollen?

 

\@jinx: Das weiß ich nicht. Das ist aber auch nicht entscheidend. Eine Anzahl lngAnzahl kann man auch auf bspw. 2 begrenzen, und man muss auch nicht beim ersten Feld anfangen. Ich sprach von der Reihenfolge. Ich hoffe, der Anwender weiß, was er will.

Ihren richtigen Charme zeigt eine Schleife natürlich erst bei großen Zahlen (Übersicht, Wartbarkeit).
Mein Hinweis war als Ergänzung gedacht.

Oben müsste es richtigerweise heißen
Code:

(die Auflistung der Excelzellen beginnt bei 1, die Feldauflistung im Recordset jedoch bei 0),

 

Ich muss gestehen, dass sehr neu bin in der VBA Programmierung.

@jinx: Die Tabelle soll genauso wie sie ist übernommen werden. D.h. Spalte A = 1.Spalte in der Datenbank usw.

Die Grundstruktur der Programmierung ist aus einem Buch. Da wird leider nur ein Sheet in die Datenbank übernommen. Im Ergebnis sind es aber 5 Tabellen die übernommen werden sollen.

@ebs17: (1)Es sollen nur der Inhalte der Access Datenbank gelöscht werden, nicht das ganze Tabelle.
@ebs17: (2) Kann man das "komprimieren" auch per Makro ausführen lassen, was hat es sonst für Auswirkungen ?
@ebs17: (3)Die Felder stimmen überein.

Vielen Dank für eure Hilfe.

Gruß

JBO73

 

Hier ist ein geteiltes Beispielprojekt zum Exportieren von Exceltabellen in eine Access-Datenbank:

- Im Beispiel enthalten die Exceltabellen jeweils eine Titelzeile (für Variante 2 notwendig). Die Access-Tabellen enthalten jeweils ein zusätzliches Feld ID (Autowert), das sich somit selbst füllt.

- Die Sub Aufruf dient nur zum Aufrufen der Exportroutine. Hier können Excel-Mappe, Access-DB sowie die Quell- und Zieltabellen übergeben werden. An der Stelle könnte man statt des direkten Einsetzens der Bezeichnungen wie gezeigt diese aus einer weiteren Tabelle auslesen, Dateien per Dateiauswahldialog ermitteln usw.

- Die Sub ExportToAccess1 verwendet ein DAO-Recordset für den Zugriff auf die Access-DB.

- Die Sub ExportToAccess2 bringt SQL-Anweisungen in Anwendung. Dies sollte schneller sein, spürbar dann vor allem bei größeren Datenmengen.

Ich bin mir hier aber nicht sicher, ob Variante 2 auch funktioniert, wenn keine Access-Installation vorhanden ist und man nur mit einer puren MDB arbeitet. Hier würde mich eine Rückmeldung freuen, ob bzw. mit welchen zusätzlichen Komponenten diese zweite Variante verwendbar ist (ich kann und will jetzt selber keine accessfreie Umgebung für einen Test anlegen).

Zum Komprimieren: Das Aufblähen der DB erfolgt dann sichtbar, wenn große Mengen an Datensätzen gelöscht und neu angelegt werden (Augenmaß behalten!). Im Beispielprojekt habe ich eine zusätzliche Sub dafür hinterlegt.
Das Komprimieren sollte aber nur dann angewandt werden, wenn kein weiterer Zugriff auf die DB erfolgt. Das Komprimieren könnte auch und sollte sogar vermutlich besser durch die Access-Anwendung selber vorgenommen werden.
Code:

 

Hallo ebs17,

vielen Dank, schon mal im Voraus! Läuft dieses "DAO-Recordset" auf allen normalen Win XP PC's mit Office 2007 oder muss ich dafür was installieren?

Gruß

Jens

 

Du musst wie beschrieben einen Verweis auf die notwendige Bibliothek (DAO) setzen, siehe Verweise.

 

\@ebs17: Ich hab mal dieses Makro ausprobiert und es stösst sofort auf Fehler in der ersten Zeile:
...
Do While Not Recordset.EOF
For k = 0 To lngAnzahl
Cells(i, k + 1).Value = Recordset(k)
Next
i = i + 1
Recordset.MoveNext
Loop
...

Was könnte der Grund sein
Gruß
JBO73

 

Hallo JBO73,

dieses ist kein Makro, sondern nur ein Codeauszug (besonders sichtbar durch ...). Die Punkte bedeuten, dass da noch etwas (anderes) stehen muss. Blindes Kopieren hilft Dir nicht weiter. Man sollte schon etwas, besser alles, verstehen, was man da anwendet.

Vollständige Makros stehen in #7.

Die Fehlermeldung (die Du hier nicht mitteilst) gibt Aufschluss darüber.

 

Hier mein Makro:

Sub ExportToAccess_ET_CAR()
Dim i As Integer
' Verbindung und Datensätze dimensionieren
Dim ADODBConnection As New ADODB.Connection
Dim ADODBRecordset As New ADODB.Recordset

'Aktivieren des Sheets ET_CAR
Worksheets("ET_CAR").Activate

' Verbindung herstellen
ADODBConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & ThisWorkbook.Path & _
"\MINNISUCH.mdb;"

' Tabelle "ET_CAR" ansprechen
ADODBRecordset.Open "ET_CAR", _
ADODBConnection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
' Daten löschen
Do While Not Recordset.EOF
For k = 0 To lngAnzahl
Cells(i, k + 1).Value = Recordset(k)
Next
i = i + 1
Recordset.MoveNext
Loop

' Daten übertragen
For i = 1 To ActiveSheet.UsedRange.Rows.Count
With ADODBRecordset
.AddNew
.Fields("T_CAR:ACHSÜBERSETZUNG") = Cells(i, 1) 'a
.Fields("T_CAR:BREMSEN_TYP_HINTEN") = Cells(i, 2) 'b
.Fields("T_CAR:BREMSEN_TYP_VORN") = Cells(i, 3) 'c
.Fields("T_CAR:CARID") = Cells(i, 4) 'd
.Fields("T_CAR:CLK_NR") = Cells(i, 5) 'e
.Fields("T_CAR:FAHRGESTELLNUMMER") = Cells(i, 6) 'f
.Fields("T_CAR:FAHRZEUGNAME") = Cells(i, 7) 'g
.Fields("T_CAR:FAHRZEUG_ABGEGANGEN") = Cells(i, 8) 'h
.Fields("T_CAR:FAHRZEUG_AN_TANKSTELLE") = Cells(i, 9) 'i
.Fields("T_CAR:FARBE") = Cells(i, 10) 'j
.Fields("T_CAR:FARBE_TEXT") = Cells(i, 11) 'k
.Fields("T_CAR:FAHRZEUG_TYP2") = Cells(i, 12) 'l
.Fields("T_CAR:FELGENTYP") = Cells(i, 13) 'M
.Fields("T_CAR:FZG_ZUGELASSEN") = Cells(i, 14) 'n
.Fields("T_CAR:GENERATOR_HERSTELLER") = Cells(i, 15) 'O
.Fields("T_CAR:GETRIEBE") = Cells(i, 16) 'P
.Fields("T_CAR:GETRIEBEBEZEICHNUNG") = Cells(i, 17) 'Q
.Fields("T_CAR:KFZ_KENNZEICHEN") = Cells(i, 18) 'R
.Fields("T_CAR:KRAFTSTOFFART") = Cells(i, 19) 'S
.Fields("T_CAR:LENKUNG") = Cells(i, 20) 'T
.Fields("T_CAR:LENKUNG_HERSTELLER") = Cells(i, 21) 'U
.Fields("T_CAR:MODELL") = Cells(i, 22) 'V
.Fields("T_CAR:MODELL_JAHR") = Cells(i, 23) 'W
.Fields("T_CAR:MOTOR") = Cells(i, 24) 'X
.Fields("T_CAR:POLSTER") = Cells(i, 25) 'Y
.Fields("T_CAR:POLSTER_TEXT") = Cells(i, 26) 'Z
.Fields("T_CAR:REIFEN_GRÖSSE") = Cells(i, 27) 'AA
.Fields("T_CAR:TYP") = Cells(i, 28) 'AB
.Fields("T_CAR:NAVIGATION_SYSTEM") = Cells(i, 29) 'AC
.Fields("T_CAR:RADIO") = Cells(i, 30) 'AD
.Fields("T_CAR:SCHIEBEDACH") = Cells(i, 31) 'AE
.Fields("T_CAR:ZUGVORRICHTUNG") = Cells(i, 32) 'AF
.Fields("T_CAR:GESCHWINDIGKEITSREGLER") = Cells(i, 33) 'AG
.Fields("T_CAR:KLIMAANLAGE") = Cells(i, 34) 'AH

.Update
End With
Next i
' Tabelle "ET_CAR" schließen
ADODBRecordset.Close

'Aktivieren des Sheets ET_CAR=============================================
Workbooks("Tabelle_Minisuch.xlsm").Worksheets("ET_CODE").Activate
' Tabelle "ET_CODE" ansprechen in der DB
ADODBRecordset.Open "ET_CODE", _
ADODBConnection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

' Daten löschen
Do While Not Recordset.EOF
For k = 0 To lngAnzahl
Cells(i, k + 1).Value = Recordset(k)
Next
i = i + 1
Recordset.MoveNext
Loop

' Daten übertragen
For i = 1 To ActiveSheet.UsedRange.Rows.Count
With ADODBRecordset
.AddNew
.Fields("T_GMUD_CODE:AKTIV") = Cells(i, 1)
.Fields("T_GMUD_CODE:GMUD_CODE") = Cells(i, 2)
.Fields("T_GMUD_CODE:GMUD_CODE_KLARTEXT") = Cells(i, 3)

'.Fields("OrtID") = i

.Update
End With
Next i
' Tabelle "ET_CODE" schließen
ADODBRecordset.Close

'Aktivieren des Sheets ET_OPTIONS=====================================
Workbooks("Tabelle_Minisuch.xlsm").Worksheets("ET_OPTIONS").Activate
' Tabelle "ET_OPTIONS" ansprechen in der DB
ADODBRecordset.Open "ET_OPTIONS", _
ADODBConnection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

' Daten löschen
Do While Not Recordset.EOF
For k = 0 To lngAnzahl
Cells(i, k + 1).Value = Recordset(k)
Next
i = i + 1
Recordset.MoveNext
Loop



' Daten übertragen
For i = 1 To ActiveSheet.UsedRange.Rows.Count
With ADODBRecordset
.AddNew
.Fields("T_CAR:CARID") = Cells(i, 1)
.Fields("T_EXTRAS:GMUD_CODE") = Cells(i, 2)


'.Fields("OrtID") = i

.Update
End With
Next i
' Tabelle "ET_OPTIONS" schließen
ADODBRecordset.Close

'Aktivieren des Sheets ET_Abg_in_Abt=====================================
Workbooks("Tabelle_Minisuch.xlsm").Worksheets("ET_Abg_in_Abt").Activate
' Tabelle "ET_Abg_in_Abt" ansprechen in der DB
ADODBRecordset.Open "ET_Abg_in_Abt", _
ADODBConnection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

' Daten löschen
Do While Not Recordset.EOF
For k = 0 To lngAnzahl
Cells(i, k + 1).Value = Recordset(k)
Next
i = i + 1
Recordset.MoveNext
Loop

' Daten übertragen
For i = 1 To ActiveSheet.UsedRange.Rows.Count
With ADODBRecordset
.AddNew
.Fields("T_ABGABE:CARID") = Cells(i, 1)
.Fields("T_ABGABE:ABTEILUNG") = Cells(i, 2)
.Fields("T_ABGABE*biggrin.gif*ATUM_VON") = Cells(i, 3) 'c
.Fields("T_ABGABE*biggrin.gif*ATUM_BIS") = Cells(i, 4) 'd
.Fields("T_CAR:FAHRZEUG_ABGEGANGEN") = Cells(i, 5) 'e


.Update
End With
Next i
' Tabelle "ET_Abg_in_Abt" schließen
ADODBRecordset.Close

'Aktivieren des Sheets ET_Verlei=====================================
Workbooks("Tabelle_Minisuch.xlsm").Worksheets("ET_Verlei").Activate
' Tabelle "ET_Verlei" ansprechen in der DB
ADODBRecordset.Open "ET_Verlei", _
ADODBConnection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

' Daten löschen
Do While Not Recordset.EOF
For k = 0 To lngAnzahl
Cells(i, k + 1).Value = Recordset(k)
Next
i = i + 1
Recordset.MoveNext
Loop

' Daten übertragen
For i = 1 To ActiveSheet.UsedRange.Rows.Count
With ADODBRecordset
.AddNew
.Fields("T_PLAN:CARID") = Cells(i, 1)
.Fields("T_PLAN:STATUS") = Cells(i, 2)
.Fields("T_PLAN:BEMERKUNG") = Cells(i, 3) 'c
.Fields("T_PLAN*biggrin.gif*ATUM_VON") = Cells(i, 4) 'd
.Fields("T_PLAN*biggrin.gif*ATUM_BIS") = Cells(i, 5) 'e
.Fields("T_PLAN:ENTWICKLUNG_TEXT") = Cells(i, 6) 'f
.Fields("T_CAR:FAHRZEUG_ABGEGANGEN") = Cells(i, 7) 'g

.Update
End With
Next i
' Tabelle "ET_Verlei" schließen
ADODBRecordset.Close





' Verbindung zur Datenbank trennen
ADODBConnection.Close

MsgBox "Die Daten wurden erfolgreich übertragen."
End Sub


Gruß
JBO73

 

Laufzeitfehler , Objekt erforderlich.

Lautet der Fehler.

 

(1) Wenn Du Dein Recordset unter dem Namen ADODBRecordset deklarierst, musst Du diese Bezeichnung auch durchgängig so benutzen, etwas wie das folgende geht dann nicht:
Code:

(2) Die Anweisungen unter Daten löschen sieht bei mir aber anders aus.

(3) Ich setzte DAO ein, Du hier ADODB. Das sind verschiedene Objekte. Somit lassen sich Anweisungen nicht einfach mischen, auch wenn vieles ähnlich ist.

 

Wie hätte ich es denn machen sollen?
Sorry ich da völlig neu!

Vielen Dank für eure Hilfe.

Gruß

JBO73