Zur Sicherung und Weiterverwendung möchte ich 50 Excel-Spalten mit Daten in eine Access-Datenbank mit gleicher Tabelle überführen. Es gibt da ein Schlüssel (ID-Nr.) auf der Spalte "F" (6). Gibt es diesen Schlüssel bzw. Daten bereits in der Access schon, sollten die Änderungen dort überschrieben werden, fehlt der Schlüssel (ID-Nr.) sollte der Datensatz (Excel-Zeile von A-AX) angefügt werden. Ist das mit VBA einfach möglich?
Vielen Dank für Eure wertvollen Vorschläge.
Gruss
Stefan1
Hallo,
du kannst Excel und Access in der Arbeitsweise nicht vergleichen. In deinem Fall müsstest du in Access wohl eine Anfügeabfrage erstellen, wenn ich richtig verstanden habe, was du meinst.
Guten Tag
Vielen Dank. Ich hatte mal folgenden VBA -Code, der gut funktionert hat, doch jetzt möchte ich nicht Zeile um Zeile, sondern einen Datenbereich (z.B. 50 Spalten, 10 Zeilen) als Anfüge-Abfrage (neue anfügen und bereits vorhandene aktualisieren) quasi in einem Guss in das Access (vordefinierte Tabelle mit der gleichen Struktur) transportieren aus einem Excel Tabellenbereich:
Code:
Sub AccessDB(strText As String)
Dim objConnection As ADODB.Connection
Dim objRecSet As ADODB.Recordset
Dim Zeile As Variant
Dim strPfad As String
Dim sBer As String
Dim sTxt As String
Dim i As Integer
On Error GoTo ErrorHandler
strPfad = ThisWorkbook.Path & CONinvNrPfa & CONdbPfad
If Len(strText) > 0 Then
Set objConnection = New ADODB.Connection
Set objRecSet = New ADODB.Recordset
Zeile = Split(strText, CONtz)
'Datenbank öffnen
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source =" & strPfad & CONaccessDB
'---------------------------------------------------------------
'Tabelle öffnen
objRecSet.Open "Tab_Import", _
objConnection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'Daten von Excel nach Access übertragen
With objRecSet
.AddNew
For i = 0 To UBound(Zeile)
sBer = Left(Zeile(i), 7)
If Right(sBer, 3) <> 200 And _
Right(sBer, 3) <> 201 Then
sTxt = Mid(Zeile(i), 9)
If Len(sTxt) > 0 Then _
.Fields(sBer) = Left(sTxt, 50)
'50 derzeitige Beschränkung Access-DB
End If
Next
.Update
End With
'Offene Verbindung schliessen
objRecSet.Close
objConnection.Close
Set objRecSet = Nothing
Set objConnection = Nothing
End If
'---------------------------------------------------------------
'Exit Sub in order to avoid ErrorHandler because all is ok!
Exit Sub
'-------------------------------------------------------------------
ErrorHandler:
MsgBox "Unbekannter Fehler aufgetreten!" & vbCrLf & vbCrLf & _
"Laufzeitfehler Nr. " & Err.Number & vbCrLf & _
"Beschreibung: " & Err.Description & vbCrLf & vbCrLf & _
"Der Vorgang kann nicht ausgeführt werden.", vbCritical
Err.Clear
On Error Resume Next
'objRecSet.Close
objConnection.Close
End Sub
Sorry, kleine Korrektur. Es sind nicht 50 Spalten, sondern lediglich 10 Spalten
Guten Tag
Danke, ja, das mit dem Verknüpfen ist mir bekannt und habe ich auch sogar mit direkter Verknüpfung zur Pivot Tabelle. Aber hier läuft es vorerst anders. Die Daten werden in der Excel-Tabelle ständig aktualisiert und sind nie mehr als 100 Zeilen. Die Access-DB hat hier noch keine Bedeutung und ist lediglich als Archiv gedacht. Deshalb wird die Access-DB-Tabelle immer mehr anwachsen. Es ist also nicht als 1 zu 1 Daten gedacht, sondern als historisches Archiv. Deshalb würde ich es gerne aktualisieren/anfügen können. Es sind nur 10 Spalten an je 100 Zeilen.
Nochmals zurück es müsste etwas in der Art sein wie hier eingestellt. Aber es müssen zwei Dinge erfüllt werden,
1. Anfügen über einen Schlüssel, welche noch nicht enthalten sind
2. überschreiben bzw. Ändern von bereits bestehenden Schlüssel bei geänderten Datensätze
Code:
Option Explicit
Sub AccImport()
Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Users\Public\Database1.accdb"
acc.DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:="tblExcelImport", _
Filename:=Application.ActiveWorkbook.FullName, _
HasFieldNames:=True, _
Range:="Folio_Data_original$A1:B10"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
End Sub
Hallo, :19:
nur mit ".DoCmd.TransferSpreadsheet" wird das nicht klappen. Du könntest aber die Daten mit ".DoCmd.TransferSpreadsheet" in eine temporäre Tabelle laden und dann mit zwei Abfragen (die du natürlich dann auch per VBA starten kannst - ".DoCmd.OpenQuery ("Abfragename")") an die Originale Tabelle anfügen bzw. aktualisieren (Anfügeabfrage und Aktualisierungsabfrage).
Das Ganze bei Bedarf auch von Excel aus gesteuert.
Guten Tag Case
Vielen Dank für Deine Meldung. Wohl genau so, Gibt es ein einfaches Beispiel für 10 Spalten dafür? Das schein ziemlich komplex zu sein. Wird dann diese temporäre Tabelle automatisch wieder gelöscht? Ist es auch möglich mit VBA von Excel aus die Anfügeabfrage und Aktualisierungsabfrage temporär bei Laufzeit zu erstellen und auszuführen ohne das in der Access mehr als die eine Tabelle sein muss?
Gruss
Stefan1
Ich habe ziemlich recherchier und finde eigentlich nur Beispiele für
- Import aus Excel nach Access
- Export aus Access nach Excel
Jedoch kein Beispiel für
- Import aus Access nach Excel
- Export aus Excel nach Access
Es müsste doch irgendwie möglich sein aus Excel eine temporäre Tabelle zu erstellen und dann eine Anfügeabfrage und Änderungen nach Schlüssel durchzuführen?
Hallo, :19:
bin jetzt auf Achse - poste dir am Samstag, oder Sonntag ein Beispiel. :21:
Hallo Case
Das wäre sehr fein. Vielen Dank. Lass Dir ruhig Zeit. Aber ich freue mich auf die Beispiele. Ist sehr interessant.
Gruss
Stefan1