Gabriel, this is one I did some time ago
Option Explicit
Dim GroupStr1,SQLStr, BatchIDStr, CookerStr,RecipeStr, Phases, Times As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Sub Main()
On Error GoTo Err
' This creates a string used for a unique batch ID made up of the Date & time i.e. 190523164533
Times = "'" + Format(Now,"yyyy-mm-dd hh:mm:ss") + "'"
BatchIDStr = "'" + Mid(Times,4,2) + Mid(Times,7,2) + Mid(Times,10,2) + Mid(Times,13,2)+ Mid(Times,16,2)+ Mid(Times,19,2) + "'"
'This sets the field for the cooker number in this case set to 3
CookerStr = "'3'"
'This sets The recipe field in this case Recipe 1
RecipeStr = "'Recipe 1'"
' This gets the phase data
Phases = "'" + CStr(GetVariableValue(CH3_Revorack3_Current_Total_Phases)) + "'"
'Set up the Connection String to Database (note: using A datasource name configured on PC
Conn1.ConnectionString = "dsn=DerbySC;uid='sa';pwd='';"
'This is the SQL string to insert the data into the Database fields
SQLStr = "INSERT INTO CookerBatch(BatchStarted,BatchID,Cooker,Recipe,No_Phases) Values (" & Times & "," & BatchIDStr & "," & CookerStr & "," & RecipeStr & "," & Phases & ")"
'Open the connection
Conn1.Open
'Execute the command
Conn1.Execute SQLStr
' Close the connection
Conn1.Close
'Release the connection & clean up
Set Conn1 =Nothing
Debug.Print "Batch Started on Cooker 3 "
Exit Sub
Err:
Debug.Print Error + ": Failed to Write Cooker 3 Batch Details to Database"
Exit Sub
End Sub
The above creates a new record, below updates the other fields as the batch progresses
SQLStr = "INSERT INTO CookerBatch(BatchStarted,BatchID,Cooker,Recipe,No_Phases) Values (" & Times & "," & BatchIDStr & "," & CookerStr & "," & RecipeStr & "," & Phases & ")"
The pic shows the tables as populated
Also see this link
https://use-the-index-luke.com/
Sorry wrong insert into string had two projects open at same time but gives you an idea