SQL Fustrations...

[Guest]

Hello,
Major problem, task past due. I have an access 2000 mdb that contains data in a table, I need to get that data into an Access 97 mdb. Table are simliar, a few type converstions are done.
I created a VB app that monitors the 2000 mdb for a new record, then inserts into 97 mdb and deletes from the 2000 mdb.
Simple, right.... WRONG... this is killing me.
Here is the message i am getting
"Error -2147217833 (The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.) in procedure InsertIntoRSC of Module basDBFunctions"
However, this is the weird thing. If I go into Access, and paste the sql statment from the vb program that tried to execute and produced the above error... It works fine?
I know.. this might have something to do with VB, thought I'd ask some the access guru's here, maybe someone has seen this before
Thanks,
Brian Shafer

 

[Douglas J. Steele]

It would help if we knew the table definitions, and the VB code you're
using.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello,
Major problem, task past due. I have an access 2000 mdb that contains

data in a table, I need to get that data into an Access 97 mdb. Table are
simliar, a few type converstions are done.

I created a VB app that monitors the 2000 mdb for a new record, then

inserts into 97 mdb and deletes from the 2000 mdb.

Simple, right.... WRONG... this is killing me.
Here is the message i am getting
"Error -2147217833 (The field is too small to accept the amount of data

you attempted to add. Try inserting or pasting less data.) in procedure
InsertIntoRSC of Module basDBFunctions"

However, this is the weird thing. If I go into Access, and paste the sql

statment from the vb program that tried to execute and produced the above
error... It works fine?

I know.. this might have something to do with VB, thought I'd ask some the

access guru's here, maybe someone has seen this before

 

[Guest]

Hello Doug,
Wow, that might be somewhat overwhelming.... some of the sql statements pass and some don't... which leads me to think that a number field with integer, double, byte, and single may be causing the problem.
I'll have to look and compare the two records, one that works and one that doesn't, and see what I can find.
Thanks for the help.... Hopfully you not be hearing from me again on this one.... if you still want to look at the table definitions and the vb code, I would be happy to email it do you.
Thanks,
Brian

 

[GVaught]

In my past experience with this message it is because there is a disconnect
between the fields you are passing from one table to the next. The clue is
in your error message "The field is too small to accept the amount of data
you attempted to add. Try inserting or pasting less data.) " . Example:
your 2000 table has one or more fields that the 97 table doesn't have or
vice versa. These should match. If you are passing 23 fields to 97, the 97
table should have those same 23 fields and same datatype/size. In some cases
it is okay that the 97 db have other fields, these should just remain blank.

Also, check your code to make sure that however you are passing one field to
the other field exists. Example: I had a table that passed the field
PromiseTime from a web database table to a local clients database. I forgot
to write the code to reference the passing of this field to the client's
table.

 

[John Vinson]

On Thu, 10 Jun 2004 12:33:02 -0700, Brian Shafer <Brian

"Error -2147217833 (The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.) in procedure InsertIntoRSC of Module basDBFunctions"

Another way this can happen is if you've got a Lookup field, and are
attempting to put the looked-up text value into the numeric key field.

 

[Birk Binnard]

A totally alternate approach that I used a couple of times was to simply
export/import everything using a text file as the intermediate. It's a
simple minded method, but pretty easy to do.