version=1.4.1
vdate=03.09.2019
fname=udf_persist.bas
ns=%NAMESPACE%
fpath=/vba/access/functions
====== [VBA][Access] persist() ======
//Eine Funktion für ein einfacher INSERT INTO...ON DUPLICATE UPDATE//
==Version %%version%% - %%vdate%%==
Die Funktion eignet sich gut, um schnell eine kleine Änderung (Add /Update) auf eine Tabelle anhand des %%PrimaryKey%% auszuführen.
Die Funktion führt ein Insert aus, sofern der Primary-Key keine übereinstimmung gefunden hat. Ansonsten wird ein Update ausgeführt.
{{%%fname%%|Download %%fname%% (V-%%version%%)}}
===== Definition =====
id = persist(table, feld-1, value-1 [,feld-2, value-2...[,feld-n, value-n]])
id = persist(table, array(feld1 [,feld2...[,feld#]]), array(value1 [,value2]...[,value#]]))
id = persist(table, dictionary)
id = persist(table, string)
Public Function persist( _
ByVal iTableName As String, _
ParamArray iExpressions() As Variant _
) As Variant
***iTableName** Name der Tabelle
***iExpressions()** Immer abwechselnd Feldname, Value
***return** %%PrimaryKey%%
===== Beispiele =====
>Für die Auswertungen verwende ich [[vba:access:functions:printrs]] und [[vba:functions:print_r:index]].
Ich habe mal die folgenden 2 Tabellen (Felder des %%PrimaryKey%% sind mit * markiert)
^ TBL_EXCHANGE ^^^
^ CCY_FROM* ^ CCY_TO* ^ EXCHANGE_VALUE ^
| CHF | EUR | 1.2 |
| CHF | USD | 1.01 |
^ TBL_USER ^^^
^ ID* ^ VORNAME ^ NACHNAME ^
| 1 | Hans | Müller |
| 2 | Susanne | Keller |
| 3 | Kurt | Sutter |
| 4 | Miriam | Hablützel |
==== Beispiele zum Verhalten der Funktion ====
=== Manipulation ohne AutoInkrement Wert ===
Public Sub testPersist()
printRs CurrentDb.OpenRecordset("tbl_exchange")
'Kurs CHF-GPB mit 2.1 eintragen
print_r persist("tbl_exchange", "ccy_from", "CHF", "ccy_to", "GBP", "exchange_value", 2.1)
printRs CurrentDb.OpenRecordset("tbl_exchange")
'Kurs nochmals eintragen, dieses mal mit 2.2. Der Aufruf bleibt gleich.
'Das wird den alten Kurs überschreiben, da die Kombination CHF-GBP bereits vorhanden ist
print_r persist("tbl_exchange", "ccy_from", "CHF", "ccy_to", "GBP", "exchange_value", 2.2)
printRs CurrentDb.OpenRecordset("tbl_exchange")
End Sub
Und das ist die Ausgabe. Man sieht, wie zuerst die Kombination CHF-GBP hinzugefügt wird und mit dem zweiten Aufruf von persist() verändert wird.
| CCY_FROM | CCY_TO | EXCHANGE_VALUE |
|----------|--------|----------------|
| CHF | EUR | 1.2 |
| CHF | USD | 1.01 |
64
| CCY_FROM | CCY_TO | EXCHANGE_VALUE |
|----------|--------|----------------|
| CHF | EUR | 1.2 |
| CHF | USD | 1.01 |
| CHF | GBP | 2.1 |
48
| CCY_FROM | CCY_TO | EXCHANGE_VALUE |
|----------|--------|----------------|
| CHF | EUR | 1.2 |
| CHF | USD | 1.01 |
| CHF | GBP | 2.2 |
=== Manipulation mit Autoinkrement Key ===
Public Sub testPersistUser()
printRs CurrentDb.OpenRecordset("tbl_users")
'Frau Keller heiratet und wechselt den Namen auf Meier
print_r persist("tbl_users", "id", "2", "nachname", "Meier")
printRs CurrentDb.OpenRecordset("tbl_users")
'Eine Frau Ruth Peter wird eingetragen. ID wird automatisch erstellt
print_r persist("tbl_users", "vorname", "Ruth", "nachname", "Peter")
printRs CurrentDb.OpenRecordset("tbl_users")
End Sub| ID | VORNAME | NACHNAME |
|----|---------|-----------|
| 1 | Hans | Müller |
| 2 | Susanne | Keller |
| 3 | Kurt | Sutter |
| 4 | Miriam | Hablützel |
48
| ID | VORNAME | NACHNAME |
|----|---------|-----------|
| 1 | Hans | Müller |
| 2 | Susanne | Meier |
| 3 | Kurt | Sutter |
| 4 | Miriam | Hablützel |
64
| ID | VORNAME | NACHNAME |
|----|---------|-----------|
| 1 | Hans | Müller |
| 2 | Susanne | Meier |
| 3 | Kurt | Sutter |
| 4 | Miriam | Hablützel |
| 5 | Ruth | Peter |
==== Beispiele zu den verschiedenen Anwendungsformaen ====
Die Folgenden Beispiele bewirken alle dasselbe Resultat. Ein Datensatz mit einem Namen und einer externen System-ID wird abgefüllt und kriegt eine neue ID.
Danach wird dieser Record um das Geburtstdatum ergänzt.
Am Ende sieht dann die Tabelle so aus
| ID | USER NAME | BIRTHDAY | SYSTEM-ID |
|----|------------|------------|-----------|
| 1 | Stefan Erb | 01.06.1972 | 123 |
=== String-Liste ===
'Neuen Datensatz erstellen
Dim newId As Long
newId = persist("T_USERS_INFO", "user name", "Stefan Erb", "system-id", 123)
'Datensatz anpassen
persist "t_users_info", "id", newId, "birthday", #6-1-1972#
=== 2 Arrays (Feld-Array & Value-Array) ===
'Neuen Datensatz erstellen
Dim newId As Long
newId = persist("T_USERS_INFO", array("user name", "system-id"), array("Stefan Erb", 123))
'Datensatz anpassen, das Geburtsdatum ergänzen
persist "t_users_info", array("id", "birthday"), array(newId, #6-1-1972#)
=== Dictionary ===
'Neuen Datensatz erstellen
Dim dict As New dictionary
dict.add "user name", "Stefan Erb"
dict.add "system-id", 123
Dim newId As Long
newId = persist("T_USERS_INFO", dict)
'Datensatz anpassen
dict.RemoveAll
dict.add "id", newId
dict.add "birthday", #6/1/1972#
persist "t_users_info", dict
=== Set-String ===
'Neuen Datensatz erstellen
'Anstelle von "" im String kann auch ' verwendet werden
Dim newId As Long
newId = (persist "T_USERS_INFO", "[user name]=""Stefan Erb"", [system-id] = 123")
'Datensatz anpassen
persist "t_users_info", "id=" & newId & ", birthday = #6-1-1972#"
===== Code =====