Excel nach Access

[Stefan1]

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

[Klaus-Dieter]

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.

[Stefan1]

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

[Case]

Hallo, :19:

also ich mache das gerne über...

   

... direkt in Access.

Da hast du eine permanente Verknüpfung der Tabelle - Änderungen werden gleich übernommen.

Siehe auch: :21:

Importieren von oder Verknüpfen mit Daten in einer Excel-Arbeitsmappe...

[Stefan1]

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

[Case]

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.

[Stefan1]

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

[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?

[Case]

Hallo, :19:

bin jetzt auf Achse - poste dir am Samstag, oder Sonntag ein Beispiel. :21:

[Stefan1]

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