Access VBA error in inserting long text into table

Question

In Access I have 2 tables, table_A and table_B. In col2 from table_A, I have an R function as cell value.

mdPatternChart<-function (x, Str_PathFile) 
{
  if (!(is.matrix(x) || is.data.frame(x))) 
    stop("Data should be a matrix or dataframe")
  if (ncol(x) < 2) 
    stop("Data should have at least two columns")
  R <- is.na(x)
  nmis <- colSums(R)
  R <- matrix(R[, order(nmis)], dim(x))
  pat <- apply(R, 1, function(x) paste(as.numeric(x), collapse = ""))
  sortR <- matrix(R[order(pat), ], dim(x))

  if (nrow(x) == 1) {
    mpat <- is.na(x)
  } else {
    mpat <- sortR[!duplicated(sortR), ]
  }

  if (all(!is.na(x))) {    cat(" /\\     /\\\n{  `---'  }\n{  O   O  }\n==>  V <==")
    cat("  No need for mice. This data set is completely observed.\n")
    cat(" \\  \\|/  /\n  `-----'\n\n")
    mpat <- t(as.matrix(mpat, byrow = TRUE))
    rownames(mpat) <- table(pat)
  } else {
    if (is.null(dim(mpat))) {
      mpat <- t(as.matrix(mpat))
    }
    rownames(mpat) <- table(pat)
  }
  r <- cbind(abs(mpat - 1), rowSums(mpat))
  r <- rbind(r, c(nmis[order(nmis)], sum(nmis)))

  png(file=paste(Str_PathFile,".png",sep=""),bg="transparent")
    plot.new()
    if (is.null(dim(sortR[!duplicated(sortR), ]))) {
      R <- t(as.matrix(r[1:nrow(r) - 1, 1:ncol(r) - 1]))
    } else {
      if (is.null(dim(R))) {
        R <- t(as.matrix(R))
      }
      R <- r[1:nrow(r) - 1, 1:ncol(r) - 1]
    }
    par(mar = rep(0, 4))
    plot.window(xlim = c(-1, ncol(R) + 1), ylim = c(-1, nrow(R) + 
                                                      1), asp = 1)
    M <- cbind(c(row(R)), c(col(R))) - 1
    shade <- ifelse(R[nrow(R):1, ], mdc(1), mdc(2))
    rect(M[, 2], M[, 1], M[, 2] + 1, M[, 1] + 1, col = shade)
    adj = c(0, 0.5)
    srt = 90
    for (i in 1:ncol(R)) {
      text(i - 0.5, nrow(R) + 0.3, colnames(r)[i], adj = adj, 
           srt = srt)
      text(i - 0.5, -0.3, nmis[order(nmis)][i])
    }
    for (i in 1:nrow(R)) {
      text(ncol(R) + 0.3, i - 0.5, r[(nrow(r) - 1):1, ncol(r)][i], 
           adj = 0)
      text(-0.3, i - 0.5, rownames(r)[(nrow(r) - 1):1][i], 
           adj = 1)
    }
    text(ncol(R) + 0.3, -0.3, r[nrow(r), ncol(r)])
    dev.off()
}

Now I would like to insert this into col2 of table_B. Col2 from both tables are memo. It works as

CurrentDb.Execute "insert into Table_B (Col1,Col2) select Col1,Col2 from Table_A"

But it does not work if I use DAO.recordset as below.

CurrentDb.Execute "insert into Table_B (Co1,Col2) values (2,'" & Rs_TableA.Fields("Col2") & "')"

And it gave a run-time error 3075 saying something is wrong with the syntax. I replaced ! and " in the function but it did not work. I also tried by saving its value in a string variable before inserting and it did not work either. As I need to loop through table_A, Can anyone help? Thanks!

Why do you need to loop through recordset? Why not use the SELECT subquery version? — June7, Mar 16 '19 at 07:04

June7 · Answer 1 · 2019-03-16T16:28:22.427

The function text contains apostrophes and quote characters. These characters have special meaning in SQL statements. The SELECT subquery won't have an issue but the constructed SQL pulling value from recordset is trying to process them as special characters, not as just simple text. This causes the compiled statement to be nonsense to the SQL engine. Review How do I escape a single quote in SQL Server?.

Options for handling:

Replace(Replace([fieldname], "'", "''"), Chr(34), Chr(34) & Chr(34))
Open a source recordset and a target recordset, loop through source and use AddNew and Update to write records to target
Maybe the SELECT subquery version will actually serve requirement, and if the ID should be supplied dynamically by textbox:
CurrentDb.Execute "INSERT INTO Table_B (Col1,Col2) SELECT " & Me.tbxID & " As C1, Col2 FROM Table_A"

Also, there are 2 slanted apostrophes where I think there should be normal apostrophes.

Access VBA error in inserting long text into table

1 Answers1