Multiple-step OLE DB operation generated errors

[Den]

I am trying to use Excel as a point of collecting some information for an
Employee database. The Employees fill in some fields and through VBA code
using ADO I am trying to assign this data to fields in an MS SQL database.

The code I am using will open the connection to the database and retrieve a
record from the database, but it won't allow me to assign values to more
than about 10 fields before I get the error message shown in the subject
line of this posting.

The database is extremely simple, one table with about 90 fields of
information. One field is a numeric Employee ID field and rest are just text
fields.

I have tried a record.update operation after 9 fields to see if it would
let me do another nine fields after that , but that did not work.

Any suggestions??????

Dennis

 

[Alex Ivanov]

Look at the data. Most likely you are trying to insert into a table field
longer string than it can hold or assign a null value to a non-nullable
field.

Alex.

 

[Den]

I tested both of those possibilities, it is not the data length or type. I
also made sure data was in the fields in question, so there is no null
value. Also, it is not related to any certain field, if I comment out the
value assignment just prior to the problem field then the problem field
works fine. If I comment out the problem field, the next field in line
becomes the problem field, even if the data types and lengths are different.
It appears to be related to the total size of all the fields or to the
number of fields being updated.

Appreciate any suggestions??

Dennis

 

[Alex Ivanov]

Try to assign some other value to the problem field manually in debug mode
from the Immediate window like rsTgt!FieldX.Value="xxx". Will it accept such
values?

It may help if you explicitly convert source field data to string before
assigning it to the target field as
rsTgt!FieldX.Value=CStr(rsSrc!FieldX.Value)

Alex.

 

[Den]

Thanks, with your help I solved the problem. I am still not sure why it
caused the error I was receiving but my Excel range name was on the wrong
cell. So I was sending the wrong cells value to the database field. When I
corrected the cell range reference it worked fine. The thing I don't
understand is the incorrect cell still had string data in it of a length
that should have been ok, so it still should have worked??? Anyway, it
works now that I have corrected the cell reference.
Thanks
Dennis