use system2 from R 4.2 to execute cammand in 32-bit R

Question

In older versions of R (e.g., 4.0.5) I could load tables from MS Access using a command such as:

system2(paste0(R.home(), "/bin/i386/Rscript.exe"),script_path)

and the R packages DBI and odbc. The script looks like

library(DBI)
library(odbc)
ch <- dbConnect(odbc(), .connection_string = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=filepath/DB.mdb;')
tab <- dbReadTable(ch, name = 'target_table')
save.image(file = 'out.RData')

And then I can load "out.RData" back into my 64- R session. Obviously, this does not work in R 4.2.X, because there is no longer support for 32-bit. But I thought I could still call the same older 32-bit version by explicitly specifying the filepath, such as

system2(paste0("C:/PROGRA~1/R/R-40~1.5/bin/i386/Rscript.exe"),script_path)

but it generates the following puzzling warnings and errors:

Warning message:
package 'DBI' was built under R version 4.2.3 
Error: package 'odbc' is not installed for 'arch = i386'
In addition: Warning message:
package 'odbc' was built under R version 4.2.3 
Execution halted

What is the reason for these odd messages, and is there a way I can still use the newer R versions to load in MS Access data? Am I stuck using older versions of R? Or are there newer solutions where MS Access plays nicely with 64-bit R?

Answer 1

It seems as if your R-4.0 instance is using the same library path (directory of packages) as R-4.2, which shouldn't happen (per R package preparation), and also shouldn't happen (since you clearly need some packages to remain "frozen" in your 4.0 instance, a state that may not be supportable in 4.2).

Check your .libPaths() in the R-4.0 instance and make sure that it is using a distinct path for its own packages. Since you're on Windows, this should include a directory such as c:/Users/<username>/AppData/Local/R/4.0 (forward or back-slashes) and .../4.2.

I don't have windows/R up atm, but it's likely that system2 is passing along the R_LIBS_USER env-var.

Answer 2

As pointed out, by @r2evans above, the issue was that, even though the script was being executed in an older version of R.script.exe, the library path still pointed to the library for the version of the current R session in which the system2 function was run. I fixed this by adding the following line to the top of my R script (that was executed in the system2 command (as shown here):

".libPaths(paste0('C:/Users/',Sys.getenv('USERNAME'),'/Documents/R/win-library/4.0'))"