linked worksheet connection error

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Hi i am getting this error when running an append query

"The connection for viewing your linked Microsoft Excel worksheet
was lost."

The thing i don't get is I can view the query but when I try and
append it doesn't work. The append query is off of a selct query which
is off the linked table. the selcet query works fine. Also, i have
other queries linked to the same file thatt are working fine. Does
anyone have any idea?
 
pat67 said:
Hi i am getting this error when running an append query

"The connection for viewing your linked Microsoft Excel worksheet
was lost."

The thing i don't get is I can view the query but when I try and
append it doesn't work. The append query is off of a selct query which
is off the linked table. the selcet query works fine. Also, i have
other queries linked to the same file thatt are working fine. Does
anyone have any idea?
Click to expand...

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.
 
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.
Click to expand...

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;
 
pat67 said:
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."
Click to expand...

When? During the import process? Or after you ran the import and tried
selecting records from the imported data?
I've never seen that error message. Have you tried googling it?
What versions of Excel and Access are you using?
 
Back
Top