msaccess adodb insert statement

Question

I have this code where I get variables from a table and it want to insert them into a new table. I keep getting errors on the insert statement, but I can't seem to figure out why. I only get an error Syntax error in insert into statement, nothing more.

'ADO

Sub ProcessRecords()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim DateTimeData As String
    Dim CallerNumberData As String
    Dim CalledNumberData As String
    
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentDb.Name
    
    strSQL = "SELECT * FROM Clean_Log_Telefooncentrale;"
    Set rs = conn.Execute(strSQL)
    
    Do While Not rs.EOF
        If InStr(rs("Param"), "Date event") > 0 Then
            DateTimeData = rs("ParamData")
        ElseIf InStr(rs("Param"), "Calling party number") > 0 Then
            CallerNumberData = rs("ParamData")
        ElseIf InStr(rs("Param"), "Called party number") > 0 Then
            CalledNumberData = rs("ParamData")
            
        ' Debug output
        Debug.Print "DateTimeData: " & DateTimeData
        Debug.Print "CallerNumberData: " & CallerNumberData
        Debug.Print "CalledNumberData: " & CalledNumberData
            
            ' Insert the data into the new table if all values are present
            If DateTimeData <> "" And CallerNumberData <> "" And CalledNumberData <> "" Then
                conn.Execute "INSERT INTO Tbl_Telefooncentrale (DateTime, CallerNumber, CalledNumber) VALUES ('" & DateTimeData & "', '" & CallerNumberData & "', '" & CalledNumberData & "')"
            End If
            
            ' Reset variables
            DateTimeData = ""
            CallerNumberData = ""
            CalledNumberData = ""
        End If
        rs.MoveNext
    Loop
    
    rs.Close
    conn.Close
    
    Set rs = Nothing
    Set conn = Nothing
End Sub

I tried DAO, but I am having issues with the library, so that is not an option. I would like to keep ADODB, I also tried to put the params seperatly, but I am keep having issues with the insert statement.

Answer 1

DateTime is a reserved word and your logic doesn't seem right, so try:

    Do While Not rs.EOF
        If InStr(rs("Param"), "Date event") > 0 Then
            DateTimeData = rs("ParamData")
        ElseIf InStr(rs("Param"), "Calling party number") > 0 Then
            CallerNumberData = rs("ParamData")
        ElseIf InStr(rs("Param"), "Called party number") > 0 Then
            CalledNumberData = rs("ParamData")
        End If
            
        ' Debug output
        Debug.Print "DateTimeData: " & DateTimeData
        Debug.Print "CallerNumberData: " & CallerNumberData
        Debug.Print "CalledNumberData: " & CalledNumberData
            
        ' Insert the data into the new table if all values are present
        If DateTimeData <> "" And CallerNumberData <> "" And CalledNumberData <> "" Then
            conn.Execute "INSERT INTO Tbl_Telefooncentrale ([DateTime], CallerNumber, CalledNumber) VALUES ('" & DateTimeData & "', '" & CallerNumberData & "', '" & CalledNumberData & "')"
            
            ' Reset variables
            DateTimeData = ""
            CallerNumberData = ""
            CalledNumberData = ""
        End If
        rs.MoveNext
    Loop