CODEWITHSUNDEEP
oracle-databasems-access
bd528
bd528

Reputation: 886

ORA-01461: can bind a LONG value only for insert into a LONG column via Access

I am trying to run the below query in Access on a Oracle database :-

UPDATE tblQuotesNew SET tblQuotesNew.Quote_Status = 'Expired' 
WHERE tblQuotesNew.Quote_Status='In Progress' AND tblQuotesNew.Date_Quote_Sent<DateAdd('m',-1,Date())

The data type of Quote_Status is VARCHAR2 size 255. The data type of Date_Quote_Sent is Date.

I am connecting to the Oracle database using the code below :-

Dim mydb        As DAO.Database
Dim myq         As DAO.QueryDef

connectstring = "ODBC;DSN=Comsales;UID=Comsales;PWD=******;SERVER=PDBREPT"

sqltext = "UPDATE tblQuotesNew SET tblQuotesNew.Quote_Status = 'Expired' WHERE tblQuotesNew.Quote_Status='In Progress' AND tblQuotesNew.Date_Quote_Sent<DateAdd('m',-1,Date());"

myq.ReturnsRecords = False
myq.Connect = connectstring
myq.SQL = sqltext
myq.Execute
myq.Close

When I run this query I get a ORA-01461: can bind a LONG value only for insert into a LONG column error.

Upvotes: 1

Views: 645

Answers (1)

Andre
Andre

Reputation: 27644

You're running an Access query (using Access SQL functions) as a Pass-Through query (by setting connectstring = "ODBC;...).

This won't work. Either use Oracle syntax in a Pass-Through query, or Access syntax in a "regular" Access query.

For the latter, tblQuotesNew must be a linked table, and the query connect string must be empty.

Upvotes: 1

Related Questions