英文:
SQL Query works from Access but not from R using RJDBC package
问题
我整天都在尝试解决这个问题..
我有一个Access数据库，我想使用JDBC连接从R中更新一些数据。
如果我在Access中的SQL查询编辑器中执行它，UPDATE查询就能完美运行，但是在R中却不起作用，我不知道为什么。这是我的代码：
library(devtools)
library(DBI)
library(dplyr)
library(rJava)
library(RJDBC)
setwd("mypath")
access_driver_file_name <- "Access_JDBC42.jar"
access_driver_class <- "com.hxtt.sql.access.AccessDriver"
access_data_file <- "my_database.accdb" #在这里放入数据库的名称
access_jdbc_url <- paste("jdbc:access:/", getwd(), "\\", access_data_file, sep="")
access_jdbc_driver <- JDBC(driverClass=access_driver_class, classPath=paste(getwd(), "\\", access_driver_file_name, sep=""))
con <- dbConnect(access_jdbc_driver, access_jdbc_url)
dbListTables(con)#这个完美运行
dbSendUpdate(con, 'UPDATE TableName SET TableName.Field = "Casa"
WHERE (([TableName].[Field]="Oficina"));')
如果我复制UPDATE TableName SET TableName.Field = "Casa" WHERE (([TableName].[Field]="Oficina"));并在Access中粘贴它，查询就能运行，但是R返回:
Error in .local(conn, statement, ...) :
execute JDBC update query failed in dbSendUpdate
JDBC ERROR: Syntax error: Stopped parse at .(id21)
Statement: UPDATE TableName SET TableName.Field = "Casa"
WHERE (([TableName].[Field]="Oficina"));
我尝试过使用dbSendQuery和dbGetQuery，但总是得到相同的错误。
有人能帮帮我吗？
先谢谢了！
英文:
I have been all day trying to solve this problem..
I have an Access Database and I want to UPDATE some data from R using JDBC connection.
The UPDATE query works perfectly if I execute it from SQL Query editor in Access, but is not working from R and I dont know why. This is my code:
library(devtools)
library(DBI)
library(dplyr)
library(rJava)
library(RJDBC)
setwd("mypath")
access_driver_file_name <- "Access_JDBC42.jar"
access_driver_class <- "com.hxtt.sql.access.AccessDriver"
access_data_file <- "my_database.accdb" #poner aqui el nombre de la base 1
access_jdbc_url <- paste("jdbc:access:/", getwd(), "\\", access_data_file, sep="")
access_jdbc_driver <- JDBC(driverClass=access_driver_class, classPath=paste(getwd(), "\\", access_driver_file_name, sep=""))
con <- dbConnect(access_jdbc_driver, access_jdbc_url)
dbListTables(con)#this work perfectly
dbSendUpdate(con, 'UPDATE TableName SET TableName.Field = "Casa"
WHERE (([TableName].[Field]="Oficina"));')
If I copy UPDATE TableName SET TableName.Field = "Casa"
WHERE (([TableName].[Field]="Oficina")); and paste it in Access the query works, but R returns:
Error in .local(conn, statement, ...) :
execute JDBC update query failed in dbSendUpdate
JDBC ERROR: Syntax error: Stopped parse at .(id21)
Statement: UPDATE TableName SET TableName.Field = "Casa"
WHERE (([TableName].[Field]="Oficina"));
I tried the same with dbSendQuery and with dbGetQuery and I get always the same error.
Can anybody help me?
Thank you in advance!
I tried the same with dbSendQuery and with dbGetQuery and I get always the same error.
答案1
得分: 0
终于找到解决方案。
我更改了与Access连接的包。
现在我正在使用RODBC包。首先，我需要安装ODBC驱动程序到Access，然后在运行此代码并执行我的UPDATE查询时没有问题：
require(RODBC)
channel=odbcConnectAccess2007("my_path/my_data_base.accdb")
sqlQuery(channel, "UPDATE Table_Name SET Table_Name.column = 'Casa' WHERE (([Table_Name].[Column]='Oficina'));")
odbcClose(channel) #this is to desconect the DB
英文:
Finally I find the solution.
I change the package to connect with Access.
Now I using RODBC package. First I need to install ODBC driver to Access, and after I run this code and execute my UPDATE query without problem:
require(RODBC)
channel=odbcConnectAccess2007("my_path/my_data_base.accdb")
sqlQuery(channel, "UPDATE Table_Name SET Table_Name.column = 'Casa'
WHERE (([Table_Name].[Column]='Oficina'));")
odbcClose(channel) #this is to desconect the DB
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论