The field is [NOT] too small to accept the amount of data you...

[geolemon]

I'm getting an error, I'm not seeing the cause:

"Run time error '-2147217833(8004 0e57)':
The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data.

[end] [debug] [help]

Debug highlights this segment of code (I just snipped a portion here):

 strProc = "Insert into Parts (MfgPN, Manufacturer, Description)" & _
               "select distinct PurchasedPN, Manufacturer, Description " & _
               "from DBimport where PurchasedPN is not null " & _
               "and PurchasedPN not in (select MfgPN from Parts) " & _
               "group by PurchasedPN, Manufacturer, Description"
 
     'insert into Parts:
     conTAGdb.Execute strProc

The "Execute" line is highlighted as the current step.

I have two VBA scripts that make up this user-operated process:
The first script: User is prompted to naviagate to a specially formatted file that he has prepared for import, and imports it into a DBimport temporary table.
The second script: User is prompted to assign a job number, and the script processes the DBimport table into the tables in the database.

This error is occurring in the second script, which is particularly baffling to me since my DBimport table shares the same column definitions as the columns in the target tables.

It doesn't specifically implicate one particular column here, but there are only three in this operation:
MfgPN - "text" in DBimport, "text" in Parts
Manufacturer - "text" in DBimport, "text" in Parts
Description - "text" in DBimport, "text" in Parts

How, if the data is resident in DBimport, could I throw this error doing an insert of this data into Parts?


Any help is greatly appreciated!!

[ADezii]

Could it be possible that you have a size limitation on the Append Table that is less than the Manufacturing Table, namely:

[Manufacturer] in DBImport [TEXT 50]
[Manufacturer] in Parts [TEXT 25]

[QVeen72]

Hi,

Either of the Fields : MfgPN, Manufacturer, Description..
TextLength Of Destination is smaller than the TextLength/Size of the Source..

REgards
Veena

[ADezii]

Hello geolemon! I've reproduced your problem to the extent that I could, and had no problem with the Append Operation whatsoever, the SQL is sound. I've provided the same functionality, but only DAO code based. Kindly Copy and Paste this code and see if it is functional within your context, then we will proceed from there. Good Luck!

Dim strSQL As String
Dim MyDB As DAO.Database
Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
 
Set MyDB = CurrentDb
 
strSQL = "SELECT DISTINCT PurchasedPN, Manufacturer, Description " & _
         "FROM DBImport " & _
         "WHERE DBImport.PurchasedPN Is Not Null And DBImport.PurchasedPN Not In (Select MfgPN From Parts) " & _
         "GROUP BY PurchasedPN, Manufacturer, Description;"
 
Set rstFrom = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
Set rstTo = MyDB.OpenRecordset("Parts", dbOpenDynaset)
 
With rstFrom
  Do While Not .EOF
    rstTo.AddNew
      rstTo![MfgPN] = ![PurchasedPN]
      rstTo![Manufacturer] = ![Manufacturer]
      rstTo![Description] = ![Description]
    rstTo.Update
    .MoveNext
  Loop
End With
 
rstFrom.Close
rstTo.Close
Set rstFrom = Nothing
Set rstTo = Nothing

P.S. - The code has been thoroughly tested and is fully operational. SQL is not my strong point, so I'll call in some of the 'real' Experts/Moderators.

[Stewart Ross]

Hi Geolemon. Like ADezii's original reply and QVeen's I'd look at the field sizes. Your SQL works perfectly for me too using basic test data in two tables set up as yours are. The only explanation I can come up with for the 'too big' error message is what ADezii and QVeen have already suggested. It certainly appears that one or more of the three fields in one of the rows of DBImport contains data that is longer in size than the corresponding field in Parts can contain.

-Stewart

[ADezii]

1. Try setting the Text Size of the Fields in the Parts Table to {TEXT 255} then do the Append Operation. Let us know what happened.
2. Could it be possible that Unicode Compression is turned ON in one or more Fields in the FROM Table and OFF in the Append Table?
   

   The first script: User is prompted to naviagate to a specially formatted file that he has prepared for import, and imports it into a DBimport temporary table.

3. Is this 'specially formatted file' Fixed Width ASCII, and are these Fields padded?

[NeoPa]

Until the OP returns with a response I will assume this is fully answered.

One point I would make though, is that there hardly seems the need for the GROUP BY clause if the SELECT clause already includes the DISTINCT predicate.

[ADezii]

@NeoPa
Thanks NeoPa, that was going to be another question! (LOL).