Neuen Datensatz anlegen - sehr tricky

Dom312_01e3cb · 9. November 2019 um 20:09

Hallo liebes Forum.

Info: mit VBA und Datenbanktheorie kenne ich mich gut aus der Zugriff auf Access Abfragen und Tabellen ist noch Neuland.

Ich möchte einen Datensatz in Tab1 anlegen und den Schlüsselwert (bekannt) eintragen. Die Werte der weiteren (8) Felder sind zu diesem Zeitpunkt unbekannt. Sie können ermittelt werden über den Schlüsselwert und den Namen des Feldes.

-> Der Feldname ist als Schlüsselwert in Tab2 abgelegt. Ein Feld in Tab2 enthält eine Formel.
-> Es erfolgt ein Funktionsaufruf mit den Parametern Schlüssel(Tab1) und Formel(Tab2)
-> die Funktion gibt einen String zurück, den ich in das entsprechende Feld in Tab1 schreiben möchte.
-> die Prozedur wiederholt sich für Tab1.Feld 3-9

Ich habe ein Problem, die Referenzen auf Tab1 zu halten. Vieleicht kann mir hier jemand konzeptionell helfen, etwa:

Datensatz anlegen & Schlüssel setzen
tab.columns(x) to tab.column.count durchlaufen
tab.columns(x).name in tab2 suchen
Funktionsparameter setzen & Funktion aufrufen
Ergebnis in tab1 eintragen wo Schlüssel = [bekannt] & Feld = tab.columns(x).name

Welche Methoden setze ich am besten ein (SQL, ADO, DAO)?!

Für gute Antworten spendiere ich über Karneval gerne das ein oder andere Bier Grüße aus dem Rheinland
Dom

Reinhard_Kraasch · 9. November 2019 um 20:10

Würde ich mit entsprechenden Recordset-Operationen lösen. Also im Prinzip halt so etwas (ausserhalb der Formularereignisse, was eigentlich vorzuziehen ist):

Dim RSN as DAO.Recordset, RS As DAO.Recordset
Set RSN = Currentdb.Openrecordset("Tab1",dbopendynaset)
RSN.Addnew
RSN!PrimaryKey = Wasauchimmer
Set RS = Currentdb.Openrecordset("SELECT \* FROM Tab2 WHERE PrimaryKey = " & Wasauchimmer, dbopensnapshot)
If Not RS.EOF then
 RSN!DeinFeld = RS!Deinfeld
End if
RSN.Update

Oder auch innerhalb der Formularereignisse (typischerweise dann im BeforeInsert-Ereignis):

Dim RS As DAO.Recordset
Me!PrimaryKey = Wasauchimmer
Set RS = Currentdb.Openrecordset("SELECT \* FROM Tab2 WHERE PrimaryKey = " & Wasauchimmer, dbopensnapshot)
If Not RS.EOF then
 Me!DeinFeld = RS!Deinfeld
End if

oder wie immer du dir das vorstellst (so ganz werde ich aus deiner Beschreibung nicht schlau).

Gruß aus dem Norden
Reinhard Kraasch

(http://www.dbwiki.de - das Datenbank-Wiki)

Dom312_01e3cb · 9. November 2019 um 20:12

Hallo und vielen Dank,
ich konnte das Problem lösen indem ich die Columns(i).name Eigenschaft auslese. Den Datensatz hänge ich via CurrentDb.Execute strSQL an. strSQL baue ich sukzessive zusammen.

Jetzt ist es so, dass die Ergebniswerte, die in die Tabelle geschrieben werden sollen immer als String zurückgegeben werden. CurrentDb.Execute erzeugt zwar keine Fehlermeldungen. Ergebniswerte werden aber auch nicht in ein Feld geschrieben, wenn dieses nicht als String (also Date oder Double) deklariert ist.

Ich möchte nun von Fall zu Fall entsprechende Umwandlungen vornehmen. Sting -> ?. ? muss ich ermitteln. Welche Eigenschaft des Columns Objekt muss ich auslesen um zu erfahren, welcher Datentyp verlangt wird?

Viele Grüße
Dom

Reinhard_Kraasch · 9. November 2019 um 20:16

Du musst halt die Felddatentypen berücksichtigen:

Dim I as Long, A as Double, S as String, D as Date
Dim DB as DAO.Database
I = 1
A = 2.5
S = "hallo"
D = Dateserial(2000,12,31)
Set DB = Currentdb
DB.Execute "INSERT INTO DeineTabelle " & \_
 "(lngDeinLongfeld, dblDeinDoublefeld, txtDeinTextfeld, dteDeinDatumsfeld)" & \_
 " VALUES (" & I & "," & Str(A) & ",'" & S & "',#" & \_
 Format(D,"yyyy-mm-dd") & "#)"

Wenn man’s ganz sauber machen will, muss man auch noch Nullwerte und mögliche Apostrophe im String berücksichtigen, die letzteren wie folgt:

 " VALUES (" & I & "," & Str(A) & ",'" & Replace(S,"'","''") & "',#" & \_
 Format(D,"yyyy-mm-dd") & "#)"

Gruß aus dem Norden
Reinhard Kraasch

(http://www.dbwiki.de - das Datenbank-Wiki)

Dom312_01e3cb · 9. November 2019 um 20:16

Hi Reinhard,
vielen Dank Du bist echt ne Hilfe. Aber mein Problem geht etwas tiefer. Zu dem Zeitpunkt, zu dem ich den Stringaufbau programmiere, kenne ich weder:

den Namen des DB Feldes
das Format des DB Feldes
die benötigte Formel um den Zielwert zu generieren
noch die Anzahl der Felder in der Zieltabelle

Hier der Code zur Info (sei nicht zu streng, ist in Arbeit
'==============================================================
Sub FillStandingData(strJCFCode As String)
'==============================================================
Dim strSQL As String
Dim strFieldList As String
Dim strValueList As String
Dim strFieldName As String
Dim strFormula As String
Dim strFctValue As String
Dim conn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.table
Dim intF As Integer
Dim länge As Long
Dim v

Set conn = CurrentProject.Connection
Set cat.ActiveConnection = conn
Set tbl = cat.Tables(„tblStandingData“)

strSQL = „INSERT INTO tblStandingData (“
strFieldList = „[JCF Code]“
strValueList = „VALUES (“ & Chr(34) & strJCFCode & Chr(34)

For intF = 0 To tbl.Columns.Count - 1
If tbl.Columns(intF).Name „JCF Code“ Then
strFieldName = tbl.Columns(intF).Name
strFieldList = strFieldList & „, [“ & strFieldName & „]“
strFormula = GetFormula(strFieldName)
strFctValue = SDKLoad(strJCFCode, strFormula, „“)
Select Case tbl.Columns(intF).Type
Case 7
'String -> Date
Case 202
'Alles ok
strValueList = strValueList & ", " & Chr(34) & strFctValue & Chr(34)
länge = Len(strValueList)
End If
Next

Mein Problem war, dass tbl.Columns(intF).Type mir immer 202 (adVarWChar) geliefert hat, auch bei Datumsfeldern (um ehrlich zu sein war ich anfangs noch auf der Suche nach der Type-Eigenschaft . Offenbar lag das an einer fehlerhaften oder fehlenden Catalog-Referenz. Wie Du oben siehst erhalte ich nun für Datumsfelder den Type 7 (adDate).
Die Case-Struktur habe ich zuletzt als Platzhalter eingesetzt. Der Wert strFactorValue ist immer ein String. Jetzt muss ich nur noch in Abhängigkeit von Type eine Umwandlungsfunktion in den benötigten Datentyp ausführen, et voila.

Ich habe an Deinem Beispiel gesehen, dass Du keine Chr(34) bzw. „“ verwendest um das SQL-Statement aufzubauen. Kann ich mir diese also sparen?

schönen Gruß
Dominique

Reinhard_Kraasch · 9. November 2019 um 20:17

Na, dann etwa so:

Dim strSQL As String
Dim strFieldList As String
Dim strValueList As String
Dim strFieldName As String
Dim strFormula As String
Dim strFctValue As String
Dim conn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.table
Dim col as Adox.Column
Dim intF As Integer
Dim länge As Long
Dim v

Set conn = CurrentProject.Connection
Set cat.ActiveConnection = conn
Set tbl = cat.Tables("tblStandingData")

strSQL = "INSERT INTO tblStandingData ("
strFieldList = "[JCF Code]"
strValueList = "VALUES (" & Chr(34) & strJCFCode & Chr(34)

For each col in tbl.Columns
 If col.Name "JCF Code" Then
 strFieldList = strFieldList & ", [" & col.Name & "]"
 strFormula = GetFormula(col.name)
 strFctValue = SDKLoad(strJCFCode, strFormula, "")
 Select Case col.Type
 Case adDate ' date
 strFctValue = "'" & Format(strFctValue,"yyyy-mm-dd") & "'"
 Case adDouble, adSingle, adCurrency ' double, single, currency
 strFctValue = Trim(Str(strFctValue))
 Case adInteger, adTinyint, adBigint ' integer, byte, long
 ' tu nix
 Case adVarWChar, adLongVarWChar ' text, memo
 strFctValue = "'" & Replace(strFctValue,"'","''") & "'"
 Case Else
 MsgBox "Dieser Datentyp fehlt noch: " & col.type
 end select
 end if
Next col

Ich habe anstelle des Anführungszeichens das Apostroph genommen, genauso gut kann man oben überall

' anstelle von: "'" 
' schreiben: """" 
' oder auch: Chr(34)

also z.B.

 strFctValue = """" & Replace(strFctValue,"""","""""") & """"
' oder:
 strFctValue = Chr(34) & Replace(strFctValue,chr(34),chr(34) & Chr(34)) & Chr(34)

Gruß aus dem Norden
Reinhard Kraasch

(http://www.dbwiki.de - das Datenbank-Wiki)