Dreaded "multi-step operation" error...

[Maury Markowitz]

I decided to use temp tables to implement a feature in my app, and I've found
that under Access 2000 it failes with the "a multi-step operation has
generated errors" error.

Looking into it, I find it fails every time at the same point in this code:

fieldcount = 0
While fieldcount < rstOrders.Fields.count
rstTempOrders.Fields(rstOrders.Fields(fieldcount).Name) =
rstOrders.Fields(fieldcount)
fieldcount = fieldcount + 1
Wend

I always dies when trying to copy field 31, "bookingNotes", which is a Text
field.

Any ideas? I can't find anything on google.

Maury

 

[Dirk Goldgar]

I decided to use temp tables to implement a feature in my app, and
I've found that under Access 2000 it failes with the "a multi-step
operation has generated errors" error.

Looking into it, I find it fails every time at the same point in this
code:

fieldcount = 0
While fieldcount < rstOrders.Fields.count
rstTempOrders.Fields(rstOrders.Fields(fieldcount).Name) =
rstOrders.Fields(fieldcount)
fieldcount = fieldcount + 1
Wend

I always dies when trying to copy field 31, "bookingNotes", which is
a Text field.

Any ideas? I can't find anything on google.

The first thing to check, naturally, is that the field is actually named
"bookingNotes" in rstTempOrders. Another possibility is that the field
as defined in the source table for rstTempOrders has some validation
rule or NOT NULL constraint on it that prevents the value from the
current rstOrders record from being saved in it.

Are you copying all fields from all records into the temp table? If so,
it would be more efficient to execute a single append query, from the
source table to the temp table, rather than looping through recordsets.

 

[Maury Markowitz]

The first thing to check, naturally, is that the field is actually named
"bookingNotes" in rstTempOrders.

The problem was the data type. Access can't do = asignments on text fields.
Sigh. Changing it to a varchar fixed the problem, although I don't like
inflating my tables that way.

Are you copying all fields from all records into the temp table? If so,
it would be more efficient to execute a single append query, from the
source table to the temp table, rather than looping through recordsets.

I thought about this, and maybe I'll do it at some point, but my concern is
that if we change the DDL in the future, like we did in this case, we have to
remember to change the procs. This is always true, of course, but it's all to
easy to forget in a DB -- there's no "compile everything" command that
catches these errors.

Maury

 

[Dirk Goldgar]

The problem was the data type. Access can't do = asignments on text
fields. Sigh. Changing it to a varchar fixed the problem, although I
don't like inflating my tables that way.

Could you explain the problem in a bit more detail? I'm always
suspicious of statements of the "Access can't do x" nature.

I thought about this, and maybe I'll do it at some point, but my
concern is that if we change the DDL in the future, like we did in
this case, we have to remember to change the procs. This is always
true, of course, but it's all to easy to forget in a DB -- there's no
"compile everything" command that catches these errors.

Could be you can build the necessary statements on the fly, such that
they will automatically adapt to your DDL changes. On the other hand,
it may not be worth it. I don't have enough info to say.

 

[Maury Markowitz]

:

Sorry, I didn't see your post until today.

Could you explain the problem in a bit more detail? I'm always
suspicious of statements of the "Access can't do x" nature.

You simply can't do an assignment on text fields, access gets confused. I
assume it's because the value is a pointer instead of the data itself, or
something like that, when the data being pointed to is empty.

Maury

 

[Dirk Goldgar]

:

Sorry, I didn't see your post until today.

No problem.

You simply can't do an assignment on text fields, access gets
confused. I assume it's because the value is a pointer instead of the
data itself, or something like that, when the data being pointed to
is empty.

What on earth are you talking about? I've never seen Access get
confused about assigning a value to a text field, if the value to be
assigned is properly defined. Of course, I gather you are working in an
ADP, with which I have only marginal familiarity, but I think I'd have
heard if there was some fundamental problem making assignments to text
fields in an ADP.

You may be talking about some odd circumstance I'm not acquainted with,
or you may be jumping to false conclusions. Why don't you post the code
you are using and tell us what error it is raising and what line raises
the error, and maybe we can figure out what's going on?