MS Access insert into SQL Server error - ODBC -- call failed - invalid object name

[malibu65k]

I keep getting errors connecting to the SQL Server from MS Access. I can view the tables, run queries and other insert queries work. This one isn't working... I am connected! The object name is correct - "dbo_T_IMPORT_FDDR_DISCREP"

Insert statement in query object <--- Append query

INSERT INTO dbo_TIMC_DISCREPANCY_INFO ( REQ_NO, WBS_PHASE_ID, PHASE_ID, TRADE_ID, ZONE_ID, AREA_ID, MAL_ID, DISCR_TYPE_ID, CORRTYPE_ID, COND_ID, SEV_ID, LEVEL_ID, ACTION_ID, CL_ID, DISP_ID, WKCTR_ID, NDI_METHOD_ID, WUC_ID, DISCREP_NO, DISCREPANCY_DETAIL, SL, BL, WL, SL_START, BL_START, WL_START, SL_END, BL_END, WL_END, LENGTH, WIDTH, DEPTH, PUB1, TECH, PUB2, EMP_STAMP, ACT_LBR_COST, MRPII_WO_OPN, MRPII_WO, MRPII_PART_NUMBER, MRPII_PART_NOMEN, MATERIAL_COST, DISCREPANCY_DETAIL_ORG )
SELECT dbo_T_IMPORT_FDDR_DISCREP.REQ_NO, dbo_T_IMPORT_FDDR_DISCREP.WBS_PHASE_ID, dbo_T_IMPORT_FDDR_DISCREP.PHASE_ID, dbo_T_IMPORT_FDDR_DISCREP.TRADE_ID, dbo_T_IMPORT_FDDR_DISCREP.ZONE_ID, dbo_T_IMPORT_FDDR_DISCREP.AREA_ID, dbo_T_IMPORT_FDDR_DISCREP.MAL_ID, dbo_T_IMPORT_FDDR_DISCREP.DISCR_TYPE_ID, dbo_T_IMPORT_FDDR_DISCREP.CORRTYPE_ID, dbo_T_IMPORT_FDDR_DISCREP.COND_ID, dbo_T_IMPORT_FDDR_DISCREP.SEV_ID, dbo_T_IMPORT_FDDR_DISCREP.LEVEL_ID, dbo_T_IMPORT_FDDR_DISCREP.ACTION_ID, dbo_T_IMPORT_FDDR_DISCREP.CL_ID, dbo_T_IMPORT_FDDR_DISCREP.DISP_ID, dbo_T_IMPORT_FDDR_DISCREP.WKCTR_ID, dbo_T_IMPORT_FDDR_DISCREP.NDI_METHOD_ID, dbo_T_IMPORT_FDDR_DISCREP.WUC_ID, dbo_T_IMPORT_FDDR_DISCREP.DISCREP_NO, dbo_T_IMPORT_FDDR_DISCREP.DISCREPANCY_DETAIL, dbo_T_IMPORT_FDDR_DISCREP.SL, dbo_T_IMPORT_FDDR_DISCREP.BL, dbo_T_IMPORT_FDDR_DISCREP.WL, dbo_T_IMPORT_FDDR_DISCREP.SL_START, dbo_T_IMPORT_FDDR_DISCREP.BL_START, dbo_T_IMPORT_FDDR_DISCREP.WL_START, dbo_T_IMPORT_FDDR_DISCREP.SL_END, dbo_T_IMPORT_FDDR_DISCREP.BL_END, dbo_T_IMPORT_FDDR_DISCREP.WL_END, dbo_T_IMPORT_FDDR_DISCREP.LENGTH, dbo_T_IMPORT_FDDR_DISCREP.WIDTH, dbo_T_IMPORT_FDDR_DISCREP.DEPTH, dbo_T_IMPORT_FDDR_DISCREP.PUB1, dbo_T_IMPORT_FDDR_DISCREP.TECH, dbo_T_IMPORT_FDDR_DISCREP.PUB2, dbo_T_IMPORT_FDDR_DISCREP.EMP_STAMP, dbo_T_IMPORT_FDDR_DISCREP.ACT_LBR_COST, dbo_T_IMPORT_FDDR_DISCREP.MRPII_WO_OPN, dbo_T_IMPORT_FDDR_DISCREP.MRPII_WO, dbo_T_IMPORT_FDDR_DISCREP.MRPII_PART_NUMBER, dbo_T_IMPORT_FDDR_DISCREP.MRPII_PART_NOMEN, dbo_T_IMPORT_FDDR_DISCREP.MATERIAL_COST, dbo_T_IMPORT_FDDR_DISCREP.DISCREPANCY_DETAIL_ORG
FROM dbo_T_IMPORT_FDDR_DISCREP;


Thank you!

 

[dhookom]

It looks like both tables are on SQL Server so I would probably do this with a pass-through query. There is no reason to have Access/JET do this.

Also, it does look like your table might not be normalized but I'm not sure.

Duane
Hook'D on Access
MS Access MVP

 

[malibu65k]

LOL!! You are corrrect about the normalization, all the tables in the db need to be normalized but it isn't my db. It was created by another contracted company who lost there contract and now I have to fix 100 or so problems the customer is having with it but I can't change anything. I'm only allowed to band-aid it. Anyway, I tried the pass-through query and I get a different error. I think it was a connect error. Why? I can't seem to figure out because I'm connected, I can view the tables and run queries. Do I need to configure something differently or should I just be able to save it as a pass-through query and run it?

 

[GHolden]

Hi, probably doesn't matter but LENGTH is a reserved word so you may need to [] it?

There are two ways to write error-free programs; only the third one works.

 

[malibu65k]

GHolden ,

Now I remember the error I was getting with the pass-through query, your post reminded me. It was not entering a record due to nulls, Microsoft Access can't append all the records in the append query. How do I get around that? There are a lot of nulls.

 

[dhookom]

Change the table to allow NULLS or change the NULL values to acceptable values.

Duane
Hook'D on Access
MS Access MVP

 

[malibu65k]

I found the field that was causing the error. The field wasn't in the list to be inserted but it's a required field, so I added it. Now it inserts a record.

 

[malibu65k]

I found the field that was causing the error. The field wasn't in the list to be inserted but it's a required field, so I added it. Now it inserts a record.