Datensatz einer Access-Tabelle kopieren
Häufig hat man die Situation, dass man einen Datensatz in der Access-Datenbank kopieren will wobei die meisten Werte gleich bleiben und sich nur wenige Werte ändern sollen.
Ein einfaches
Insert into tblXXX select * from tblXXX where ID=3
geht in der Regel nicht, weil die Tabelle eine AutoWert-ID enthält und der INSERT dann eine Schlüsselverletzung zur Folge hat.
man muss also alle Feldnamen (Spaltennamen) angeben und bei vielen Feldern ist das eine mühsame Angelegenheit:
Insert into tblXXX (feld1, feld2, feld3, ...) select feld1, feld2, feld3, ... from tblXXX where ID=3
Außerdem soll vielleicht feld3 ersetzt werden durch einen anderen Wert.
Beispielsweise wird ein Artikel-Datensatz in der Tabelle tblArtikel kopiert, wobei sich nur der Artikelname unterscheiden soll und alle anderen Felder gleich bleiben, weil es z.B. der gleiche Artikel in einer anderen Farbe ist:
Insert into tblXXX (feld1, feld2, feld3, ...) select feld1, feld2, 'Neuer Wert' as feld3, ... from tblXXX where ID=3
Funktion zum Erstellen einer Feldliste
Zunächst benötigen wir eine Funktion zum Erstellen einer Liste der Felder einer Tabelle, wobei einzelne felder auch leicht ausgenommen oder ersetzt werden können:
Public Function FieldList(strTable As String, ParamArray varReplace() As Variant) As String
' gibt kommagetrennte Liste von Feldnamen zur Tabelle oder Abfrage zurück
' mit varReplace kann man Felder rausnehmen oder durch "xy as Feldname" erstezen
' varReplace muss die Form Feld_x,Ersetzung_x, feld_y,Ersetzung_y haben
' Ersetzung_x ist NULL, wenn Feld entfallen soll
' Beispiele:
' FieldList(strTable) -> "ID, Nachname, Vorname, ID_Status, Alter"
' FieldList(strTable,ID,Null) -> "Nachname, Vorname, ID_Status, Alter"
' FieldList(strTable,ID,Null,ID_Status,7) -> "Nachname, Vorname, 7 as ID_Status, Alter"
' jede ungerade Position in varReplace ist also Feldname, darauffolgend immer ein konstanter ausdruck
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim arrFields As Variant
Dim strField As String
Dim varReplaceField As Variant
On Error GoTo ErrHandle
Set rs = New ADODB.Recordset
rs.Open "select * from " & strTable & " where 1=0", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
' Alle Felder durchlaufen
For Each fld In rs.Fields
' Ist es ein Feld, das ersetzt oder weggelassen werden soll?
If ArrayContains(varReplace, fld.Name) Then
varReplaceField = varReplace(ArrayPos(varReplace, fld.Name) + 1)
If IsNull(varReplaceField) Then
' Feld soll weggelassen werden
strField = ""
Else
' Feld soll durch Wert ersetzt werden
strField = varReplaceField & " as " & fld.Name
End If
Else
strField = fld.Name
End If
If strField <> "" Then ArrayAdd arrFields, strField
Next
FieldList = Join(arrFields, ", ")
rs.Close
Set rs = Nothing
Exit Function
ErrHandle:
Select Case err
Case Else
MsgBox err.Description
End Select
End Function
Erläuterung:
Verwendete Array-Funktionen
Die Funktionen ArrayContains, ArrayPos und ArrayAdd sind nützliche Array-Funktionen, die in diesem Beitrag zu finden sind:
Nützliche Array-Funktionen
Verweise auf ADO-Bibliothek
Damit das Beispiel funktioniert, muss unter den Projekt-Verweisen die ADO-Bibliothek aktiviert sein:
In der Regel wird die Bibliothek mit der höchsten Version ausgewählt.
Tabellen-Felder durchlaufen
Mit rs.Open ... wird ein ‚leerer‘ Recordset der Tabelle geöffnet um dann mit For Each fld In rs.Fields jede Spalte der Tabelle zu durchlaufen.
Tabellen-Felder erstzen
Mit dem Funktionsparameter varReplace() kann man festlegen, welche Felder ersetzt bzw. ausgenommen werden sollen.
Es muss immer eine gerade Anzahl von Parametern übergeben werden.
Dabei ist Parameter X ein Feldname, Parameter X+1 entweder NULL (Feld wird weggelassen) oder ein beliebiger Wert (Feldwert wird ersetzt).
Beispiel:
Tabelle tblMitarbeiter enthält die Spalten: ID, Nachname, Vorname, ID_Status, Alter
Dann liefert
(1) komplette Feldliste
FieldList(strTable) -> „ID, Nachname, Vorname, ID_Status, Alter“
(2) Feld ID weglassen:
FieldList(strTable,ID,Null) -> „Nachname, Vorname, ID_Status, Alter“
(3) Feld ID wird weggelassen und ID_Status durch „7 as ID_Status“ ersetzen:
FieldList(strTable,ID,Null,ID_Status,7) -> „Nachname, Vorname, 7 as ID_Status, Alter“
Datensatz duplizieren
Mit dieser Funktion lässt sich dann einfach ein Select-Statement erzeugen, mit dem man einen Datensatz klonen kann:
Public Sub CloneRecord()
Dim strSQL As String
Dim strTable As String
Dim strFieldListAll As String
Dim strFieldListInsert As String
strTable = "tblArtikel"
strFieldListAll = FieldList(strTable, "ID", Null)
strFieldListInsert = FieldList(strTable, "ID", Null, "Artikelname", "'Kugelschreiber rot'")
strSQL = "Insert into " & strTable & " (" & strFieldListAll & ") " & _
"select " & strFieldListInsert & " from tblArtikel where ID = 3"
CurrentProject.Connection.Execute strSQL
End Sub
Erläuterung:
Die Tabelle tblArtikel enthalte folgende Felder:
Der Datensatz mit der ID = 3 soll dupliziert werden, wobei sich der Artikelname unterscheiden, nämlich „Kugelschreiber rot“ sein soll.
Mit Hilfe der Feldlisten-Funktion wird folgendes Select-Statement erzeugt:
Insert into tblArtikel (Artikelname, Preis, Einstelldatum, Kategorie, Beschreibung, ImSortiment)
select 'Kugelschreiber schwarz' as Artikelname, Preis, Einstelldatum, Kategorie, Beschreibung, ImSortiment from tblArtikel where ID= 3