Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1220to1224
1220to1224
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Daten aus Excel in Accesstabelle schreiben

Forumthread: Daten aus Excel in Accesstabelle schreiben

Daten aus Excel in Accesstabelle schreiben
Tim
Hallo zusammen,
ich habe diverse Daten in Excel vorliegen, die ich in form einer *.mdb Tabelle weiterleiten muss. Da sich der Datensatz regelmäßig ändern kann, würde ich gerne dafür ein Makro haben. Ich bin soweit, die *.mdb erstellen zu können und es wird mir darin eine Tabelle mit dem namen Kontakte angelegt. Er will mir die Daten einfach nicht in die Datenbank schreiben. Egal wie ich es versuche, die Datenbank bleibt immer leer. Folgenden Code habe ich :
Verweis auf Microsoft DAO 3.6 Object Library ist gesetzt.
Private Sub CommandButton3_Click()
Dim Datenbank As Database
Dim Datensatz As Recordset
Dim Tabelle As TableDef
Dim Feld1 As Field
Dim Feld2 As Field
Dim Feld3 As Field
Dim x As Integer
Dim y As Integer
Dim Dateiname As String
Const Tabellenname = "Kontakte"
'Erzeugen der Datenbank und Tabelle
Dateiname = InputBox("Bitte Namen eingeben")
Dateiname = ThisWorkbook.Path & "\Kontaktelisten\" & Dateiname & ".mdb"
On Error Resume Next
'Prüfen, ob Datenbank bereits vorhanden
If Dir(Dateiname) = "" Then
Set Datenbank = CreateDatabase(Dateiname, dbLangGeneral, dbEncrypt)     'Datenbank  _
erzeugen
End If
Set Datenbank = OpenDatabase(Dateiname)                                 'Tabelle erzeugen
Set Tabelle = Datenbank.CreateTableDef(Tabellenname)
'Felder erzeugen Die Namen der Felder werden aus den entsprechenden Zellen geholt
With Tabelle
Set Feld1 = .CreateField(Range("A1"), dbText, 200)
Set Feld2 = .CreateField(Range("B1"), dbText, 200)
Set Feld3 = .CreateField(Range("C1"), dbText, 200)
.Fields.Append Feld1
.Fields.Append Feld2
.Fields.Append Feld3
End With
'Tabelle hinzufügen
Datenbank.TableDefs.Append Tabelle
Set Datensatz = Datenbank.OpenRecordset(Tabellenname)
With Datensatz
For x = 2 To 5          'Zeile 2 bis 5
.AddNew
For y = 1 To 5      'Spalte 1 bis 5
.Fields(tabelle11.Cells(1, y)).Value = Tabelle11.Cells(x, y).Text
Next y
'Datensatz updaten
.Update
.Bookmark = .LastModified
Next x
End With
'Datenbank schließen
Datenbank.Close
End Sub
Wenn mir jemand helfen könnte, wäre echt toll.
Gruß
Tim
Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
Daten aus Excel in Accesstabelle schreiben
17.07.2011 12:29:51
Anton
Hallo Tim,
probier's hiermit:
Code:

Private Sub CommandButton3_Click()  
  Dim Datenbank As Database  
  Dim Datensatz As Recordset  
  Dim Tabelle As TableDef  
  Dim Feld1 As Field  
  Dim Feld2 As Field  
  Dim Feld3 As Field  
  Dim x As Integer    
  Dim y As Integer    
  Dim Dateiname As String  
  Const Tabellenname = "Kontakte"
 'Erzeugen der Datenbank und Tabelle
 'Dateiname = InputBox("Bitte Namen eingeben")
  Dateiname = "E:\Temp\Test\test.mdb"
 'Prüfen, ob Datenbank bereits vorhanden
  If Dir(Dateiname) = "" Then  
    Set Datenbank = CreateDatabase(Dateiname, dbLangGeneral, dbEncrypt)    'Datenbank erzeugen
  End If  
  Set Datenbank = OpenDatabase(Dateiname)  
 'Prüfen, ob Tabelle bereits vorhanden
  Set kontakte = Datenbank.TableDefs(Tabellenname)
  If kontakte Is Nothing Then    
   'Tabelle erzeugen
    Set Tabelle = Datenbank.CreateTableDef(Tabellenname)
   'Felder erzeugen Die Namen der Felder werden aus den entsprechenden Zellen geholt
    With Tabelle
      Set Feld1 = .CreateField(Range("A1"), dbText, 200)  
      Set Feld2 = .CreateField(Range("B1"), dbText, 200)  
      Set Feld3 = .CreateField(Range("C1"), dbText, 200)  
      .Fields.Append Feld1
      .Fields.Append Feld2
      .Fields.Append Feld3
    End With  
   'Tabelle hinzufügen
    Datenbank.TableDefs.Append Tabelle
  End If  
  Set Datensatz = Datenbank.OpenRecordset(Tabellenname)  
  With Datensatz
    For x = 2 To 5          'Zeile 2 bis 5
      .AddNew
     'hier ist der Fehler: Tabelle "Kontakte" hat nur drei Felder,
     'du versuchst aber 5 Felder zu befühlen
     'For y = 1 To 5      'Spalte 1 bis 5
      For y = 1 To 3        'Spalte 1 bis 3
        .Fields(Tabelle1.Cells(1, y)).Value = Tabelle1.Cells(x, y).Text
      Next y
     'Datensatz updaten
      .Update
      .Bookmark = .LastModified
    Next x
  End With  
 'Datenbank schließen
  Datenbank.Close
End Sub  


mfg Anton
Anzeige
wie erlaube ich leere Zelleneinträge
17.07.2011 13:21:15
Tim
@ Anton, den Fehler hatte ich noch selber bemerkt, hat aber keine Lösung gebracht. Danke
Ich hab ein wenig rumprobiert und bin dahingehend weitergekommen, das der Fehler wohl darin liegt, dass in der erstellten Accessdatenbank keine Leeren Zelleneinträge in den erzeugten Spalten erlaubt sind.
Folgender Code funktioniert jetzt:
Sub InAccessSchreiben(ende As Integer)
Dim Datenbank As Database
Dim Datensatz As Recordset
Dim Tabelle As TableDef
Dim Feld2 As Field
Dim Feld3 As Field
Dim Feld4 As Field
Dim Feld5 As Field
Dim Feld6 As Field
Dim Feld7 As Field
Dim Feld8 As Field
Dim Feld9 As Field
Dim Feld10 As Field
Dim Feld11 As Field
Dim Feld12 As Field
Dim Feld13 As Field
Dim Feld14 As Field
Dim Feld15 As Field
Dim Feld16 As Field
Dim Feld17 As Field
Dim x As Integer
Dim y As Integer
Dim Dateiname As String
Const Tabellenname = "Kontakte"
'Erzeugen der Datenbank und Tabelle
Dateiname = InputBox("Bitte Listennamen eingeben")
Dateiname = ThisWorkbook.Path & "\Kontaktelisten\" & Dateiname & ".mdb"
If Dir(Dateiname) = "" Then                                                 'Prüfen, ob  _
Datenbank bereits vorhanden
Set Datenbank = CreateDatabase(Dateiname, dbLangGeneral, dbEncrypt)     'Datenbank  _
erzeugen
End If
Set Datenbank = OpenDatabase(Dateiname)                                 'Tabelle erzeugen
Set Tabelle = Datenbank.CreateTableDef(Tabellenname)
'Felder erzeugen Die Namen der Felder werden aus den entsprechenden Zellen geholt
With Tabelle
Set Feld2 = .CreateField(Range("A1"), dbText, 200)
Set Feld3 = .CreateField(Range("B1"), dbText, 200)
Set Feld4 = .CreateField(Range("C1"), dbText, 200)
Set Feld5 = .CreateField(Range("D1"), dbText, 200)
Set Feld6 = .CreateField(Range("E1"), dbText, 200)
Set Feld7 = .CreateField(Range("F1"), dbText, 200)
Set Feld8 = .CreateField(Range("G1"), dbText, 200)
Set Feld9 = .CreateField(Range("H1"), dbText, 200)
Set Feld10 = .CreateField(Range("I1"), dbText, 200)
Set Feld11 = .CreateField(Range("J1"), dbText, 200)
Set Feld12 = .CreateField(Range("K1"), dbText, 200)
Set Feld13 = .CreateField(Range("L1"), dbText, 200)
Set Feld14 = .CreateField(Range("M1"), dbText, 200)
Set Feld15 = .CreateField(Range("N1"), dbText, 200)
Set Feld16 = .CreateField(Range("O1"), dbText, 200)
Set Feld17 = .CreateField(Range("P1"), dbText, 200)
.Fields.Append Feld2
.Fields.Append Feld3
.Fields.Append Feld4
.Fields.Append Feld5
.Fields.Append Feld6
.Fields.Append Feld7
.Fields.Append Feld8
.Fields.Append Feld9
.Fields.Append Feld10
.Fields.Append Feld11
.Fields.Append Feld12
.Fields.Append Feld13
.Fields.Append Feld14
.Fields.Append Feld15
.Fields.Append Feld16
.Fields.Append Feld17
End With
'Tabelle hinzufügen
Datenbank.TableDefs.Append Tabelle
Datenbank.Close
ZweiterVersuch Dateiname, ende
End Sub


Sub ZweiterVersuch(Dateiname As String, ende As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim x As Integer
Dim y As Integer
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & Dateiname & ";"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Kontakte", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
For x = 2 To ende - 1
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
For y = 1 To 16
' Prüfen on Zelle Eintrag besitzt !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
If Tabelle11.Cells(x, y).Text  "" Then
.Fields(Tabelle11.Cells(1, y).Text) = Tabelle11.Cells(x, y).Text
Else
.Fields(Tabelle11.Cells(1, y).Text) = " "
End If
If Err.Number  0 Then MsgBox Err.Description
Next y
.Update ' stores the new record
End With
Next x
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Ich trage also wenn ich eine Leere Zelle hab ein Leerzeichen ein. DAs ist zwar eine Lösung, aber ich würde eigentlich lieber die Eigenschaft der Access-Spalten dahingehen ändern, dass Sie leere Einträge zulassen...
Jemand eine Ahnung ob und wie das geht ?
Danke und Gruß
Tim
Anzeige
wie erlaube ich leere Zelleneinträge
17.07.2011 13:47:50
Anton
Hallo Tim,
schau dir in OH von DAO die AllowZeroLength-Eigenschaft an.
mfg Anton
;
Anzeige
Anzeige

Infobox / Tutorial

Daten aus Excel in eine Access-Tabelle übertragen

Schritt-für-Schritt-Anleitung

  1. Verweis auf Microsoft DAO 3.6 Object Library setzen:

    • Gehe in Excel zu Entwicklertools > Visual Basic.
    • Klicke auf Extras > Verweise und aktiviere Microsoft DAO 3.6 Object Library.

  2. Makro erstellen:

    Private Sub CommandButton3_Click()
   Dim Datenbank As Database
   Dim Datensatz As Recordset
   Dim Tabelle As TableDef
   Dim Feld1 As Field
   Dim Feld2 As Field
   Dim Feld3 As Field
   Dim x As Integer
   Dim y As Integer
   Dim Dateiname As String
   Const Tabellenname = "Kontakte"

   Dateiname = InputBox("Bitte Namen eingeben")
   Dateiname = ThisWorkbook.Path & "\Kontaktelisten\" & Dateiname & ".mdb"
   On Error Resume Next

   If Dir(Dateiname) = "" Then
       Set Datenbank = CreateDatabase(Dateiname, dbLangGeneral, dbEncrypt)
   End If

   Set Datenbank = OpenDatabase(Dateiname)
   Set Tabelle = Datenbank.CreateTableDef(Tabellenname)

   With Tabelle
       Set Feld1 = .CreateField(Range("A1"), dbText, 200)
       Set Feld2 = .CreateField(Range("B1"), dbText, 200)
       Set Feld3 = .CreateField(Range("C1"), dbText, 200)
       .Fields.Append Feld1
       .Fields.Append Feld2
       .Fields.Append Feld3
   End With

   Datenbank.TableDefs.Append Tabelle
   Set Datensatz = Datenbank.OpenRecordset(Tabellenname)

   With Datensatz
       For x = 2 To 5
           .AddNew
           For y = 1 To 3
               .Fields(Tabelle1.Cells(1, y)).Value = Tabelle1.Cells(x, y).Text
           Next y
           .Update
       Next x
   End With
   Datenbank.Close
End Sub

  3. Daten in Access übertragen:

    • Führe das Makro aus, um die Daten in die Access-Datenbank zu schreiben.

Häufige Fehler und Lösungen

  • Datenbank bleibt leer:

    • Überprüfe, ob die Felder der Tabelle in Access mit den Spalten in Excel übereinstimmen. Achte darauf, dass du nicht mehr Felder hinzufügst, als in der Access-Tabelle definiert sind.

  • Leere Zelleneinträge nicht erlaubt:

    • Um leere Einträge zuzulassen, kannst du die AllowZeroLength-Eigenschaft in Access anpassen. Das geht über die Eigenschaften der Tabelle in Access.

  • Fehlermeldung bei AddNew:

    • Stelle sicher, dass die Felder korrekt benannt sind und die Daten in Excel in den richtigen Formaten vorliegen.

Alternative Methoden

  • ADO verwenden: Eine andere Möglichkeit, Daten in Access zu übertragen, ist die Verwendung von ADO (ActiveX Data Objects). Hier ist ein Beispiel:

    Sub InAccessSchreiben(Dateiname As String)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim x As Integer

   Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Dateiname & ";"
   Set rs = New ADODB.Recordset
   rs.Open "Kontakte", cn, adOpenKeyset, adLockOptimistic, adCmdTable

   For x = 2 To 5
       With rs
           .AddNew
           .Fields("Feld1") = Tabelle1.Cells(x, 1).Value
           .Fields("Feld2") = Tabelle1.Cells(x, 2).Value
           .Update
       End With
   Next x

   rs.Close
   cn.Close
End Sub

Praktische Beispiele

  • Erstellen einer Access-Tabelle: Um eine neue Access-Tabelle zu erstellen, kannst du den folgenden Code verwenden:

    Set Tabelle = Datenbank.CreateTableDef("NeuerTabelleName")

  • Hinzufügen von Datensätzen: Um einen Datensatz hinzuzufügen, benutze die AddNew-Methode im Recordset.

Tipps für Profis

  • Datenvalidierung: Stelle sicher, dass die Daten in Excel validiert sind, bevor du sie in Access überträgst. Dies kann durch die Verwendung von Datenüberprüfungen in Excel erfolgen.

  • Automatisierung von Prozessen: Überlege dir, wie du regelmäßig Daten automatisch von Excel nach Access übertragen kannst, z.B. durch das Erstellen eines Zeitplans für das Makro.

FAQ: Häufige Fragen

1. Wie kann ich leere Einträge in Access erlauben? Um leere Einträge in Access zuzulassen, kannst du die AllowZeroLength-Eigenschaft der entsprechenden Felder in der Access-Datenbank ändern.

2. Welche Excel-Version benötige ich für VBA? Du benötigst eine Excel-Version, die VBA unterstützt, z.B. Excel 2007 oder neuer.

3. Wie kann ich sicherstellen, dass meine Daten in der Access-Datenbank korrekt geschrieben werden? Testen deine Makros mit einer kleinen Datenmenge und überprüfe die Access-Datenbank nach jedem Lauf auf die korrekten Einträge.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige