SQL INSERT Will not work - Why ??? 1

[whatisahandle]

Hi,

I am trying to insert rows in a table with the SQL statement at the bottom. Because of the outer joins, some fields are null when no match is found. Because of this, Access keeps giving me a "Record as been deleted" message and no insert is done. However when there are no null value fields, the insert works.

I tried to fix it with this code in the SELECT section, but it does not help:

IIF(isNull([mytable].[myfield]),"",[mytable].[myfield]) AS myfield

Note: My table is a link to a table in SQL Server 7.0 and I am using Access 2000

SQL statement:

INSERT INTO cat_TransPageCat ( noCompagnie, produit_ln, categorie, classe, page, micro_fiche, no_Unique, sequence, code_equivalent, date_revision, image_pang, image_afr )
SELECT [cat_prod_prix].[NoCompagnie], [cat_prod_prix].[produit_ln], IIF(isNull([cat_Idx_sect_prod].[categorie_ln]),"",[cat_Idx_sect_prod].[categorie_ln]) AS categorie_ln, IIF(isNull([cat_det_section].[section_ln]),"",[cat_det_section].[section_ln]) AS section_ln, IIF(isNull([cat_Idx_sect_prod].[page_log]),"",[cat_Idx_sect_prod].[page_log]) AS page_log, ' ' AS micro_fiche, [cat_prod_prix].[No_Unique], [cat_prod_prix].[sequence], IIF(isNull([cat_prod_prix].[code_equivalent]),"",[cat_prod_prix].[code_equivalent]) AS code_equivalent, '' AS date_revision, IIF(isNull([cat_produit].[image_link_an]),"",[cat_produit].[image_link_an]) AS image_link_an, IIF(isNull([cat_produit].

 

[apbamike]

How are the fields in the SQL table defined. Are null perimitted in the fields that you know are null in the update?

 

[whatisahandle]

Yes, the fields are all nullable, except the primary key which is an identity field.