1
Ik heb een probleem met de melding "Kan de lege rij niet invoegen. Er moet minimaal één kolomwaarde zijn ingesteld" in de volgende code :
De eerste kolommen worden weggeschreven in de DB. Een volgende rij aanmaken met .addnew lukt niet.
Code:
Function Postprocessor()
If rs_MySQL_01.State <> adStateClosed Then Call rs_MySQL_01.Close
Set rs_MySQL_01 = Nothing
If rs_MySQL_02.State <> adStateClosed Then Call rs_MySQL_02.Close
Set rs_MySQL_02 = Nothing
If rs_MSExcel_01.State <> adStateClosed Then Call rs_MSExcel_01.Close
Set rs_MSExcel_01 = Nothing
If rs_MSExcel_02.State <> adStateClosed Then Call rs_MSExcel_02.Close
Set rs_MSExcel_02 = Nothing
' DB Project DATA
strSQL_02 = "SELECT * FROM " & Setvalue(LCase(Form_100.LBLImport.Caption))
rs_MySQL_02.Open strSQL_02, cnn_MySQL_02, adOpenDynamic, adLockOptimistic
TmpInput_1 = rs_MySQL_02.RecordCount
Debug.Print "DB Project DATA: " & TmpInput_1
' Import Bestand DATA
strXLS_02 = "SELECT * FROM [" & Form_100.CMBSheets.Text & "]"
rs_MSExcel_02.Open strXLS_02, cnn_MSExcel_02, adOpenStatic, adLockPessimistic
TmpInput_2 = rs_MSExcel_02.RecordCount
Debug.Print "Import bestand DATA: " & TmpInput_2
' Import Bestand HEADERS
Set rs_MSExcel_01 = cnn_MSExcel_01.OpenSchema(adSchemaColumns, Array(Empty, Empty, Form_100.CMBSheets.Text, Empty))
TmpInput_3 = rs_MSExcel_01.RecordCount
TmpInput_4 = rs_MSExcel_01.Fields.Count
Debug.Print "Import bestand HEADERS: " & TmpInput_3 & " - " & TmpInput_4
' Postprocessing
rs_MSExcel_02.AbsolutePosition = 2
Do While Not rs_MSExcel_02.EOF
rs_MySQL_02.AddNew
Do While Not rs_MSExcel_01.EOF
strSQL_01 = "SELECT * FROM postprocessor WHERE post_field = '" & LCase(rs_MSExcel_01.Fields.Item("COLUMN_NAME").Value) & "' AND post_name = '" & Form_100.LBLImport.Caption & "'"
Set rs_MySQL_01 = cnn_MySQL_01.Execute(strSQL_01)
TmpInput = Val(rs_MSExcel_01.Fields("ORDINAL_POSITION").Value) - 1
'Debug.Print rs_MySQL_01.Fields.Item("Synkrino_field").Value
'Debug.Print LCase(rs_MSExcel_01.Fields.Item("COLUMN_NAME").Value)
If IsNull(rs_MSExcel_02.Fields(TmpInput).Value) Or (rs_MSExcel_02.Fields(TmpInput).Value) = "" Then
TempInput = "-"
Else
TempInput = rs_MSExcel_02.Fields(TmpInput).Value
End If
'Debug.Print TempInput
rs_MySQL_02.Fields(Setvalue(rs_MySQL_01!synkrino_field)) = TempInput
rs_MySQL_02.Update
rs_MSExcel_01.MoveNext
rs_MySQL_01.MoveNext
Loop
rs_MSExcel_02.MoveNext
Loop
rs_MySQL_02.Close
rs_MSExcel_02.Close
rs_MSExcel_01.Close
rs_MySQL_01.Close
End Function
