Excel still open after Query

hi Hans,

Can you please post a small example file (without any confidential info etc)?

I suspect that the code may not be releasing or resetting all the variables (but to honest, without seeing the code I'm only guessing...). Also if you are creating an Instance of Excel through code, is the instance Quit once its work is done?

hth
Rob

Hi Rob,

The query in access is a Union query to combine data for every state, so it's a bit much to paste in - however here is the SQL part for NSW:

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.

Here is the relevant part of the retrieve data macro in excel:

'Retrieve Data from AS-400 Database

    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=S:\Merchandise\Procurement BA\Projects\AS-400.mdb;Mode=Sh" _
        , _
        "are Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet" _
        , _
        " OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" _
        , _
        "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy " _
        , _
        "Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
        ), Destination:=Range("a2"))
        .CommandType = xlCmdTable
        .CommandText = Array("SOH - All")
        .Name = "AS-400_SOH"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .SourceDataFile = "S:\Merchandise\Procurement BA\Projects\AS-400.mdb"
        .Refresh BackgroundQuery:=False

I've just done a trial run, all worked fine - but again after closing excel it is still open in task manager. I reopened the file, chose some different parameters, and it appeared to work. Given that, it looks as if the problem can be narrowed down to a) an instance of excel still taking up memory when closed (potential problem for some users as they are using ancient laptops), and b) rerunning for different parameters requires file to be closed and reopened.

Any more details you need let me know.

Thanks

Hans

hi Hans,

I'm out of my depth here (I have only used SQL once - so I have to assume that's right!) & thought I could offer a long shot response if I saw your code, since now one else had offered an answer.
Hopefully someone with more knowledge can provide a succinct answer to your question... Anyone?

Anyway, I've done a bit of reading & found the below pages which may be of use:

Why do you "add a query" rather than have one set up which is refreshed as needed with the different variables?
See the suggestions made in the below thread
http://www.tek-tips.com/viewthread.c...1523888&page=2

Are you creating a new Excel instance as an object at the start of your code?eg

Set objXL = New Excel.Application

If so, do you quit the object at the end of your code & release the memory?
eg

objXL.Application.Quit 
        Set objXL = Nothing

see post 5 of http://www.ozgrid.com/forum/showthread.php?t=52181

Would including the below line of code help?

.MaintainConnection = False

http://www.dbforums.com/microsoft-ex...ables-add.html

Other technical stuff:
http://support.microsoft.com/kb/211931/en-us

hth
Rob

hi Hans,

Originally Posted by Hans_Gruber

... I've had a look through the other links you listed, but I'm not sure if they apply, as I'm not creating a new instance of excel using excel - it's the access query that that links back to the excel parameters that I think are creating a new instance of excel.

Should I be adding in SQL code to the access query to shutdown the other instance of excel (similar to the "objXL.Application.Quit
Set objXL = Nothing" you mentioned)? If so, is there any way of guaranteeing this will close only the table link in access, and not shut down excel entirely?
Regards
Hans

Hmmm...
I think that you need to find an Access forum to post this question in for example http://www.excelforum.com/access-programming/
If you do repost this question can you please state that this is the original thread & provide a link to this thread (in the new thread)?

I think yes, you should be adding code to the access "query" (but I'm not sure if it is "sql code"). If an Excel instance is created by the Access "query" (or code) it should be identifiable & therefore able to be individually closed.

Are you able to post all of the Access query/code?
This may help me or others point out where the/any object variable is being set.

hth
Rob