Append query error

[Jerry]

I'm trying to append some records into an empty table that doesn't have a
primary key or
indexes and am getting the message:

"Microsoft Access set 0 fields(s) to Null due to a type conversion failure,
and it didn't add 1268
record(s) to the table due to key violations, 0 records(s0 due to lock
violations, and 0 record(s)
due to validation rule violations."

I don't see any duplicate records in the table when I run the view.

Any ideas why this message is appearing even when I don't have
indexes/primary keys associated
with the table being appended to?

Thanks,

Jerry

 

[John Vinson]

I'm trying to append some records into an empty table that doesn't have a
primary key or
indexes and am getting the message:

"Microsoft Access set 0 fields(s) to Null due to a type conversion failure,
and it didn't add 1268
record(s) to the table due to key violations, 0 records(s0 due to lock
violations, and 0 record(s)
due to validation rule violations."

I don't see any duplicate records in the table when I run the view.

Any ideas why this message is appearing even when I don't have
indexes/primary keys associated
with the table being appended to?

Thanks,

Jerry

Do you have any Relationships defined for this table? Perhaps you
could post the SQL of the append query; it might not be doing just
what you expect.

John W. Vinson[MVP]

 

[John Spencer (MVP)]

Is the table linked to any other tables? Is relational integrity set up? If
so, if the other table is the "one" table does it have the values in it that
would be linked to the table you are creating?

 

[Jerry]

I should note that the table being inserted into is a linked table in a
Sybase database but
the table isn't related to any other tables. The SQL is a little long but
fairly straightforward -
here it is:

INSERT INTO dbo_ADV_WORK_ADDRESS ( header, id_number, person_or_org,
stop_date, xsequence, type_code, status_code, zipcode, postnet_zip,
preferred_indicator, addr_control, street_1, street_2, street_3, street_4,
city, state, county, country, area_code, xnumber, exten, unlist_ind,
unlist_date, fax_area_code, fax_number, mail_ret_ind, nbr_mail_returned,
mail_ret_date, tracer_sent_date, employment_stop_date, employment_sequence,
person_responsible, start_date, source_code, xcomment, phone_date_modified,
added, modified, operator, owner, care_of, company_title, company_name_1,
company_name_2, email_address, old_phone_status, phone_status,
phone_changed_date, pk, fk, fktype, fkname, emp_title, foreign_phone,
foreign_fax, zip_suffix, change_source_code, src_id_number, src_id_name,
pref_mail_name, label1, label2, label3, label4, label5, label6, label7,
label8, start_mm, start_dd, start_yyyy, stop_mm, stop_dd, stop_yyyy,
business_ind, employment_link, address_mod_date, retrieved, set_mod_date,
t_source_id, t_resp_id, echo_zipcode, url_type )
SELECT "0000000114" & [UCSB04LC_NCOA_Changes_Only].[STRSEQUENCE] &
Format([UCSB04LC_NCOA_Changes_Only].[ID_NUMBER],String(30,"0")) & "M" &
String(66," ") AS Expr1, Ucsb04LC_NCOA_Changes_Only.ID_NUMBER, "P" AS
Expr81, "" AS Expr82, Ucsb04LC_NCOA_Changes_Only.XSEQUENCE,
IIf([dbo_address].[addr_type_code]<>"O",IIf([dbo_address].[addr_type_code]<>
"F",IIf([dbo_address].[addr_type_code]<>"M",IIf([dbo_address].[addr_type_cod
e]<>"X","Z","V"),"P"),"T"),"Z") AS Expr2, "P" AS Expr7,
IIf(IsNull([zipcode]),"",[zipcode]) AS Expr69,
IIf(IsNull([postnet_zip]),"",[postnet_zip]) AS Expr51, "N" AS Expr8, "" AS
Expr9, IIf(IsNull([street1]),"",[street1]) AS Expr50,
IIf(IsNull([street2]),"",[street2]) AS Expr44,
IIf(IsNull([street3]),"",[street3]) AS Expr43,
IIf(IsNull([foreign_cityzip]),"",[foreign_cityzip]) AS Expr41,
IIf(IsNull([dbo_address].[city]),"",[dbo_address].[city]) AS Expr40,
IIf(IsNull([state_code]),"",[state_code]) AS Expr39,
IIf(IsNull([county_code]),"",[county_code]) AS Expr38,
IIf(IsNull([country_code]),"",[country_code]) AS Expr37,
IIf(IsNull([phone_area_code]),"",[phone_area_code]) AS Expr36,
IIf(IsNull([phone_number]),"",[phone_number]) AS Expr32,
IIf(IsNull([phone_extension]),"",[phone_extension]) AS Expr31,
IIf(IsNull([phone_unlisted_ind]),"",[phone_unlisted_ind]) AS Expr30,
IIf(IsNull([phone_unlisted_date]),Date(),[phone_unlisted_date]) AS Expr29,
IIf(IsNull([fax_area_code]),"",[fax_area_code]) AS Expr28,
IIf(IsNull([fax_number]),"",[fax_number]) AS Expr24,
IIf(IsNull([mail_returned_code]),"",[mail_returned_code]) AS Expr23,
IIf(IsNull([mail_returned_nbr]),0,[mail_returned_nbr]) AS Expr80,
IIf(IsNull([mail_returned_date]),Date(),[mail_returned_date]) AS Expr22,
IIf(IsNull([tracer_sent_date]),Date(),[tracer_sent_date]) AS Expr21, "" AS
Expr26, 0 AS Expr27,
IIf(IsNull([person_resp_id_number]),"",[person_resp_id_number]) AS Expr20,
IIf(IsNull([start_dt]),"",[start_dt]) AS Expr19, "NCP" AS Expr3, "Moved to
Past by NCOA process " & " - " & Format(Date(),"mmmm yyyy") AS Expr4,
IIf(IsNull([phone_change_date]),Date(),[phone_change_date]) AS Expr18,
IIf(IsNull([date_added]),Date(),[date_added]) AS Expr17, Date() AS Expr33,
"JBaltes" AS Expr34, "" AS Expr35, IIf(IsNull([care_of]),"",[care_of]) AS
Expr16, IIf(IsNull([business_title]),"",[business_title]) AS Expr15,
IIf(IsNull([company_name_1]),"",[company_name_1]) AS Expr14,
IIf(IsNull([company_name_2]),"",[company_name_2]) AS Expr13,
IIf(IsNull([email_address]),"",[email_address]) AS Expr12, "" AS Expr42,
IIf(IsNull([phone_status]),"",[phone_status]) AS Expr11,
IIf(IsNull([phone_change_date]),Date(),[phone_change_date]) AS Expr10, "" AS
Expr45, "" AS Expr46, "" AS Expr47, "" AS Expr48, "" AS Expr49,
IIf(IsNull([foreign_phone]),"",[foreign_phone]) AS Expr6,
IIf(IsNull([foreign_fax]),"",[foreign_fax]) AS Expr5, "" AS Expr52, "" AS
Expr53, "" AS Expr54, "" AS Expr55, "" AS Expr56, "" AS Expr57, "" AS
Expr58, "" AS Expr59, "" AS Expr60, "" AS Expr61, "" AS Expr62, "" AS
Expr63, "" AS Expr64, DatePart("m",Date()) AS Expr65, DatePart("d",Date())
AS Expr66, DatePart("yyyy",Date()) AS Expr67, "" AS Expr25, "" AS Expr68, ""
AS Expr70, "" AS Expr71, "" AS Expr72, Date() AS Expr73, Date() AS Expr74,
"" AS Expr75, "" AS Expr76, "" AS Expr77, "" AS Expr78, "" AS Expr79
FROM Ucsb04LC_NCOA_Changes_Only INNER JOIN dbo_address ON
(Ucsb04LC_NCOA_Changes_Only.SEQUENCE = dbo_address.sequence) AND
(Ucsb04LC_NCOA_Changes_Only.ID_NUMBER = dbo_address.id_number)
WHERE (((Ucsb04LC_NCOA_Changes_Only.ADDR_TYPE_CODE)<>"H" And
(Ucsb04LC_NCOA_Changes_Only.ADDR_TYPE_CODE)<>"B"))
WITH OWNERACCESS OPTION;

Jerry

 

[John Vinson]

I should note that the table being inserted into is a linked table in a
Sybase database

Then I suspect that Sybase is rejecting the additions, or that there's
a problem with the ODBC link. Can you update this table manually from
the datasheet?

John W. Vinson[MVP]

 

[Jerry]

When I try to update a records from the datasheet, I get a message at the
bottom of the window that states "This Recordset is not updateable".

 

[John Vinson]

When I try to update a records from the datasheet, I get a message at the
bottom of the window that states "This Recordset is not updateable".

Is there a Primary Key on the Sybase table, and have you informed
Access (during the ODBC link process) which field is the PK? This will
be essential to get updatability (at least that's the case for MySQL
and some other backends, I don't have Sybase available to check).

John W. Vinson[MVP]