Oracle OLE Error

[Rich Hunsicker]

I have this process that retrieve data from two Oracle tables. Lately, we
have been receiving the OLE error: Multiple-step OLE DB operation generated
errors. Check OLE DB status value, if available. No work was done.

There are two Oracle tables that are processed, the first processes fine,
and the second throws this error (but not all the time). Could it be a data
issue with the information from Oracle? All fields but 2 are defines as
text. Detailed below is the connect string and the processing. When the
process dies, it always dies on the same record which would have me believe
that it is the data issue.

sConnStr = "Provider=MSDAORA;Data Source=XXXX;User
ID=OracleUser;Password=OraPasswd;"
cnxn.Open (sConnStr)

'rs1 is the connected Oracle table; rstConInst is the local Access table

rstConInst.Open TableName, cnn, adOpenKeyset, adLockOptimistic
rs1.MoveFirst
Do While Not rs1.EOF
rstConInst.AddNew
For i = 0 To 13
rstConInst(i) = rs1(i)
Next
rstConInst.Update
rstConInst.MoveNext
rs1.MoveNext
Loop

Thanks for your help,

 

[Alex Dybenko]

Hi,
perhaps there are some restrictions which does not allow to add record, try
to manually enter new record in a table with values you want to copy

You can also link oracle table to access and then run insert (append) query
to copy records

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Rich Hunsicker]

Alex,

Thanks.

I am reading from the Oracle table and inserting the data into an Access
table. Please note, this process has been running for months and has just
recently started to cause an issue. As a work-around, I exported the data
into a text file and manually imported the data and was able to successfully
append it to the Access table in question. There were about 36K rows in the
data, and the database was not at maximum.

 

[Alex Dybenko]

Hi,
i would try the following:
once i get an error - pause a code, then get all rs1(0)-rs1(13) values and
then manually insert into access table. Access will tell you which one is
wrong. perhaps field length was changes in oracle table, or field type, and
you need to update access table same way

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi Rich,

There are two Oracle tables that are processed, the first processes fine,
and the second throws this error (but not all the time). Could it be a data
issue with the information from Oracle? All fields but 2 are defines as
text. Detailed below is the connect string and the processing. When the
process dies, it always dies on the same record which would have me believe
that it is the data issue.

Check the size of the text fields in Oracle, e.g.

SELECT Length(Field1), .., Length(FieldN)
FROM oraTable


mfG
--> stefan <--

 

[Rich Hunsicker]

Thanks.

It is still terminating with the same error. The source field lengths are
well within the range of the maximum field lengths. I am trapping the record
where the process terminates, and it always terminates at the same record. I
have set the maxlocks to 65K, and have changed the receiving table have all
text fields.