Append Query - 18 feilds to external database

  • Thread starter Thread starter Gravey
  • Start date Start date
G

Gravey

I wish to append all records from a linked table to an external
database. I have used both the query design grid and sql statements and
all I get is a error message 'can't append records due to validation
rule violations'

INSERT INTO SFTABLE IN 'S:\dept_warehouse\scans\scans.mdb'
SELECT SFTable.*
FROM SFTable;

If I include all feild names in the sql statement, the same result. If
I import the source table instead of it being linked, I get same, If I
shorten the feild names both source and destination tables I get same
(thinking character limitations in sql statement?)

INSERT INTO SFTable ( RECORDTYPE, FROMLOC, TOLOCATION, MATERIAL, BATCH,
SERIALNUM, QUANTITY, WEIGHT, DATESTAMP, [TIMESTAMP], DELIVERY, SORTKEY,
FILENAME, STATUS, PHYSICALOC, ORDERNUM, LINEITEM, STOCKIND ) IN
's:\dept_warehouse\scans\scans.mdb'
SELECT SFTable.RECORDTYPE, SFTable.FROMLOC, SFTable.TOLOCATION,
SFTable.MATERIAL, SFTable.BATCH, SFTable.SERIALNUM, SFTable.QUANTITY,
SFTable.WEIGHT, SFTable.DATESTAMP, SFTable.TIMESTAMP, SFTable.DELIVERY,
SFTable.SORTKEY, SFTable.FILENAME, SFTable.STATUS, SFTable.PHYSICALOC,
SFTable.ORDERNUM, SFTable.LINEITEM, SFTable.STOCKIND
FROM SFTable;

But if I delete 8 feilds to total of ten it works.

INSERT INTO SFTable ( RECORDTYPE, FROMLOC, TOLOCATION, MATERIAL, BATCH,
SERIALNUM, QUANTITY, WEIGHT, DATESTAMP, [TIMESTAMP] ) IN
's:\dept_warehouse\scans\scans.mdb'
SELECT SFTable.RECORDTYPE, SFTable.FROMLOC, SFTable.TOLOCATION,
SFTable.MATERIAL, SFTable.BATCH, SFTable.SERIALNUM, SFTable.QUANTITY,
SFTable.WEIGHT, SFTable.DATESTAMP, SFTable.TIMESTAMP
FROM SFTable;

Is there a way to append all feilds?
And why does it put brackets around the timestamp, all feilds are text
except quantity.


+----------------------------------------------------------------+
| Attachment filename: can\'t append records.gif |
|Download attachment: http://www.MSAccessForum.com.com/forums/attachment.php?postid=159755|
+----------------------------------------------------------------+
 
Either there's some difference between the two tables in one (or more) of
those fields you removed, or else one or more of them are foreign keys
pointing to other tables, and the database to which you're inserting the
records doesn't have the required data in the other tables.

And it's probably putting the [] around TIMESTAMP because that's a reserved
word (and shouldn't be used for your own purposes).
 
Thanks for the reply, The tables I knew were the same as I copied the
orignal to make the storage one to which I was appending the records. I
built a new table from the same feild names manually thinking about the
strange/hidden key setup, as this database is from a satelite forms
system hot syncing a palm unit so who knows what happens behind the
scenes. The manually made table was copied, and I could append between
them no problems. I looked more closly at the feild settings in the
orignal table and they had 'allow zero length' set to no, but the data
had some feilds with zero length! Anyway with the storage table set to
allow zero length = yes, it works.

Ya

Thanks v much, I spent 3 day working this out going thru help etc.
Gravey
 
Back
Top