Search code examples
rdataframerodbc

Insert error : from R to Access DB

Using the below code to insert data from R to Access DB 

sql <- paste0("INSERT INTO test(test1,test2,test3) 
          VALUES('",df1$test1,"','",df1$test2,"','",df1$test2,"',)")
appendRecords <- lapply(sql, function(x) sqlQuery(channel, x))

Column Test1 has text data that contain special character like (' " ,) due that I am getting the following error 

[1] "42000 -3100 [Microsoft][ODBC Microsoft Access Driver] Syntax error 
(missing operator) in query expression '\\\\.\\ROOT\\XXXX' \\n  \\n...''."

Data in column (a'\\.\ROOT\XXXX' \n \n...''.)

How to fix that issue??

Solution

  • Unable to find a permanent solution. so i have made temporary solution, Hope this helps all.

      df1$test1 = gsub("'", "", as.character(df1$test1)) # this remove '.

    After that Inserting it into DB.