It's not clear: are you trying to append to the linked spreadsheet? If so,
due to a court order, MS was forced to make it impossible to update a linked
Excel spreadsheet.
My question would not have arisen if you had showed us the sql of the query
that was failing.
If you are actually appending records to a local Access table by selecting
records from the linked spreadsheet, then I'm stumped. It should work.
Yes I am appending a table in access. So I tried to just import the
table from Excel and got this error. "the search key was not found in
any record." any ideas?
If you want the sql here it is. this is the append qry from the select
qry
INSERT INTO tblExcess_2 ( [Extraction Date], Plnt, Material, [Material
Description], MRPC, Function, [Prd Line], ExcStk, ExcOther, ExcProd,
ExcPO, ExcPR, ExcSub, ExcProj, [ExcStk Value], [ExcProd Value], [ExcPO
Value], [ExcPR Value], [ExcSub Value], [ExcProj Value], [ExcOther
Value] )
SELECT qryExcess_With_Values.[Extraction Date],
qryExcess_With_Values.Plnt, qryExcess_With_Values.Material,
qryExcess_With_Values.[Material Description],
qryExcess_With_Values.MRPC, qryExcess_With_Values.Function,
qryExcess_With_Values.[Prd Line], qryExcess_With_Values.ExcStk,
qryExcess_With_Values.ExcOther, qryExcess_With_Values.ExcProd,
qryExcess_With_Values.ExcPO, qryExcess_With_Values.ExcPR,
qryExcess_With_Values.ExcSub, qryExcess_With_Values.ExcProj,
qryExcess_With_Values.[ExcStk Value], qryExcess_With_Values.[ExcProd
Value], qryExcess_With_Values.[ExcPO Value], qryExcess_With_Values.
[ExcPR Value], qryExcess_With_Values.[ExcSub Value],
qryExcess_With_Values.[ExcProj Value], qryExcess_With_Values.[ExcOther
Value]
FROM qryExcess_With_Values;
this is the select qry from the linked table
SELECT Excess_link.[Extraction Date], Excess_link.Plnt,
Excess_link.Material, Excess_link.[Material Description],
Excess_link.MRPC, Excess_link.Function, Excess_link.[Prd Line],
Excess_link.MTyp, Excess_link.Typ, Excess_link.[Price Unit],
Excess_link.MvAvgPrice, Excess_link.[Std price], Excess_link.SPT,
Excess_link.[Safety Stk], Excess_link.ExcStk, Excess_link.ExcOther,
Excess_link.ExcProd, Excess_link.ExcPO, Excess_link.ExcPR,
Excess_link.ExcSub, Excess_link.ExcProj, Excess_link.Evaluation,
IIf([MvAvgPrice]=0,([Std Price]/[Price Unit]),([MvAvgPrice]/[Price
Unit])) AS [Calculated Price], [ExcStk]*IIf([MvAvgPrice]=0,[Std Price],
[MvAvgPrice])/[Price Unit] AS [ExcStk Value],
[ExcProd]*IIf([MvAvgPrice]=0,[Std Price],[MvAvgPrice])/[Price Unit] AS
[ExcProd Value], [ExcPO]*IIf([MvAvgPrice]=0,[Std Price],[MvAvgPrice])/
[Price Unit] AS [ExcPO Value], [ExcPR]*IIf([MvAvgPrice]=0,[Std Price],
[MvAvgPrice])/[Price Unit] AS [ExcPR Value],
[ExcSub]*IIf([MvAvgPrice]=0,[Std Price],[MvAvgPrice])/[Price Unit] AS
[ExcSub Value], [ExcProj]*IIf([MvAvgPrice]=0,[Std Price],[MvAvgPrice])/
[Price Unit] AS [ExcProj Value], [ExcOther]*IIf([MvAvgPrice]=0,[Std
Price],[MvAvgPrice])/[Price Unit] AS [ExcOther Value]
FROM Excess_link;
