Oracle OLE Error

  • Thread starter Thread starter Rich Hunsicker
  • Start date Start date
R

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,
 
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
 
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.
 
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
 
hi Rich,

Rich said:
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.
Click to expand...
Check the size of the text fields in Oracle, e.g.

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


mfG
--> stefan <--
 
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.
 
Back
Top