Excel still open after Query retrieve from Access

Hi,

I've had issues with excel remaining open after an access query has been run. This relates to thread http://www.excelforum.com/excel-prog...=1#post2040499

Briefly, I have a spreadsheet which retrieves data from an access query, which in turn pulls parameters from a linked table which is a named range in the original spreadsheet. This is intended to allow users to open the spreadsheet, enter parameters, and retrieve queried data from our AS-400 system.

After closing excel an instance of excel remains open in task manager, and I occasionally seem to have problems rerunning with new parameters (I need to close and reopen the spreadsheet to rerun the retrieve). I have almost come to the conclusion that access needs code to close the link to excel's parameters once the query is run - preventing the second instance of excel remaining open.

The query in access is a Union query to combine each state, but here is a cutdown version of the SQL for New South Wales:

SELECT SOH_Div_Count_match.[Division(s) #], SOH_Reg_Count_match.[Region(s) #], [Product Location file - NSW]![PRDLOC] AS [Loc#], (Trim([Locations - NSW]![DESC])) AS [Branch Name], Trim([Product Conversion Table - NSW]![ALTNO]) AS [Product #], Trim([Product - NSW]![PRDDES]) AS [Product Name], [Product Location file - NSW]![COST2] AS [Average Unit Cost (C2)], [Product - NSW]![WEIGHT] AS [Unit Weight], [Product - NSW]![VOLUME] AS [Unit Volume], [Product Location file - NSW]![SOHLST] AS [SOH Last Stocktake], [Product Location file - NSW]![SOHSM] AS [SOH Start of Month], [Product Location file - NSW]![SOHSW] AS [SOH Start of Week], [Product Location file - NSW]![SOHTM] AS [SOH This Morning], [Product Location file - NSW]![SOHNOW] AS [Available SOH]
FROM (((((([Product Location file - NSW] INNER JOIN [Product Conversion Table - NSW] ON [Product Location file - NSW].PRDNO = [Product Conversion Table - NSW].CSSKEY) LEFT JOIN (SOH_Div_Count_match LEFT JOIN Parameter_Div ON SOH_Div_Count_match.[Division(s) #] = Parameter_Div.[Division(s) #]) ON [Product Location file - NSW].PLGL1 = SOH_Div_Count_match.[Division(s) #]) LEFT JOIN [Product - NSW] ON [Product Location file - NSW].PRDNO = [Product - NSW].PRDNO) INNER JOIN [Locations - NSW] ON [Product Location file - NSW].PRDLOC = [Locations - NSW].PRDLOC) INNER JOIN Locations ON [Locations - NSW].PRDLOC = Locations.Location) LEFT JOIN SOH_Reg_Count_match ON Locations.Region = SOH_Reg_Count_match.[Region(s) #]) LEFT JOIN Parameter_Reg ON SOH_Reg_Count_match.[Region(s) #] = Parameter_Reg.[Region(s) #]
GROUP BY SOH_Div_Count_match.[Division(s) #], SOH_Reg_Count_match.[Region(s) #], [Product Location file - NSW]![PRDLOC], (Trim([Locations - NSW]![DESC])), Trim([Product Conversion Table - NSW]![ALTNO]), Trim([Product - NSW]![PRDDES]), [Product Location file - NSW]![COST2], [Product - NSW]![WEIGHT], [Product - NSW]![VOLUME], [Product Location file - NSW]![SOHLST], [Product Location file - NSW]![SOHSM], [Product Location file - NSW]![SOHSW], [Product Location file - NSW]![SOHTM], [Product Location file - NSW]![SOHNOW], IIf([CountOfDivision(s) #]=0,True,IIf([Ref_Div].[Division(s) #]=[Parameter_Div].[Division(s) #],True,False)), IIf([CountOfRegion(s) #]=0,True,IIf([Ref_Reg].[Region(s) #]=[Parameter_Reg].[Region(s) #],True,False))
HAVING ((([Product Location file - NSW]![SOHNOW])<>0) AND ((IIf([CountOfDivision(s) #]=0,True,IIf([Ref_Div].[Division(s) #]=[Parameter_Div].[Division(s) #],True,False)))=True) AND ((IIf([CountOfRegion(s) #]=0,True,IIf([Ref_Reg].[Region(s) #]=[Parameter_Reg].[Region(s) #],True,False)))=True));

The "Parameter_Div" and "Parameter_Reg" tables are the linked tables from the excel file for the parameters. The "SOH_Div_Count_match" and "Reg_Div_Count_match" are queries that check against "Ref_Div" and "Ref_Reg" tables (also in the excel file) to check if the count of items in the parameters list is zero - basically so that if nothing is selected in the parameters then the query runs for everything.

Any ideas on code to close the second instance of excel? If so, will this close all instances of excel, or just the links to the parameters?

Hans

I'm not entirely sure I follow the process you're going through but the following may be of interest: http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/