Append query fails

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I have 2 tables eg Table x and Table y, PK on tblX = EnquiryNumber FK
on tbly = EnquiryNumber.

After importing Excel data to a temp table I wish to Append this data
to tblY. Of 20 fields 18 import fine 2 do not. Removing those 2
fields allow the Append to go ahead.
Error message refers to Key violations. There are no links on the 2
problem fields or on any others except PK and FK in the 2 tables.

To try to narrow it down I removed all other fields and attempted to
import to a blank tblY (Structure only) It still failed.

Any ideas?

Thanks

Don
 
Hard to give you specific info because you don't give us much info about the
table setup and fields, but.... the error about "key violations" usually
means that you are trying to write a record where the primary key is not
unique (it already exists in the table) OR where the value of a field that
is Indexed - No Duplicates is the same as a value already in the table for
that field.

Look at the data that you're trying to append, look at the data already in
the table (or being appended to the table), and see if you have these
duplication issues.
 
Thanks for the pointer on this Ken, after further investigation I
turned my attention to the Excel sheet that I import from. The 2 cells
that fail to import have an 'IF' statement that use 1 of 3 options
dependant on the state of previous cells.
Formula is =IF(DBReccData!B2="","",IF(I2="Identified","Estimates","Not
Known"))

The number ( 1 to 3 representing the 3 text options) gets imported,
not hte text as I would have expected.
In itself this would not represent a problem but Access seems to
recognise that this is a value list and the list does not exist in the
imported table, any attempt to change the number even to another
number1 to 3 in Access results on a Not in list message.
A work around is to Copy and Paste - Special - Values only in the
Excel sheet prior to import but as the spreadsheet will be distributed
to several hundred users and many will have macros disabled I can
foresee a problem looming.
Is it possible to instigate the copy and paste special in Excel from
Access? Or do you or any of the group have other ideas?

Bear in mind that the Import to the temp table works and the
subsequent Append to the Sub Table is failing.


Thanks

Don
 
Don said:
I have 2 tables eg Table x and Table y, PK on tblX = EnquiryNumber FK
on tbly = EnquiryNumber.

After importing Excel data to a temp table I wish to Append this data
to tblY. Of 20 fields 18 import fine 2 do not. Removing those 2
fields allow the Append to go ahead.
Error message refers to Key violations. There are no links on the 2
problem fields or on any others except PK and FK in the 2 tables.

To try to narrow it down I removed all other fields and attempted to
import to a blank tblY (Structure only) It still failed.
Click to expand...

Sounds like the FK constraint in Table y is disallowing some inserts
because there is no corresonding value in its PK column/field in Table
x.

--
 
I don't have specific experience with what you're describing, but taking it
at face value, you would need to open the EXCEL file via Automation and
either

(1) read the data cell by cell, row by row, and write it into a
recordset that is based on the destination table.

or

(2) do the copy / paste special via VBA code, then save and close the
EXCEL file, and then continue with the import process.

Have you worked with Automation before?
 
...
I don't have specific experience with what you're describing, but taking it
at face value, you would need to open the EXCEL file via Automation and
either

(1) read the data cell by cell, row by row, and write it into a
recordset that is based on the destination table. <snip>
Click to expand...

Have you tried querying the (closed) workbook to do the import e.g.

INSERT INTO [MyJetTable]
(Col1, Col2)
SELECT MyColA AS Col1, MyColColA AS Col2
FROM [Excel 8.0;database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]

I know for sure when you *query* a workbook you get the calculated
value of a cell formula.

--
 
Back
Top